Parameterized Report in SSRS

SSRS provide a feature of Parameter with the help of it a user can supply value to filter the report. Parameters are used in a report to provide a dynamic feature by providing user input to the report execution process .Parameters are usually used to filter large set of records so as to narrow down the end report for better analysis.

For Example:-

I am using AdventureWorksDW2008R2 database. I want to generate report based on the product categories, product sub categories and product and want to know the total sales according to this group. Here I have three parameter Product categories, Product subcategories and product. This is cascading report.

Lest start step by step to create Cascading Report

Open the reporting services and create a new project.

In Solution Explorer Right click on Report -> Click on Add -> Click on new Item

You will get below screen.

Select the Report and write the report name and click add.

 Create a new data source.

Right click on Data source and click on Add Data source

A: - write the data source name

B: - you can used two type of connection

1.       Embedded connection

2.       Shared data source

There are following type of data source

C: - you can write the connection string on text box or Click on Edit button

Write the sever name and select the data base. Click on test connection.

Click on ok.

You will get the connection string here.

D: - if you want to use shared data source then select this option.

E: - click ok.

New data source is created.

Once data source is created we need to create dataset. For creating dataset right click on data set

Click on Add Dataset

Write the data set name. If you want to use shared data set you click on shared data set.

User a dataset embedded in my report and select Data source.

You can write the sql query in text otherwise you can use query designer

For creating the data set you can use sql query or stored procedure.

select ProductCategoryKey,EnglishProductCategoryName from DimProductCategory

Click on Fields. You will get the selected fields here.

Click on Options

Here you can set collection, case sensitivity, Accent sensitivity, width sensitivity etc.

If you want to create filter you can click on filter.

If you are using any parameter you can select Parameters and add new

Click ok.

New data set has been created.

Now we need to create

Right click on Parameter and select Add parameter and click.

A: -write the parameter name

B: - write the prompt name. This name will be display.

C: - select the data type of the parameter. There are following type of data type as below.

D: - There is the following option which you can set on the parameter.

Ø  Allow blank value

Ø  Allow null value

Ø  Allow multiple values.

E: - if you want to hide you parameter the select hidden.

   

Click on Available Values.

If you are selecting none. You will get the text box you have to supply the value.

If you are selecting Specify values you can select that.

Here I am  getting  values from query so I have selected Get values from a query option.

Select the data set. After that you have to select values fields and label fields.

If you want to select the default value display on the parameter. Select default value tab. Select get from a query. Select dataset and value fields.

 Select advance tab for refreshing the data.

Now click on ok.

Parameter has been created.

Now you can preview page you can see one drop down list has been created.

I have created one parameter similarly I am going to create other parameter for product sub category.

Create a new data set for Product sub category.

select ProductsubCategoryKey,EnglishProductSubCategoryName from DimProductSubcategory where ProductCategoryKey=@Prod_Catg

Here I am creating a variable and this value I will take from the product category parameter.

We can do setting as above describe. Now come to Parameter tab.

Click on add you will see the variable which is declare in query. Now we can map it which I have created.

Click on ok.

Now data set has been created.

Similarly we need to create a parameter for product sub category.

Now similarly I am going to create a data set for product.

Query for that

Select P.EnglishProductName,Count(*) as [Seles Unit],SUM(FIS.salesAmount) As [Total seles] from FactInternetSales FIS INNER JOIN DimProduct P ON P.ProductKey=FIS.ProductKey INNER Join DimProductSubcategory PSC ON PSC.ProductsubCategoryKey=P.ProductsubCategoryKey where P.ProductsubCategoryKey = @Prod_Sub_Catg group by P.EnglishProductName

Now see the report