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:
- Add a
SubReport
item in the Report Group Header and use Bindings to set its DataSource toReportItem.DataObject
. - Create a new report definition and use it as a ReportSource of the main report SubReport.
- Add a new
Filter
to the subreport with Filtering Rule to display only particular Types. - Set the subreport Report Header/Footer
CanShrink
toTrue
to allow the section to shrink when some of its items are hidden. -
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 theNoDataStyle
to be applied. - Set
NoDataStyle
>Visible
toFalse
. -
Bind the
DataSource
of each table to the corresponding Expression:- Case 1
= If(CountDistinct(Fields.Name) >= 3, ReportItem.DataObject, Null)
- Case 2
= If(CountDistinct(Fields.Name) = 2, ReportItem.DataObject, Null)
- Case 3
= If(CountDistinct(Fields.Name) = 1, ReportItem.DataObject, Null)
- Case 1
Configure and Style each table as required.
- Set
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.