How to filter a report using URL parameters

Modified on Tue, 12 Mar at 5:18 PM

Introduction


In an Omniscope report, if you want to configure some filters you could add a Filters view and arrange the filters manually. 




If you want to show someone else the same report but with different filters configured you would either have to modify the filters in the original report, or create a copy of the report and update the filters in the copy. An alternative solution is to define the filters within the report URL parameters. Using this approach you would simply change the URL whenever you want to show a different set of filters, without needing to open or modify the original report.


This is useful if you want to send tailored emails to different clients or individuals, where the report opens with a customised initial filter setting, or if you want to create bookmarks /links in a document, with the same effect. It should not be used for security purposes, where you want to restrict access to subsets of the data by the visitor; for that - see Multi-tenant reports.


In this article we discuss how to filter report data using the URL parameters. We will be using the Video Game Sales dataset, which is hosted on Kaggle. To reproduce on your Omniscope installation you will need to create a Kaggle account, then download the CSV file from the following location:


https://www.kaggle.com/gregorut/videogamesales


After downloading the file, create a new Omniscope project file, add a File block and select the Video Game Sales CSV file, then connect your file block to a blank Omniscope report and execute the workflow.



Open the report, and open the "Share" settings; allow external access (viewing only) by selecting "Public" (this won't be truly public unless you're using an externally accessible server deployment) and leave "Can edit report" unticked:




Now we're ready to dive in!


Report URL structure


Before we start exploring filters, it is worthwhile discussing the structure of an Omniscope report URL (if configured for sharing). You can find this URL in the "Share" settings of your report:


http://[Project URL]/r/[Report name]/#[Tab name]&[Filter 1]&[Filter 2]&...[Filter n]


A brief explanation of each of these segments is shown below:


PartDescription
[Project URL]The hostname and path to your Omniscope file on the Omniscope server.
[Report name]The name of your report. 
[Tab name]Optional. The name of the tab name you want to display when the report is first opened. If the tab name is not specified the first tab will be selected.
[Filter 1]&[Filter 2]Optional. One or more filters to apply to the report.


You may have noticed that the Tab name and Filters are configured after the hash # character. These parts are known as Hash parameters, which can be edited without the client app reloading. We can define multiple Hash parameters by separating each one using the & character. 


Let's jump straight in and configure our report with a simple filter:


/Video+games.iox/r/Report/#Genre=Puzzle


If we add a Table view to the report we should now only see Puzzle games listed, and the barometer in the top-right corner indicating the filter has been applied and the number of filtered records.



Now, let's imagine we want to send the same report to another customer, but this time we only want to include Puzzle games from Nintendo. We could email them the following URL:


/Video+games.iox/r/Report/#Genre=Puzzle&Publisher=Nintendo


When our customer clicks on this URL to open the report they will see the data with the filter applied:



Hopefully this has given you a taste of how report URL filter parameters work. In the next section we will discuss how filters are structured, and the different filtering options available.


Filter URL structure


The structure of a Filter has been designed to be both simple and powerful. Inside a filter you can specify the data table, field and filter expression:


[Optional table name or index]$[Field name]=[Filter expression]


Let's examine each of these parts in more detail.


Optional table name or index


The first part of the URL filter is the table name or table index number, followed by $


This is entirely optional, and is used to specify which table within the Report data sources the filter should apply to if your report has more than one input. We can enter the name or the index number.


The name of a table can be found from Report data sources dialog.


If you use index number then you are defining the index within the "Report data sources", 1..based.


If we don't specify a table name or index (and there is no $ to separate it) then each filter will be applied to every dataset, as long as the dataset contains the field specified in the filter.


Let's jump back into our example report and switch back to the workflow. Now let's connect a second dataset to the report. I'm going to use the "Bond Prices" demo dataset for this example, but it doesn't really matter.


Now enter the following URL:


/Video+games.iox/r/Report/#VG Sales$Genre=Puzzle&Publisher=Nintendo


Here we are specifying that the Puzzle/Publisher filter should only be applied to the VG Sales dataset. Likewise we could also use the following:


/Video+games.iox/r/Report/#1$Genre=Puzzle&Publisher=Nintendo


This time we have told Omniscope to only apply the filter to the first dataset, which in this case is also our VG Sales data.


Field name


In order to apply a filter we need to tell Omniscope the name of the field the filter applies to. Although we can specify multiple filters we can only specify one filter per field. The field name is not case-sensitive, so the following would all have the same result:


/Video+games.iox/r/Report/#Genre=Puzzle

/Video+games.iox/r/Report/#GENRE=Puzzle

/Video+games.iox/r/Report/#gEnRe=Puzzle


In your report fields will either be Number, Date or Text type. Some filter expressions will only work with certain field types, so be sure to check your data structure before configuring the URL filters. 


Filter expressions


The Filter expression outlines the behaviour of the filter in relation to the field. Omniscope supports many different types of expressions. Each type is listed below:


