Chapter-17 Adding or Creating Drop Down Parameter List in SSRS

By Default Parameters are created as TextFields in SSRS, but it can be converted to Drop Down List to select one value from all available values and based on the parameter reports brings data. The steps for creating Drop Down parameter in SSRS are.

Download Full Tutorial of Chapter-17-CreatingDropDownParameterinSSRS



Download Source Code for Chapter-17 Adding or Creating Drop Down Parameter List in SSRS

Step-1: Designed a simple tabular SSRS Report using the following query

SELECT ProductName,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock FROM Products where CategoryID=@CatID

simple tabular SSRS Report

Simple tabular SSRS Report

Simple Tabular Report is designed with Parameter “CatID” . Now Click on the ‘+’ sign on DataSets Option shown in the above image.



Add Dataset SSRS

Now right click on Datasets and click on ‘Add Dataset’ Option as shown a DataSet Properties windows will appear provide appropriate Name to the DataSet we had took “DSCategory”  and choose ‘Use a DataSet Embeded in my Report’.

. Now Click on the ‘+’ sign on DataSets Option shown
Now Click on the ‘+’ sign on DataSets Option shown




Step2 :  select “NorthwindDataSource” and then paste the following query to the space provided after choosing ‘Text’ radio button as shown and click on OK Button, a New Data with name “DSCategory” is created. Now click on Parameters and the right click on “CatID” and select Parameter Properties as shown.

Parameter Properties

A ‘Report Parameter Properties’ windows will appear. Change the Prompt to ‘Choose Category’ in the ‘General’ tab. Now choose ‘Available Values’  tab and Get Value form a Query radio Button and set ‘Dataset’,’Value Field’ and ‘Label Fields’ to ‘DSCategory’, ‘CategoryID’ and ‘CategoryName’ as shown in the image and click on OK Button.



click on ‘Add Dataset’ Option as shown a DataSet Properties
Click on ‘Add Dataset’ Option as shown a DataSet Properties

Now Preview the Report and Note Now Parameter Value changes to ‘List’ giving the name of all the avaibale categories in the ‘Categories’ table now click on ‘Preview Report’ button.

Choose category

Step3: Please note in the Report the Products coming under category “Beverages” is only coming but on the report header the name of Cateory is not coming to do this open the report in the design view.

preview report

Right click on the white space below the Header “Chapter-17….” And insert a TextBox as shown

add text box for report parameter

And now right click on the newly inserted TextBox and choose “Expression….”  As shown

expression

An Expression Properties window will appear set or paste the following  expresion to the spave provided

=”Product Category: ” & Parameters!CatID.Label

paste expression

And click on OK Button , Category Header TextBox is Created , Now Preview the report.

Final Preview of report

This completes the Chapter-17.

Related Posts:



Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report

Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS

Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

14 + 14 =