OR Between Filters

intapiuser · ‎03-02-2023

This plugin enables dashboard designers to apply an OR operator over a group of dashboard filters, rather than the default AND behavior.

All filters not selected in the or filters array will maintain normal behavior (AND behavior with each other and with the group of alternating filters).

Please note: This plugin requires a good understanding of your data model, and data modeling concepts.

STEPS

STEP 1 - ADD THE PLUGIN

Download the plugin
Extract the `.zip` file to C:\Program Files\Sisense\PrismWeb\Plugins . If the folder does not exist, please create it before extracting the `.zip` file.
Refresh your dashboard

STEP 2 - CHOOSE YOUR LEADING FILTERED FIELD

This plugin works by modifying the widgets' JAQL's (queries).

In JAQL syntax, OR operators can only be applied as sub-conditions: filtering a single field either by one condition or by another. Therefore, in each query, we must choose a single leading field to filter.

Before you choose the leading field, try and sort your use case into one of the following categories:

Single Fact Model

When all measures in the dashboard are taken from a single fact table, we would choose that fact table's primary key (unique identifier) as that leading field. The records from this table will be filtered by all of our required conditions before being aggregated and\or returned to the dashboard, and we are good to go.

All alternative filters are taken from the same table

If the alternative OR filters are applied on fields that come from the same table (regardless of the number of fact tables being used in the dashboard), choose that filtered table's primary key as the leading field.

Multiple Fact Tables and Multiple Filtering Tables

In more complex use cases where measures are taken from multiple fact tables, finding a single leading field is a challenge. If we choose a field from fact table A, it would filter all of the measures taken from table A properly. However, if we attempt to filter measured from fact table B by the same leading field, we could create Many to Many on the dashboard (which nobody likes).

In those cases, it is recommended to consider adding a key table to the model, to mediate between the alternatively filtered dimensions and the fact tables:

Once you identify your leading field, take note of its name, its table and its data type (text, numeric, date).

STEP 3 - ACTIVATING THE PLUGIN

Go to the dashboard that you would like to apply the OR Filters.

In the dashboard script, paste the following code:

/*************************************/
dashboard.orFiltersConfig = {
  orFilters: [
    {
      table: 'product',
      column: 'productCategory'
    },
    {
      table: 'employees',
      column: 'name'
    }
  ],
  pk: {
    table: 'key',
    column: 'keynum',
    datatype: 'numeric'
  },
  includeWidgets: ['5b31ee819eaa5e7124ba286c'], //Optional
  excludeWidgets: []//Optional
};

prism.plugins.orFilters.applyOrFilters();
/*************************************/

STEP 4 - CODE CONFIGURATION

orFilterss: An array, holding the details of the filters that should be affected by the plugin. Any filter not included in this array will maintain normal AND behavior.

PK: The details of your leading field

includeWidgets (optional): If you only want specific widgets in the dashboard to be affected by the plugin, add their id's to this array. All other widgets not included on the list will maintain normal AND behavior.

excludeWidgets (optional): If you want to apply the plugin's behavior to all widgets in the dashboard apart from specific ones, add the excluded widgets' id's to this list.

Do Note: any item added to the includeWidgets array will automatically disable the excludeWidgets option.

If neither list includes any id, all widgets in the dashboard will be affected.

STEP 5 - SAVE THE SCRIPT AND REFRESH YOUR DASHBOARD

For reference and examples, please see these sample dashboards.

Sisense Community