Expression typeStructureDescriptionExample
Equals=[Value]An exact match on a single value.#Genre=Puzzle
Not=![Value]Exclude a single value.#Publisher=!Nintendo
In=[Value1],[Value2],...An exact match on ANY of the values.#Platform=NES,DS
Null=nullMatch on null/empty values only. "null" is a special value.#Genre=null
Contains=*[Value]*Text fields only.
Match on any values that contain the value sub-text.
#Name=*Tetris*
Range=[Lower]~[Upper]Number/Date fields only.
Match on all values between the lower/upper bounds (inclusive).
#Year=2000~2010
Range greater than or equal=[Lower]~Number/Date fields only.
Match on all values greater than or equal to the lower bounds.
#Year=2000~
Range less than or equal=~[Upper]Number/Date fields only.
Match on all values less than or equal to the upper bounds.
#Year=~2010


Encoding special characters


Both the URL specification and our own specification above preclude you from directly using special characters - or, in fact, almost all characters other than A-Z, a-z, 0-9. If your field names, data source names or values have any such characters, they must be encoded, to let Omniscope distinguish them from their normal effect within the specification.


Special characters must be encoded, as standard for URLs, using percent encoding, and additionally, the space character can more elegantly be encoded using +. You can use an online tool to help you encode arbitrary characters. For example:


my+value   or   my%20valuemy value
a%7Eba~b
%C2%A35£5
Some+%23Difficult+%22text%22%2C+yes%21Some #Difficult "text", yes!


In modern browsers, you can enter arbitrary characters into the URL and the browser will automatically encode most of them for you invisibly. Certain characters such as ~ will not be encoded, so you must do this manually if present in your literal values or names. However, if pasting a URL into an email or document, you should paste the properly encoded form, otherwise the link may not open correctly. Be sure to test before sending any automated emails!


Formatting numbers


We support two formats when interpreting values given for numeric fields (whether using "equals" such as field=5, or "range" such as field=5~10). This is only relevant for numbers with decimal points (or thousand separators, which are not recommended but tolerated).

  1. First, using US/UK format "123.456" ("." is the decimal point)
  2. Second, using the server's configured format, which might be "123,456" commonly in European-locale installations (where a comma "," is the decimal point).

Even if the field has a configured thousand separator or text prefix/suffix, such as a currency symbol, do not include these in the value entered into the URL.


Formatting dates


When specifying dates (whether for "equals" or "range), you must always specify them in the timezone of the project (which typically matches the timezone of the server it was created on). We support 3 standardised formats and one custom, in this order:

  1. 2020-10-21   (no character encoding required)
  2. 2020-10-21 05:12   (encoded as 2020-10-21+05%3A12)
  3. 2020-10-21 05:12:47 (encoded as 2020-10-21+05%3A12%3A47)
  4. The configured format for the field, so if for example have a custom "Month name" date format "MMM" configured, on a French server, you should use "juil" for July.


Persistence of filters


URL filter parameters in Omniscope are designed to be used with report URLs that have "viewer" permissions - where the recipient of the report can open and explore, but not make any lasting changes.

However, if used in a report that is shared with "Can edit" ticked, URL filters will actually change the configuration of all tabs in the report, presenting a "Save" button, or if "Synchronisation" is also enabled, immediately persisting the change, causing other users of the report to see the impact of changes immediately.
It is recommended to use URL filters (e.g. when sending tailored report links in emails) only with "Can edit" unticked, which is the default sharing setting.


Interaction between UI filters and URL filters


Once a report has been opened, you can edit the hash parameters in the URL and press Enter to see the effects immediately without page reload. If there are problems, the app will report these briefly using a message popup, or in detail on the browser's developer console.


If you subsequently play with the filters within the report UI (sliders, checkboxes), the URL filters will be removed, since they no longer match. You can restore them in the URL by adding them back as hash parameters, which will reset any UI filters to the specified state. 


Hidden and frozen devices


If you have URL filter parameters referring to hidden or frozen filters inside your report, the report filters will be overwritten by the URL filter configuration values.


The URL filter can change the Report filters (if they are hidden) based on the URL filter rules. By hidden we mean that there are no "Filters" views or filter device associated with the field.


For example, if you have a number field which is of type Range inside the Report filters, but then the url filter defines a rule which is only valid for choices i.e. number=1,4 then behind the scenes the Report filter will be changed from "Range" to "Choices" to reflect the state.


NOTE: A report Filter will ONLY change its device type if the field is not visible in any Filters view in the tab.


Queries


URL filters are always applied to the default query - "Filtered IN". This query is always present in every tab of every report. If you have created additional named queries, they will not be affected. Each view in your report can be customised by associating it with a different query, or another query intersected with the default Filtered IN query; as such you can create advanced reports with some views unaffected by the URL filter parameters.


Share current filter state


From build 2021.1 onwards, we have added ability to capture the current "Filtered-IN" query state and automatically create a URL. For more information see here.


Variables


From build 2024.2 b21855 variable values can now be included in report URL.


The URL structure is the same, but you need to use the '@' character to identify a variable, ie:


/Video+games.iox/r/Report/#@MyVariable=3


You can combine variable and field filters in the same URL, ie:


/Video+games.iox/r/Report/#Genre=Puzzle&@MyVariable=3


Feedback


Hopefully this guide has given you a good understanding of how URL filter parameters work. In the future we have plans to enhance the functionality further. One such enhancement would be to create a way to auto-generate the URL parameters based the current filter states inside the report. 


As always, please get in touch if you have any questions or suggestions, using either our forums or public Trello board.


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article