This is the fourth report in the series.
So far, we’ve used different types of restrictions, but those were independent of each other. However, real-world filtering scenarios often require dependent restrictions, where one selection affects the available values of another.
Within the AdventureWorks2025 open-source database, there is a classic example of such dependency: CountryRegion → StateProvince.
Applying this type of filter allows us to narrow down employees based on the province associated with one of their recorded addresses, while ensuring that only provinces belonging to the selected country are available for selection.
In the previous post, we examined static and dynamic select lists as data table restrictions.
In this article, we will configure dependent select lists so that the available StateProvince values automatically adjust based on the selected CountryRegion.
Adding Necessary Data Tables from the Catalog
To accomplish the desired task, several data tables must first be imported from the Catalog. The process was described in the first article of this series:
For the current scenario, the following data tables need to be imported from the AdventureWorks2025 database:
- Person.CountryRegion,
- Person.StateProvince,
- Person.Address,
- Person.BusinessEntityAddress,
- Person.BusinessEntity.
These tables allow us to build the relationship chain required to filter employees by the province associated with their addresses, while ensuring that the selected province belongs to the chosen country.
If the Auto create joins option was enabled during import, the records in the Joins section should look similar to the following:
Adding the Main Enumeration
The process is straightforward. Add a dynamic enumeration using the following SQL Select Statement:
SELECT
CountryRegionCode AS [KEY],
Name AS [VALUE]
FROM Person.CountryRegion
ORDER BY Name
Because the values are retrieved from the query, the enumeration must be configured to load its content dynamically from the SQL Select Statement and refreshed before report execution.
I've named this enumeration as CountryRegion. This enumeration name will be referenced later when configuring the dependent select list.
Next, open Models → Model and drag the CountryRegionCode property from the Person.CountryRegion data table into the Restrictions section.
The newly created restriction can now be linked to the CountryRegion enumeration by selecting it as the custom enumerated list from the Advanced configuration panel.
Finally, enable Prompt at execution so the restriction becomes available when running the report.
Adding the Dependant Enumeration
Things become slightly more interesting when configuring the dependent enumeration.
At first glance, its main configuration looks similar to the previous example. The base SQL Select Statement still loads all available values and does not reference any restrictions:
SELECT
StateProvinceID as [KEY],
Name as [Value]
FROM Person.StateProvince
ORDER BY Name
The dependency itself is defined separately, inside Dynamic display → SQL Select Statement for prompted restriction:
SELECT
StateProvinceID as [Key],
Name as [Value]
FROM Person.StateProvince
WHERE CountryRegionCode IN ({EnumValues_CountryRegion})
ORDER BY Name
The dependency is implemented inside the SQL Select Statement for prompted restriction, where the selected values of the CountryRegion restriction are referenced using the {EnumValues_CountryRegion} pattern. This ensures that only the provinces belonging to the selected country remain available in the StateProvince enumeration.
The restriction itself is then added to the report in the same way as in the previous examples, so no additional configuration is required at the model level.
Skill Check
If everything has been configured as described in this post:
- initially, the StateProvince filter remains empty because no country or region has yet been selected in the CountryRegion filter:
- after selecting a region such as United Kingdom, the dependent filter is refreshed automatically and displays only the corresponding provinces:
As an additional exercise, try making the dependent filter required and configure a display message indicating that a country or region must be selected first.
So far, all examples in this series have been based on data tables as the underlying data source. However, real-life reporting scenarios often rely on stored procedures and user-defined functions. In the next post, we’ll explore how the same filtering techniques can be applied when working with those types of sources.
Top comments (0)