SSRS Report Builder Part 11.2 - Optional Drop Down List Parameters

By Andrew Gould

Chapters
00:00 Topic List
00:36 Creating the Basic Report
03:39 Creating a Basic Drop Down List Parameter
06:17 Adding an "Any" Option to the Drop Down List
07:41 Checking for the Any Option in a Query
09:05 Allowing Null Values
11:12 Assigning a Default Value of Null
12:22 Displaying the Parameter Label in the Report
14:44 Using Report Parameters and Filters
18:11 Query Parameters vs Report Parameters and Filters
19:26 Creating a Drop Down List of Months
22:06 Making the Month Parameter Optional
24:03 Showing the Month Name in the Report
24:48 Using a Filter for the Month Parameter
27:10 Populating a Drop Down List with a Dataset
30:04 Adding an "Any" Option to a Dataset
33:11 Checking for Null in the Dataset Query
34:04 Sorting the Genre List
37:30 Including the Genre Label in the Report
38:38 Creating a Dataset to List Directors
40:46 Creating the Report Parameter
41:52 Creating the Dataset Filter
44:02 Displaying the Director Name in the Report
44:58 Converting to a Query Parameter
46:28 Using a Query to Populate the Oscar Wins List

Learn how to make drop down list parameters optional in SSRS Report Builder. You'll learn how to allow null values for a parameter, how to add this to the list of available values and how to test for nulls in both a query and a dataset filter. You'll also learn how to use UNION queries to add a null option to a dataset and how to use the dataset to populate the available values of the parameter.

[MrShahidalik]

Thank you for clear explanation, It helped me resolve an urgent request. I needed to run run a report where 3 were mandatory inputs and with 6 optional dropdown lists.

[abdullahquhtani]

Thank you for another great video of this series. I think there is an easier solution to sort Genre list without adding an extra column simply by adding space before Any genre is sql statement so it looks like this
N’ Any genre’
In that way it will be at the top of the list since space sorted before any character (Numbers and letter)
That goes for directors list as well or any other similar list.
Thank you.

[sandhyan]

Thankyou for the clear explanation. Can I please know how can I add "Any" option when i use filter function in the tablix properties when I have varchar values to filter.

[TheDjblane]

It is possible to add a local filtering to drop down? when I have large data set?

[phillipk]

Thanks a lot!. In a SSRS report. The database has 1, 2, 3 as Good, Fair and Poor. But the parameter show during runtime as 0 - All conditions then 1, 2, 3. How do you create a parameter 0 as being All? Thanks

[hpermonth]

Is it possible to add option to select yes or no in report body after exporting as an excel

[scotolivera]

Hello sir, are you planning for a csharp tutorial in the near future?

[HiDef]

I have a list being brought over dynamically from the DB how can i place a "Select any" value to the drop down, so I can display all items ?

[zzzzzzzaaaaaaaaaaaaaaa]

Hi, what is SSRS Report Builder? Is it connecting to Excel?