Adding Cascading Parameters to SSRS Reports

Friends,

Parameters are used to specify the data to use in a report. There are two types of parameters in a report –

• Query Parameters – When you define a dataset query that includes variables, Reporting Services creates corresponding query parameters. Query parameters are used to limit data retrieved from the data source to just the data needed for the report.
• Report Parameters – Report parameters appear on the report toolbar and allow report readers to select or enter values that are used when a report is processed. In Report Designer, report parameters are created automatically from query parameters. In Report Builder, report parameters are created when you set a prompt on a filter clause. You can also define report parameters that are not tied to query parameters.

Now let’s see how to add Parameters to a Report. Here in this post we are gonna discuss about Single Valued Parameters and for that I selected OUR Adventure Works Database as Data source. First I created a report(Tabular) using the following query –

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName, FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey

and pfb the report screenshot.

At this point of time there are no Parameters defined and hence you will get all the data from the database and will be displayed in the report. PFB the steps to be followed to ADD PARAMETERS to your report –

• Right click on Datasource and select “Add Dataset” option.

• Select the data source and provide the query which pulls values for parameter and as I am adding “Category” as parameter I am using the following query.

• When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
• Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Chnage the default value given for NAME and PROMPT if required.

• Now select the “Available Values” tab which is on the Left hand side of the wizard and select the option “Get Values From a Query”. Provide the Dataset name,Value Field and Label Field as shown below –

• Click on OK and with this our First parameter is added to report.

As we are gonna design cascading parameters, the remaining parameters creation changes a bit as those are depended on other parameters and here let us see how to define the parameter for Sub Categories.

• Right click on Datasource and select “Add Dataset” option.

• Select the data source and provide the query which pulls values for parameter and as I am adding “Sub Category” as parameter I am using the following query.

• The above query has a WHERE clause and @ReportParameter1 as varaible. This is because the values of SubCategory parameter is based on the selection of Categories parameter.
• Now select parameters tab on left hand side and select the “Parameter Value” from the list of available parameters as shown below.

• When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
• Right click on “Parameters” and select “Add New Parameter”.
• When the wizard opens, Change the Prompt and Name if you wish to provide proper names.
• Go to the “Available Values” tab and select the option “Get Values from Query” and select the values as shown below.

• Select OK and you can see your first cascading parameter got created.
• Now repeat the same step to create one more parameter “Products” whose value is depended on selection of “SubCategories” and PFB the query for the same.

select ProductKey,EnglishProductName as “Products” from dimproduct
Where ProductSubCategoryKey = @ReportParameter2

Now all the report parameters are ready. Here the sequence of parameters are as follows – Category –> Sub Category–> Product and you can see the same in the below given screenshot.

If you see the above screenshot the  Parameters looks like working fine i.e when u Sub Category is listing only those records which belongs to the category selected and the same for Products too but if you see the chart area the data doesn’t look correct. This is because we added parameters but not linked those parameters to the Main dataset from which we are getting data and filling the chart. Now I will modify the Main dataset in order to fetch data by applying parameter values. Edit the main dataset and update the query as shown below –

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey Where DimProduct.ProductKey = @ReportParameter3 and DimProductSubCategory.ProductSubCategoryKey = @ReportParameter2 and DimProductCategory.ProductCategoryKey = @ReportParameter1

Now Select Parameters tab and map the parameters as shown below.

Click on OK and your report is ready. Go to preview tab and test you report. You can see Parameters cascading and the Report data get filtered as shown below –

That’s it .. Your Report with Parameters is Ready .. Hope you understood the concepts .. Happy Coding !!

Regards,

Roopesh Babu V