Parameters using Stored Procedures in SSRS Reports

Friends,

Here in this post we are gonna discuss about how to use SP(Stored Procedures) to get input for Parameters in SSRS Reports. If you wish to define parameters using Sql Queries then you can find the sample here. For this demonstration purpose I Created three Stored procedures with the following names –

• sp_getcategories – To pull categories
• sp_getsubcategories – To pull Subcategories
• sp_getProducts – To pull products

All the above three SPS are pretty straigh forward and you can find code below.

Create procedure sp_getCategories
as
select ProductCategoryKey,EnglishProductCategoryName as “Categories” from dimproductcategory
Go

Create procedure sp_getSubCategories @CatID int
as
select ProductSubCategoryKey,EnglishProductSubCategoryName as “SubCategories” from dimproductsubcategory
Where ProductCategoryKey = @CatID
Go

Create procedure sp_getProducts @SubCatID int
as
select ProductKey,EnglishProductName as “Products” from dimproduct
Where ProductSubCategoryKey = @SubCatID

If you see the code for all the three SPS, the first one has no parameter and it pulls all the Categories. The second one pulls Subcategory names based on the CategoryKey we pass. The third one pulls Products info based on the SubcategoryKey we pass. PFB the screenshot showing the result set of the SPS.

Now lets design a report with THREE parameters which pulls data using SPS. 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 using SPS as source –

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

• Select the data source and select the option “Stored Procedure” radio button and provide the Stored Procedure name which pulls values for parameter and as I am adding “Category” as parameter I am using the following stored procedure which pulls Categories data.

• As there is no INPUT parameters for this Stored Procedure we can continue by Clicking OK. When you click on OK , 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 using SP is added to report.
• As the remaining SPS has input parameters, the creation of those slightly changes when compared to the last one. Let’s create the parameters using SP and having input parameters to SP.
• Right click on Datasource and select “Add Dataset” option.
• Select the data source and select the option “Stored Procedure” and provide the SP name which pulls values for parameter.
• The above SP has a Input Parameter and hence to provide input value to the parameter select PARAMETERS tab and provide the Parameter value as shown below.

• When you click on OK by providing the query, you can see new data set added with TWO columns which we can use to define the second parameter.
• 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 repeat the same for the remaining parameter “Products” also.
• 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 which is sourced from Stored Procedures is Ready .. Hope you understood the concepts .. Happy Coding !!

Regards,

Roopesh Babu V