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.
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 Report is designed with Parameter “CatID” . Now Click on the ‘+’ sign on DataSets Option shown in the above image.
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’.
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.
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.
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.
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.
Right click on the white space below the Header “Chapter-17….” And insert a TextBox as shown
And now right click on the newly inserted TextBox and choose “Expression….” As shown
An Expression Properties window will appear set or paste the following expresion to the spave provided
=”Product Category: ” & Parameters!CatID.Label
And click on OK Button , Category Header TextBox is Created , Now Preview the report.
This completes the Chapter-17.
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