How to create Parameterized report in SSRS 2012

Posted on April 24, 2013 by uday arumilli


How to create Parameterized report in SSRS 2012

udayarumilli_sql_server_2012_ssrs

In the previous post we have created a simple report. We are going to use the same report to demonstrate the parameterized report.

 

 

Under the same report solution copy and paste the Simple_Report and rename it as “Parameterized_Report”.

Now we can modify the report to accept parameters.

 

Now create a new stored procedure to accept the parameters as below.

 

USE[DBAConnect]
GO
CREATEPROCEDURE[dbo].[usp_Sales_Report_Parameterized](
@OrganizationVARCHAR(100)=’ALL’,
@CountryVARCHAR(100)=’ALL’,
@ZoneVARCHAR(20)=’ALL’,
@ProductVARCHAR(100)=’ALL’,
@YearCHAR(4)=’ALL’)
AS
BEGIN
SETNOCOUNTON
DECLARE@FilterVARCHAR(1000),@SQLVARCHAR(8000)
SET@Filter=”

 

IF (@Organization<>’ALL’)
BEGIN
SET@Filter=’ AND Organization = ”’+@Organization+””
END

 

IF (@Country<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Country = ”’+@Country+””
END

 

IF (@Zone<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Zone = ”’+@Zone+””
END

 

IF (@Product<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Product = ”’+@Product+””
END

 

IF (@Year<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND SYear = ”’+@Year+””
END
SET@SQL=’
SELECT”Organization”=Organization,
”Country”=Country,
”Zone”=Zone,
”Product”=Product,
”Year”=SYear,
”Total_SoldOut”=Total_SoldOut,
”Total_Price”=Total_Price
FROM[dbo].[SALES]
WHERE1 = 1 ‘+@Filter

 

PRINT@SQL
EXECUTE (@SQL)
END

 

I have written a stored procedure to accept parameters and retrieve result set dynamically. Now we’ll change the report. Follow below steps.

Change the procedure execution statement to
“EXEC [usp_Sales_Report_Parameterized]  @Organization”



Add a new data set to support List of Organizations as an input to the report.

Use the query to retrieve the input set
SELECT’ALL’ASOrganization  FROMSALES
UNION
SELECTDISTINCTOrganizationASOrganizationFROMSALES;


Now map the newly created dataset to the parameter “Organization”.




Now preview the report, by default “ALL” is selected for Organization hence all records retrieved.

Now select an organization from the list.


Now let’s include all parameters as below.
  • @Country
  • @Zone
  • @Product
  • @Year

 

Parameters are added. Now add datasets to provide inputs for parameters as below.

 

DataSet :
Country:
SELECT’ALL’ASCountry
UNION
SELECTDISTINCTCountryASCountryFROMSALES

 

Zone:
SELECT’ALL’ASZone
UNION
SELECTDISTINCTZoneASZoneFROMSALES

 

Product:
SELECT’ALL’ASProduct
UNION
SELECTDISTINCTProductASProductFROMSALES

 

Year:
SELECT’ALL’AS[Year]
UNION
SELECTDISTINCTSYearAS[Year]FROMSALES

 

As we have done mapping between dataset and parameter for “Organization” follow those steps and map all datasets to corresponding parameters. Finally it looks like as below.

Now preview the report to check the output. As we designed by default all parameters are shown as “ALL”

Now select Organization and Country values.

Now select Zone and Product values as below.

This is a simple way to implement parameterized reports.

 

Related:

Posted in MSBI, SQL Development, SSRS | Tagged , , , , , , , , , , , , | 1 Comment
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Download Niche PLR Articles
Download Niche PLR Articles
11 years ago

Very nice post. I certainly appreciate this site. Stick with it!

0
Reply