Cascading Parameter in SSRS
Cascading parameter is basically a parameter that depends on another parameter so that the list of available values for a parameter are filtered based on another parameter selection. Cascading parameter provide a way of managing large amounts of report data
This Article is based on AdventureWorksDW database which is a sample database for SQL server. The example in this blog is to fetch the Employee information.
The report takes 2 parameters:
• Department : Department filter for the Title.
• Title : This dependent parameter which is populated based on Department for which we have list in a Employee report, we can have Department list main parameter and Title as dependent parameter, and values of the this dependent parameter are based on Department selection.
Report Design:-
1. Create a new SSRS project and solution.
• Open SQL Server Business Intelligence Studio (BIDS).
• Select "New Project"
• Select 'Reporting Services' template.
• Name of "SSIS" (this will be the name of the overall solution).
• Location of existing folder "C:\ SSRS_Reports".
• Check the 'Create Directory for Solution' box.
• Click 'Ok' and the Solution will be created.
2. In Solution Explorer window, Right click "Reports" and Click "Add New Item"
3. In Add New Item window, Select "Report", enter the Report Name and click Ok.
4. In Report Data Window, Right click "Data Sources" and Create a new data source. Refer the below image.
5. Create Department Parameter Dataset
• In Report Data Window, Right click "Data Sets" and Create a new data "Add New Dataset"
• In Query Tab, Enter the Data Set Name
• Select "Use a dataset embedded in my report." Radio button
• Expand "Data Source" drop down, Select the Data Source name (dsReport).
• Select "Query Type" as Text.
• Enter the Select Query in "Query" Text Box and Click Ok. Refer the below Screen shot.
6. Create Department Parameter
• In Report Data Window, Right click "Parameters" and Create a new data "Add New Parameters"
• In General Tab, Enter Parameter Name and Parameter Prompt string
• Expand "Data Type" drop down and Select "Text". Refer below screen shot
• In Available Values Tab, Select "Get values from a Query" radio button.
• Choose the Dept_P1 dataset. From there, just match up the Value and Label fields like in below image.
7. Create Title Parameter Dataset
• Follow the sample steps from point 5.
• In Query Text box, Modify the Query Like as below because we are creating cascading parameters
SELECT DISTINCT title
FROM dimemployee
WHERE departmentname = @DepartmentName
• In Parameters Tab, enter Parameter Name and Select Parameter Value
• Follow the sample steps from point 6.
8. Create Title Report Dataset
• Follow the sample steps from point 5 & 6
• Modify the Query as follows
SELECT TOP 100 firstname,
lastname,
birthdate,
hiredate,
emailaddress,
phone
FROM dimemployee
WHERE departmentname = @DepartmentName
AND title = @Title
• In Parameters Tab, enter Parameter Name and Select Parameter Value
9. The Report Data should be display as below image
10. In Report Design Tab, Right Click, Select Insert And Click Table.
11. Right Click Table and Select Properties and select the Dataset Name as shown below image.
12. Drag and drop the field names into Table. Add the columns which you need to include in the report output. Refer the below screen shot.
13. Click preview tab
14. Now you can see that Title is connected to Department. Any changes to Department, repopulates the Title.
Nice Explanation, better to explain how you design the Department and Title dropdown's in Report Builder end. Why because most of the people are done this type of filtering options in front end itself. But you did this in Report Builder side. This will give better performance compare to that.
How to design DropDown in Report Builder:
Right click on report then choose DropDownList, How we choose a Table, Matrix in Report Builder same like we have an option called as DropDown. We just select that and assign values to that by selecting appropriate DataSet for that.