How do I filter the second parameter based on the first parameter in Power BI Report Builder?

[HamidBee]

I am working with some data which contains a list of football players of various nationalities. Here is a sample of that data:

ID	Player	Nationality
1	Roberto Baggio	Italy
2	Hernan Crespo	Argentina
3	Lionel Messi	Argentina

I created two parameters. The first one should filter the Nationality and the second one should filter the player. Here is an image of what I see:

 

When I select Nationality the dropdown for 'Player' still shows me all of the list of players irrespective of their nationality. How do I get the 'Player' drop down to filter based on what I select for 'Nationality'?. E.g. so that if I select England, I would only see Rooney and Joe Cole for the 'Player' drop down?. Here is my rdl file:

https://www.mediafire.com/file/ljbfis2awvadt7b/Football.rdl/file

I'm mindful that you wouldn't be able to run the report but you would atleast be able to view settings for the datasources and parameters.

[HamidBee]

Hi @sevenhills 

This worked beautifully. Thank you so much for your help. I'll have a read through the documentation you shared, I wasn't aware of the technical term for it until you mentioned it.

I am curious to know, if I added another parameter, club. So say I would filter by nationality ->club -> player. Would I simply add another condition to the SQL clause above and then add a parameter? like so:

SELECT * 
FROM Sheet1$
WHERE
[Sheet1$].Club = @Club
AND
[Sheet1$].Nationality = @Nationality

[sevenhills]

Yes, that is how it works.

For a report with cascading parameters, 

a) Get the list of parameters - both cascading and non-cascading

b) For each non-cascading parameters without data from the source, like date range, you may not need any source i.e., no need for dataset.

c) For each non-cascading parameters with data from the source, you get the data from the source by adding dataset and using parameter value filters if apply to your need.

d) For each cascading parameter, top level is same as above.

e) For each cascading parameter,

      second level and each level down,
      if you need data from the source, 
      add a dataset with the parameters upto previous level (which you want to filter), adjust the sql or code to filter 

     and point the parameter to this dataset.

f) Repeat the same for each level of cascading parameter. when you do for the first time, it may take some time back and forth. The concept is same as more levels of cascading added

g) Finally, to the actual dataset(s) that you consume for reporting,

       you need to add logic of filter from each of the parameters

       and apply parameter names to parameter values.

SSRS and Paginated reports are the same, FYI. 

There are sometimes, few exceptions like you may NOT need all parameters for the reporting dataset and ONLY need the last parameter or some of the parameters for the reporting dataset, purely on your needs.

[HamidBee]

Thanks for the detailed response. This may take me some time to fully digest. I'll run some demo reports and play around with non-cascading and cascading parameters. At some point I'm sure it would all just click. I was wondering though why do the parameters need to be linked to a seperate dataset instead of being linked to the main dataset?.

I honestly have never used SSRS. I've only very recently finished a course on SQL and began using SSMS and Report builder properly. I'm enjoying the process though. Again, I appreciate the help. 

[sevenhills]

Go through these links, for detailed steps:

https://www.mssqltips.com/sqlservertip/3466/cascaded-parameters-in-sql-server-reporting-services/

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/add-cascading-parameters-to-a-...

https://www.tutorialgateway.org/ssrs-cascading-parameters/

I quoted SSRS because there are thousands of articles and videos exists on internet. Not to confuse you. 

I added detailed information as we will doing parameters in a project through out. Better to invest time for long term. 

Why do we create different datasets for each parameter?

We are using each dataset for each parameter and don't want to see the repeats. 

Say, in your case, you have Nationality, Club, Players. If you have one dataset containing all three, then we see the duplicate values for nationality, club ... the idea of the dropdown will have unique list and using it will filter one more level down. A way of organizing and displaing unique items for the selection. Hope this helps!

[sevenhills · Accepted solution]

What you are looking is cascading parameters effect.

To read and understand: https://docs.microsoft.com/en-us/power-bi/guidance/paginated-report-cascading-parameter 

I saw your .rdl file, pls change these only to Dataset2

Query:

SELECT * 
FROM Sheet1$
WHERE
[Sheet1$].Nationality = @Nationality

"Dataset 2" Parameters:

and try running the report.

hope this helps!

[HamidBee]

Thanks for your help on the paramters before. I actually tried working on three parameters today after reading the guidance you shared. I was find with one parameter, then two worked fine. As soon as I got to the third parameter I hit a brick wall. I shared the problem here: 

https://community.powerbi.com/t5/Desktop/Cascading-Report-in-Power-BI-Report-Builder-Causing-Errors/...

feel free to take a look if you like. 

[sevenhills]

You cannot have NAME as parameter. It is a reserved word.  

Try changing to "Param_Name" or "NAME1" and keep the prompt as "Name"

apply to the dataset filter the same change and see if it works!