Working with Enhanced Filter Editors in the ActiveReports End-User Report Designer

Businesses of all sizes use data filtering to narrow their analysis and answer questions with data-driven evidence. As the number of data increases, filters pertaining to specific business interests become more critical.

With ActiveReports v15, the ProDesigner for Web features an enhanced filter editor that lets end-users filter their data with less developers' assistance.

Stakeholders benefit from filtering in various ways, for example:

• Filter data by the current year: show only the most recent data
• Filter data by location: show data about a specific country, region, state, or city
• Filter data by specific criteria: view particular age groups, income thresholds, and more
• Filter data dynamically: apply relevant parameters based on a specific project

This new filter editor in the web-based report designer gives end-users the ability to create complex query filters for reports from an intuitive interface located inside the designer. Group the filter rules and create hierarchies for filtering conditions, joined by logical operators: "AND/OR."

The ActiveReports ProDesigner lets end-users easily create reports with several enhanced filtering options. The filter editor is designed to handle collections of filters, filter groups, hierarchical filters, and logical operators. This blog explores the filter editor's vast capabilities applied to a simple use-case.

Data Filtering in the ProDesigner for Web: Use Case

A company keeps sales transaction data for multiple locations inside a single database, as shown below:

The company needs an annual report identifying which products surpass a benchmarked sales figure for the current year. This sales figure may vary across different stores based on geographical or other differences.

The data query for filtering out desired data from the database can be represented with the SQL below:

Select * From ProductSales  
where  
YearOfSales = #CurrentYear  
AND  
(  
       (StoreID = #Store1 AND Sales > #Target1)  
OR  
       (StoreID = #Store2 AND Sales > #Target2)  
)

Before creating the above filter query using the enhanced editor in the ActiveReports ProDesigner for Web, let's review the new interface and its options.

Enhanced Filter Editor User Interface

The new filter editor simplifies the process of applying filters to a report and its contents. This includes datasets and controls such as Table, Tablix, and other data regions. Filters at the dataset level reduce traffic between the server and client. Filters on data regions are ideal for use with shared datasets. To learn more about the ProDesigner, visit this blog.

Click "Add" in the property panel, and open the new filter editor.

Open it for Dataset from within the "Edit Data Set" dialog.

It opens the new filter editor (below).

Configure the filters from a dedicated interface instead of adding filter collections, as was the case with previous versions of the ProDesigner for Web.

Interface Options for the New Filter Editor

The new interface provides users with several options:

• Select the logical operators "All of" (AND) and "Any of" (OR) for both RDL and Page report types.

  • These operators specify the logical operation performed between the filter rules.

    • The "All of" operator displays a record if all conditions (or groups of conditions) separated by this operator are "true."

    • The "Any of" operator displays a record if any conditions (or groups of conditions) separated by this operator are "true."

• Add one or more filter conditions ("rules") for filtering the dataset query based on data fields and expressions.

• Add filter groups to add hierarchical and grouped rules using the AND/OR operators.

  • These groups represent the filter conditions grouped inside the parentheses in a SQL query, where the parenthesis defines the precedence of evaluation for the filter conditions.

• Each group is visually identified by a separate tree in the filter hierarchy enclosing its rules and the logical operator, as shown below:

• Add filter values based on expressions and report parameters that match the data field's type.

  • Create new parameters or select existing report parameters based on the selected field type from within the filter editor interface. Save time spent moving from one panel to another to add parameters as values on filter conditions.

• To add a new parameter from within the filter editor, click "New Parameter." A new window interface opens and lets the user configure the parameter as shown below:

• Choose commonly-used operators from a drop-down:

• Delete a filter condition or a filter group without affecting other conditions and groups.

There is no limit on how many rules or groups can be added to the new filter editor. This flexibility helps users meet any number of complex business needs.

Designing Filter Queries Using the Enhanced Filter Editor

Let's walk through the steps to visually design the desired filter query using the Enhanced Filter Editor in the web-based End-User Report Designer. To create this query, you will need:

• A simple rule on the Year field
• One group separated logically using AND from the above rule on Year
• Two sub-groups logically separated using OR inside the group created above:
  • One sub-group with rules on Store1 and its targeted sales
  • Another sub-group with rules on Store2 and its targeted sales

To follow the steps, you can use the attached report in your End-User Designer for web implementation or use in any of the web designer samples from GitHub. To use this report with the sample, place it in the sample directory's resources folder, and open it in the designer. When the report is ready, add the following rules and groups.

Add a Rule on a Year

In the report designer, select the Table control and open the Filter Editor from the Properties panel.

Once inside the Filter Editor, click the Add button and select the YearOfSales data field. Add a simple filter condition outside of any group, and filter out the data for a specified year.

For the value, either use a constant, expression-based, or a parameter-based value. For this use case, use an expression {Year(DateTime.Now())} to fetch the current year's value.

Once done, the filtering rule appears as follows:

Add the Top Level Group in the Hierarchy

The next filter query rules are grouped to give resulting data when either of the sub-filter conditions is met. These rules require the addition of three filter groups: one as a top-level group and two as sub-groups.

To add the top-level group first, click Add below the filtering rule on year and select the Add Group option. The resulting structure should be displayed, as shown below:

The top-level rule needs to be in conjunction with the rule on Year, so we keep the default "All of" logical operator at the top in the Filter Editor.

Add the Sub-groups in the Hierarchy

Once the top-level group is added, the next step is to add the sub-groups. Inside the top-level group, click Add and then select the Add Group option twice for each sub-group to get a structure as shown below:

For each sub-group, click Add and select the data field "StoreID" as the first rule with values equal to constants: Store1 and Store2 respectively, as shown below:

Next, create a filter rule for target sales in each sub-group. Click "Add" again in each sub-group. Select the data field, "Sales."

Choose the operator "Greater than or equal to" for this rule to fetch sales that equal or surpass the desired target.

This use-case requires a dynamic report as the target sales may vary for the succeeding years. Fetch values for this rule using parameters as shown below:

Set the logical operators for sub-groups

The final step in building the desired filter query is to update the logical operator separating the sub-groups in the top-level group. Once complete, the last filter query with the filter conditions, operator, and values appears as shown below:

Conclusion: Next Steps for the End-User Report Designer for Web

Once the filtering conditions are saved to the report, the query retrieves the data specific to those conditions. With the above-created filter query and Target parameter values: 50000 and 15000 for Store1 and Store2, respectively, the data in the report appears as follows:

Enhanced filters in ActiveReports End-User Report Designer for Web are designed to structure any complex filter query with its intuitive and easy to use editor.

For assistance designing filter queries for your business needs, reach us through our support channels.