Forum Discussion
How do I filter the second parameter based on the first parameter in Power BI Report Builder?
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!
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.
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.
Go through these links, for detailed steps:
https://www.mssqltips.com/sqlservertip/3466/cascaded-parameters-in-sql-server-reporting-services/
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!