Hierarchical Cascading Parameters in Paginated Reports

In my previous blog, I mentioned the limitation of searching drop-down lists. We overcame that limitation by using a search field. In this blog, we are going one step further. We will use both the search field, and the result of the drop-down list to filter the third parameter. In classic SSRS terminology, we use the term ‘Cascading Parameters’ to define this problem. Just like a waterfall, the parameter values are selected from top to bottom. The parameters positioned below are called dependent parameters because their value depends upon the selection of parameters above them.

What you should know

Before you start the blog, these are the things that you should be familiar with:

• Power BI and SSRS
• Paginated Reports
• Power BI Report Builder
• Basic understanding of DAX

Also, we will build upon the working done in the previous blog ‘Limitations in Power BI Paginated  Reports‘ so you should check it out first. More specifically, the first limitation mentioned in that blog is the one you need to understand before moving on with this blog.

Premise

Let’s say that we have a hierarchy of two columns: Continent and Country. For each of these columns, we have a drop-down list. The data is sourced through a Power BI dataset through XMLA. Hence, we will use DAX to slice and dice the data. The desired flow for this report is as such:

• The user selects a continent from the drop-down list. Since there are only seven continents, we don’t need a search field to search through the list.
• The drop-down list of countries should repopulate based on the selection of the continent. Since there can be many countries, we also need a search field in which we will provide keywords to further narrow down the list.
• The user then selects the desired countries.

Working

We will use divide and conquer to split the task into three subproblems:

1. Filter the drop-down list of cities based on the keyword provided in the ‘Search Countries’ text field.
2. Filter the drop-down list of countries based on the selection of Continents drop-down list.
3. Combine the above two approaches and filter the drop-down list of countries based on both the keyword and the continents.

Subproblem 1:

Let’s say we want to search all countries that contain the keyword ‘Can’. We will write the keyword ‘Can’ in the ‘Search Countries’ text field, and it will filter the drop-down list accordingly.

Note: We will use the DAX function Search(), which is case-insensitive.

The result should look like this:

To achieve this result, we perform the following actions:

1) Open the dataset of ‘Countries’ and create a parameter ‘p_search’ which will hold the value of the text field parameter.

2) Go to the ‘Query’ tab of the dataset and use the DAX expression to filter the dataset based on the text field.
Evaluate Filter(DISTINCT(‘Sheet1′[country]), SEARCH(@p_search, ‘Sheet1′[country], 1, 0))

3) Save the dataset and run the report. Now when we write ‘can’ in the text field and press tab, it will filter the countries.

Subproblem 2:

We also want to filter the countries based on the continent selected from the drop-down field. Suppose we select a continent ‘Asia.’ It should filter the ‘Countries’ drop-down to select all the Asian countries. Furthermore, we also need to be able to select multiple continents at once.

For this purpose, we will use the DAX function PATHCONTAINS(path, item). This function takes a pipe-delimited text of values (for example, Asia|Europe|Africa) known as a path and an item to search for in this path (for example, Africa). It returns true if that item is found in the path.

1) To achieve this result, we create a new parameter in the Countries dataset that takes the values of the Continent drop-down field and creates a pipe-delimited string from the array of selection. This step ensures that we can filter countries based on the selection of multiple continents, instead of one continent at a time.

2) Navigate to the Query tab and use the following DAX expression to filter countries based on the pipe-delimited parameter created above.
Evaluate Filter(‘Countries’, PathContains(@p_continents, ‘Countries'[Continent]))

3) Save the dataset and run the report. Now when we select South America from the drop-down list, it shows only South American Countries.

Till now, we have solved both the problems separately, but we should be able to search countries by continent and by text as well. So now it’s time to combine the above two sub-solutions and solve the bigger problem.

1. We will use the AND() operator in DAX that gets two expressions as input and returns true, if both of them are true. Otherwise, it returns false.
2. Update the Query to use the following DAX expression.

Evaluate Filter(

    ‘Countries’, AND(

                               PathContains(@p_continents, ‘Countries'[Continent]) ,

                               SEARCH(@p_search, ‘Countries'[country], 1, 0)

 ))

3. Save the query and run the report. Now when we choose South America and search for countries that contain the keyword ‘GU’ in their name, we get the desired result.

Limitations

In Paginated Reports, we can’t filter a parameter’s value based on another parameter that is positioned below it. For example, in the picture below, the parameter the Country is below the Continent, so we can’t filter Continent based on Country.

Paginated Reports don’t support circular dependency between parameters. What this means is that we can either filter Parameter A based on Parameter B, or we can filter Parameter B based on the value of Parameter A, but we can’t support both in a single report.

Conclusion

In this blog, we took an example of geographical data to explain how we can cater to cascading parameters in Paginated Reports that source data from Power BI datasets. We used DAX to slice and dice the data along the hierarchy. We filtered a drop-down list of countries in three different ways: Based on a text in a text-field; a result of a drop-down list of continents; and combining both the text-field and the continent drop-down list. In the end, we mentioned some limitations that go around with the parameters in SSRS. Hopefully, this article will help you solve any problems related to the cascading parameters.

Happy cascading!