Monday, November 20, 2017

How do I make dropdown list controls in SSRS reports?

When creating a parameter at "Parameters" within the "Report Data" pane in Microsoft SQL Server Report Builder as suggested here a dialog with buttons for General, Available Values, Default Values, and Advanced at the upper left will appear dubbed "Report Parameter Properties" and changing the "Data type:" at the General subsection will change the type of control. The options are Text, Boolean, Date/Time, Integer, and Float and Text tends to make a fill-in-the-blank text field while Date/Time is going to create a calendar control of sorts. To make a drop down list, start off with a Text type control and then switch over to Available Views where "Select from one of the following options:" needs to change from "None" to "Get values from a query" in the name of procuring a set of options. You will be prompted to pick a Dataset. When setting up the "Query type:" for the Dataset it may be wise to select the option for "Stored Procedure" in lieu of the option for "Text" where you could just free form some SQL. The reason is that you may find yourself repurposing queries for dropdowns. It is somewhat tricky to have a nullable option for a dropdown. You may solve the problem with a query like so:

Declare @Options TABLE
(
   CompanyId uniqueIdentifier null,
   CompanyName varchar(100) null
)
INSERT INTO @Options (CompanyId, CompanyName) VALUES (null, null)
INSERT INTO @Options
SELECT CompanyId, CompanyName FROM Company
SELECT * FROM @Options

No comments:

Post a Comment