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
