Filter Data Based on Aggregate Function through SubReport

Environment

Product

Progress® Telerik® Reporting

Description

Imagine the following scenario.

We want to group a report, and in each group to show a table that is filtered to show only records with a specific field value, for example, set as a Report Parameter value. We also want the table to be with a different layout depending on the number of records it will display. Here are the more specific requirements for clarity:

We want to show all groups and a different table in each group:

• Case 1 - only for those with specific Fields.Type and more than 3 records to show.
• Case 2 - only for those with specific Fields.Type and 2 records to show.
• Case 3 - only for those with specific Fields.Type and 1 record to show.

Filtering by the value of the Fields.Type is possible, however, it is not possible to filter on the count of the filtered items or another aggregate function. The latter leads to the error message in the report stating that DataItem Filtering expression should not contain aggregate functions.

Aggregates may be used on the group filtering level, however, this will take into account the entire report data and not only the data shown in the group.

Solution

Let's use a SubReport in the Report Group Header or Footer instead of a Table. We will bind its DataSource to its parent DataSource, which is the group data. The idea is to apply the filtering by the value of Fields.Type in the subreport and pass different DataSources with Bindings to different Tables that are going to represent each case.

We simulate the filtering with an aggregate function with a Binding for the inner Data Items' DataSources.

Here are the sample steps for implementation:

1. Add a SubReport item in the Report Group Header and use Bindings to set its DataSource to ReportItem.DataObject.
2. Create a new report definition and use it as a ReportSource of the main report SubReport.
3. Add a new Filter to the subreport with Filtering Rule to display only particular Types.
4. Set the subreport Report Header/Footer CanShrink to True to allow the section to shrink when some of its items are hidden.

5. Add 3 (three) tables in the subreport Report Header/Footer - one for each case. For each table configure the following properties:

   • Set NoDataMessage to a valid string or whitespace. This is needed in order for the NoDataStyle to be applied.
   • Set NoDataStyle > Visible to False.

   • Bind the DataSource of each table to the corresponding Expression:

     1. Case 1 = If(CountDistinct(Fields.Name) >= 3, ReportItem.DataObject, Null)
     2. Case 2 = If(CountDistinct(Fields.Name) = 2, ReportItem.DataObject, Null)
     3. Case 3 = If(CountDistinct(Fields.Name) = 1, ReportItem.DataObject, Null)

   • Configure and Style each table as required.

With the above settings, for any number of names (i.e. Fields.Name) in the corresponding type (i.e. Fields.Type) only one table will have data and will be shown, simulating filtering based on an aggregate of its inner data.

Example

Download the sample main report and subreport from our Reporting Samples GitHub repository: FilteringWithSubReport.

See Also

• Data Items
• Filtering Data
• Adding Filtering to Table and Crosstab Items
• Adding Filtering to Report
• SubReport Item