Business intelligence is a complex yet increasingly important subject in the business landscape. Users need to analyze data, then transform it from raw data points into meaningful visualizations. This process can be challenging, especially as businesses generate datasets ranging in size from a few rows (such as transactional sales data) to millions of rows in predictive data models to forecast future decisions.

As the size of the required data grows, the need to filter it becomes more relevant across stakeholders so that only the data pertaining to a specific business interest is used to make data-driven decisions.

Examples of Data Filtering

• Filtering data by the current year, to only show most recent data
• Filtering data by region/country/state/city, to show data pertaining to a specific geography
• Filtering data by specific criteria, such as particular age groups or income thresholds,
• Filtering data dynamically using any relevant parameters

Earlier versions of Wyn Enterprise offer data filtering within the Datasets (or ad hoc query) by creating simple rules on data fields and expressions to extract the specific information from the original data.

With Wyn Enterprise, 4.0, Dataset filters have progressed to support more complex query filters. You can now group by the same simple rules, as well as create hierarchies for filtering conditions. You can also choose the operator “AND/OR” for grouping these rules.

In this post, we explore the new interface for adding filters to a dataset.

Using Dataset Filters

Suppose the CEO of a company wants to analyze the product sales for the current fiscal year from different regions through a dashboard and/or report. The schema of the data in the database is as shown below:

The CEO also wants to share this sales analysis with the company's Regional and Store Managers, but he/she wants to restrict the data so that these managers receive only the data relevant to their respective region and/or stores. According to this business requirement, the data should be made accessible to different users across the company's hierarchy as depicted below:

To be able to fetch data using the Wyn Datasets for the above scenario, we will need an ad hoc query that looks something like the below SQL query:

Select * From Sales
where 
YearofSales = Current Year 
AND
(
( Region = @Region  AND RegionalManager = @UserName )
OR
( StoreID = @StoreID AND StoreManager = @UserName ) 
)

Let's walk-through the steps to visually design this filtered query using Advanced Filters in Wyn's Dataset Designer.

Advanced Filter Editor

Before we proceed with designing the filter query, let's take a look at the UI and the main components of the Advanced Dataset Filter Editor.The UI of the Advanced filter functionality is shown below with descriptions for each major function:

1. Add rule: This allows a user to add one or more conditions (\"rules\") for filtering the dataset query. This function has always been in Wyn Enterprise and has not changed in 4.0.
2. Add group: This allows users to add hierarchical rules using AND/OR operators, as well as options to add subgroups. These groups represent the conditions (\"rules\") grouped inside parenthesis in a SQL query, in which the parenthesis defines the precedence of evaluation for the filter conditions. Each group is visually separated by a visual border enclosing its rules and the logical operator. These groups allow users to create complex hierarchical queries for the filters.

1. AND/OR operators: These operators specify the logical operation to be performed between the filter rules. The AND operator displays a record if all conditions (or groups of conditions) that are separated by the AND operator are \"true\". The OR operator displays a record if any of the conditions (or groups of conditions) that are separated by the OR operator are \"true\".

There is no limit on how many rules or groups can be used in the Advanced Filters, so users can meet any complex business requirement.

To design the query for the above use-case using the Advanced Filter editor we will need:

1. A simple rule on the \"Year\" field (not in a group)
2. A group, separated logically using AND from the above rule on \"Year\"
3. Two sub-groups of the group created above: one sub-group with rules on \"Region\" and \"Regional Manager\" fields, and another sub-group with rules on \"StoreID\" and \"Store Manager\" fields. The inner rules of these sub-groups must be logically separated using an AND operator, while each sub-group must be separated by an OR operator. Further details on these requirements are shown and discussed below.

To follow the steps for adding the above filter conditions, you will need a Wyn Dataset. You can either create a new Dataset using the attached excel \"Sales.xlsx\" as its Datasource provider or simply upload the attached datasource/dataset files - \"Sales.dsc\" and \"YearlySales.dst\" to the Wyn Portal.

Once you have created a dataset for the Sales data, we can now add the rules and groups.

Add Rule on Year

The filter condition for \"Year\" is a simple rule outside of any group. Simply click \"Add Rule\" and select the \"YearOfSales\" column to filter the data on a specified value. For this value, you can either use a constant/static value, or a parameter. For this use-case, we want it to be dynamic, so we will use a parameter with a default value of the current year, 2020.

Once done, the filter rule appears as follows:

Add the Top Level Group in the Hierarchy

The next rules in the Filter condition will be grouped to give resulting data when either of the conditions are met. The resulting data will then be combined with the data from the \"Year\" filter. As a result, we need to add three Filter groups: one as a top-level group, and two as sub-groups. .

To add the top-level group first, click \"Add group\". The resulting structure should be displayed as shown below:

Groups in the Advanced Filter mode must always have a rule or sub-group. Thus, when adding a group, the group will automatically be given an empty filter.

In this use-case, we do not need this default rule, so we can delete it by clicking the \"Delete\" icon on the right side of the empty filter, however only after adding the sub groups as described below:

Add the Sub-Groups in the Hierarchy

Once the top-level group is added, the next step is to add the sub-groups. Click \"Add group\" again, ensuring that the sub-group added is in the correct level of the hierarchy. Do this step twice, since we need two sub-groups for our use-case. Each sub-group should be added with one rule by default, but we will need two rules in each sub-group. Click \"Add rule\" in each sub-group and delete the additional rule in the top-level group to achieve the desired filter hierarchy, as shown below:

Bind Data Columns and Update the Logical Operators

The final step in building the advanced filter query is to bind the data columns in the rules against which data is to be filtered, and then update the AND/OR operators separating the rules and groups. Once complete, the final filter query with the appropriate rules should appear as shown:

Since our use-case should filter the data based on whether a Regional Manager or Store Manager is logged in, and which user that is, the parameters added here should be based on User Context and Custom Properties to fetch and filter the data dynamically.

Once the filtering conditions are saved to the dataset, the query retrieves the data specific to those conditions. This filtered query composing the dataset can now be used in Dashboards and/or Reports.

When the above-filtered query conditions are executed for the dataset, data corresponding to the currently logged-in user is fetched, as shown below:

The above image shows the data returned after filtering, when the data is accessed by a Regional Manager (e.g. \"John Conner\"). This Regional Manager sees the sales for all stores operating in his specific region. The above image shows the data returned after filtering, when the data is accessed by a Store Manager \"James Johnson\". This Store Manager sees the sales for the specific store he is operating.

Download Attachments

The advanced filters in Wyn Datasets are designed to be able to structure any complex filter query for your business requirement with its intuitive and easy-to-use editor. However, if you need any assistance to design the filter query for your business requirement, reach out to us at Wyn.Experts@grapecity.com.