Using Filters and Queries in the report

Let's say that a company wants to visualise on the same page some time series data that pins the sales figures, marketing budget and weather data for one month, and monitor the relationships.

Step 1 - Pick the Data Source

The three datasets might come from three unrelated sources. In Omniscope Evo application there is no imperative to merge/ append the sources before the visualisation phase. The user can feed them directly into the Report block, then for each individual chart pick a dataset from the list  (see Chart > Data menu). 

Step 2: Pick the Data Subset

The user can then refine further the exact data subset by picking one of the configuration options from the Chart settings > Query : 

1. All data (the whole dataset).
2. Filtered data (result of filtering - subset of 'all data' e.g. Country = "Italy", Value > 100).
3. Selected data (a chart can be set to display details of the selection on another chart e.g. line view showing some information about the slice of a pie chart. Both charts will have to be pointed to the same input dataset).
4. 'Read-only' query (works on a single tab or on multiple tabs e.g. data representing female customers, aged 35-45. The aim is to create a static data subset that can be easily referenced from different charts in the same report)
5. 'Dynamic query' is a method to influence filter states on multiple tabs by controlling filters on one of them (this query is 'live' and interaction with the exposed query devices will cause mirroring of filter states on the linked tabs e.g. if Country="UK" is selected on the 1st tab, this state will appear on other tabs, avoiding the need to manually pick the same choice on multiple tabs, or even have filter devices on all of them). Note: the report viewer will still be able to 'reset' the query and access 'all data'.
6. Intersection of multiple options above (by adding them in the Query section of the chart settings - see image below).
7. Inverted state of the filter/selection is showing the data that was filtered out.

Configuration

To add one or multiple filtering devices to a tab - drag the Filter from the chart picker (+ button). Expand to configure the fields on which the recipient can filter, as well as the filter type (see field filter settings). If the report has multiple data sources coming in from the workflow the user can pick relevant field filter for each of them. (Tip: if the chart isn't responding to filtering it is worth checking that filter belongs to a correct source)

To point a visualisation to a particular data subset expand the Query section in the Chart Settings to access the filter/query options.

1. All data

Open the Chart settings by clicking on the chart expand button, or on the Chart settings button on the right hand corner.

In the Query section delete the 'Filter IN' - the default query choice (click on the bin button).

This chart will now show all data from the selected input, regardless of the filtering and selection activity around it.

2. Filtered data

No need to do much - this is a default, meaning the chart will show all data until a choice is made in the filter devices or selection activity.

3. Selected data

When this option is ticked the chart will remain empty until a selection is made in a chart on the same tab, that is pointing to the same dataset. Worth remembering (if you see a dashboard with a blank chart) - to place a note next to it or in the chart title, indicating that it requires selection to be made.

4. 'Read-only' query data

Named query is a way of capturing a data subset that will be referenced often in multiple parts of the report e.g. customer segment "Female 25-35".

Deploy a named query: 

Create it: open a Filter device and expand options for the dataset you wish to use in a query, then make filter choices to create a static query (Country = "Italy", Value>100)

- Click on the '+ Query' button and give your query a name, also select if you want it to work across multiple tabs

- in the Report Settings (cog in the right-hand corner), on the Global tab, find your named query (under the name of the dataset it's based on) and make sure the option 'Read only' is ticked (see image).  You may wish to tick 'ignore reset' option, which will make the query totally static, and non-responsive to filtering and move/keep interaction on the page

Reference it: Point the relevant visualisations to the read only query from the chart's Query section.

Note - you can also do this in reverse order: start creating queries from the Report Settings, then go to the Filter devices, open the field choices for the relevant queries, then determine filter settings for each one.

For more information see queries in Omniscope Evo.

5. Dynamic query ( filter behaviour on one tab is reflected across multiple tabs)

This is a useful concept that allows you to switch between the data subsets with minimal interaction: you could place filters on one tab and simultaneously control the whole report.

Deploy a dynamic query in 3 steps:

- Create it: In the Report Settings > Global tab > create a new multi-tab query (+Query button) and make sure 'read only' option is not ticked

- Expose the query fields: On each tab where you wish to add filters that will influence data shown on multiple other tabs add Devices view with exposed Dynamic query fields (see image below - select the field filters from the Multi-tab section)
- Reference it: Point the relevant visualisations to the Dynamic query subset from the Chart Settings > Query section 

For more information see queries in Omniscope Evo.

What happens when your chart is pointed to a read-only or a dynamic query?

In the Chart settings (the second screenshot) you can pick the query name and also decide whether to intersect it with the filtering/selection subset.

In case of a 'read only' query you do not need to make any other arrangements - simply point the relevant charts to this query and they will always represent the 'canned' data segment. Should you decide to make changes to this query, you will have to unlock it first (from the Report settings), make changes in the Filters devices, then tick again the 'read only' option.

If you wanted to activate a dynamic query you will need to expose the filter devices belonging to this query, on one or multiple tabs.

Open the Filter devices and navigate to the Query name by expanding the options under the main dataset name. 

Note - if you have multiple sources connected to the same repot, you may have to click on the individual data source names to expand/collapse them in order to get to your query / field level.

You can pick the fields that will shape the query and be reflected across the tabs, while making sure that relevant charts are pointed to this dynamic query.

Important: When deciding which filters to include on a particular tab you may have a choice between the fields that appear in multiple datasources, also in multiple queries, in which case you can expose deliberately (or accidentally!)[Field A] in the main datasource next to the same [Field A] that belongs to a dynamic query. These will have different effects on your tab, so worth re-naming them (from the little settings cog next to an individual field filter), to avoid confusion.

6. Intersection of multiple options above (by adding them in the Query section of the chart settings)

In the Chart Settings add multiple items - try to avoid conflicts. 

7. Inverted state of the filter/selection

Available as the last option from the Chart settings - will point a chart to the inverted universe in relation to the filter choices e.g if the filter choice = A, the chart will exclude A records from the subset and visualise B, C, D etc.

Troubleshooting - Dynamic query ( filter settings on one tab having effect on multiple tabs)

This will require several steps, but you'll love the effects. In case the results are not what you expected - please check the list below, to make sure you haven't missed a step:

Advanced feature: Union query

In Omniscope 2022.2 b21735, a powerful feature has been introduced to customize query behavior, providing users with more versatile filtering options. This feature is accessible through the "Union" option in the Query section of the Report settings dialog.

Configuring the "Union" Option:

1. Open your report in Omniscope.
2. Navigate to the Report settings dialog.
3. In the Query section, you will find the "Union" option next to the query.
4. Toggle the "Union" option on to activate it.
   
   

By enabling the "Union" option, filter states shift from the traditional AND operator to the OR operator. This means that records can now meet any of the specified filter conditions to be included in the query results. 

For instance, if you have two filters - Field1=A and Field2=C - enabling the "Union" option would retrieve records where either Field1=A OR Field2=C, offering greater flexibility in query customization.

Important Note: It's crucial to be aware that the "Adaptive Choices" and "Filters" options will not influence filters when they are part of a Query configured with the "Union" setting.