Dotnet Report Builder provides many advanced ways to filter report data, and one of those options is setting up a “Cascading Dropdown Filter”. This option basically lets the user choose a “Parent” value from the dropdown, and then it displays the “Child” values in a second dropdown to pick from. One of the most common example of cascading dropdown is when selecting a car in a filter. You usually have to choose a make, like Honda or Toyota, and then choose a model that’s related to your selected make, like Civic for Honda, or Camry for Toyota. This can be required in many real word scenarios, like picking a “Supervisor” and then employees “Reporting to” the supervisor, choose a country, then cities, and so on.
This feature can be implemented by an admin/developer using the setup page. In the example below, we will setup a Department relation with Sub Departments.
Foreign Key Filter
The first step is to setup a regular foreign key for a dropdown filter.
As indicated in the screenshot above, we are using “DeptID” as a foreign key in “Employee” table to link with “SubDepartment” to filter report.
Here is a quick view of just setting up this “Dropdown Filter”.
When the user chooses to filter on “Sub Department”, the report builder displays a drop down list of all the department to select and filter report data.
Now let’s see how to add cascading filters and how we can utilize this feature during the report building.
Foreign Key Parent / Cascading Filter
To setup cascading filters, back in the setup page, the system shows you the option to “Setup Foreign Key Parent” when you have a Foreign Key defined.
In the above displayed example, “Master Department” and “Sub Department” are linked using foreign key of ”DMID”. So you have to basically setup the parent table in a similar way as the foreign key, but the key difference is you have provide the “Foreign Key Filter Field” as well, which will basically be applied in the where clause.
Now, when the user picks this field as filter, they will see 2 dropdowns, one for Master Department, and the other for Sub Department. The user can filter the “Subdepartment” list, by selecting its ”Master Department” first.
So in this way, dotnet report builder allows admin users to setup cascading filter in a fairly easy and intuitive way, by setting up parent fields and then child data.
You can also review the video below to learn how to setup cascading filters: