Report Parameters in SSRS 2014

In SQL Server Reporting Services, Report Parameters allows the users to enter specific value in the text Box and then, SSRS will filter the Report data using the user specified value. OR Report Parameters allows the users to Dynamically Filter the SSRS Reports. In this article we will show you, How to add Report parameters in SQL Server Reporting Services 2014 with example.

We are going to use below shown report to explain, Report parameters in SSRS Reports. Please refer SSRS Table Report article to understand the creation of Table report. If you observe the below screenshot, It was a normal Table report with Country, Full Name, Occupation, Yearly Income  and Sales Amount columns.

Below screenshot will show you the, Data Source and Dataset we used for this report.

SQL Command we used in the above screenshot is:

T-SQL QUERY

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SELECT Geo.[EnglishCountryRegionName] AS [Country]       ,Geo.[StateProvinceName] AS [State]       ,Geo.[City]       ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]         ,Cust.EnglishEducation AS Education       ,Cust.EnglishOccupation AS Occupation       ,SUM(Cust.YearlyIncome) AS YearlyIncome       ,SUM(Fact.SalesAmount)AS SalesAmount FROM  DimCustomer AS Cust   INNER JOIN       FactInternetSales AS Fact ON Cust.CustomerKey = Fact.CustomerKey INNER JOIN      [DimGeography] AS Geo ON    Cust.GeographyKey = Geo.GeographyKey GROUP BY Geo.[EnglishCountryRegionName]          ,Geo.[StateProvinceName] ,Geo.[City] ,Cust.FirstName          ,Cust.LastName            ,Cust.EnglishEducation          ,Cust.EnglishOccupation   ORDER BY [Country]

Adding Report Parameters in SSRS 2014

Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.

• Name: Please specify the valid Parameter name as per your requirement. Here, We specified it as CountryParameter
• Prompt: The text you specified here will be displayed as label before the text box
• Data Type: Country name is a Text data type so, we kept the default text unchanged

Click Ok to finish configuring the Report parameters in SSRS. Now, We have to apply filter condition on dataset. Please refer Filter at Dataset Level in SSRS article to understand the creation of Filters.

Select the Dataset from the Report data tab and Right click on it will open the context menu. Please select the Dataset Properties.. option from it

In this example, We are going to display the records whose [Country Name] is equal to [Country Parameter] we just created so, Please select the Country as Expression and Operator as Like Operator and Value as Parameter name. If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.

Click Ok to finish configuring the Filters at Dataset Level. Lets Click on Preview Tab to preview the Data.

From the above screenshot you can observe that, It is displaying Blank Report with Empty Textbox on Top. In order to display the records, We have to enter the Country Name in that Text box and Press Enter.

For now, We are entering the Germany as Country Name.

NOTE: If we exactly know the Parameter values (Country Names in this example) then there will be no problem. How about remembering 100 Employee Names or Product Names?. In these situations, We have to use Drop Down List parameters

Thank You for visiting Our Blog.