SSRS Dropdown parameter cannot be blank!

What?
I have created an SSRS report which can compare 4 reports side by side and brings up their latest execution times to the nearest millisecond. The report has 4 parameters. Each parameter is a dropdown populated by a list of all available reports.

Why?
I want the report to be run with the 3rd and 4th parameter as OPTIONAL. When I leave the 3rd and 4th parameter untouched (="<Select a value>"), the report complains saying "Report #3 parameter cannot be blank!". Before you ask, I have ticked both "Allow Blank" and "Allow NULL".

How?
This is the tough part. I was reading up on the MSDN page for the closest solution but it still didn't work for me. But the idea of inserting a NULL entry to select sounded good.

Aim / Objective
The plan will be to replace the default "<Select a Value>" with a custom null entry and the end-user will be none the wiser.

OLD: Dataset to populate the dropdown
copyraw
SELECT        
	Name, 
	ItemID
FROM
	Catalog
WHERE
	Type = 2
ORDER BY 
        Name ASC
  1.  SELECT 
  2.      Name, 
  3.      ItemID 
  4.  FROM 
  5.      Catalog 
  6.  WHERE 
  7.      Type = 2 
  8.  ORDER BY 
  9.          Name ASC 

NEW: Dataset to populate the dropdown
copyraw
SELECT        
	d1.Name, 
	d1.ItemID
FROM            
	(
	SELECT        
		Name, 
		ItemID
	FROM
		Catalog
	WHERE
		Type = 2

        UNION ALL

	SELECT TOP 1 
		'<Select a Value>' AS Name, 
		CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000') AS ItemID 
	FROM 
		ExecutionLogStorage
	) AS d1
ORDER BY d1.Name ASC
  1.  SELECT 
  2.      d1.Name, 
  3.      d1.ItemID 
  4.  FROM 
  5.      ( 
  6.      SELECT 
  7.          Name, 
  8.          ItemID 
  9.      FROM 
  10.          Catalog 
  11.      WHERE 
  12.          Type = 2 
  13.   
  14.          UNION ALL 
  15.   
  16.      SELECT TOP 1 
  17.          '<Select a Value>' AS Name, 
  18.          CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000') AS ItemID 
  19.      FROM 
  20.          ExecutionLogStorage 
  21.      ) AS d1 
  22.  ORDER BY d1.Name ASC 
Additional Modifications:
For all Parameters:
  1. » General - Untick "allow blank value" and "allow null value" (so these checkboxes are empty).
  2. » Available Values - "Get values from a query" = Select the dataset of the dropdown options.
  3. » Default Values - "Get values from a query" = Select the dataset of dropdown options, pay attention to the value in "Value field" (as I kept putting the label rather than the value field) to make this all work.

Notes:
My convert part of my SQL query is because I had to match the datatype used by our reporting server for the ItemID field which is some sort of hexadecimal ID value that Microsoft are so very fond of. You will need to use your own head to match the datatypes for including a dummy row.

Also note that because I don't have any options matching the ID above ("000000-0000-..."), this worked perfectly for me as my report didn't compare the 2 remaining dummy reports it couldn't find. It doesn't even mention them anywhere in the resulting report.

One Issue
The report will always load when opened defaulting to having all parameters set to the dummy value. For me it just opens an empty report with the charts saying "No Data Available" but I find this better then the alert message "parameter cannot be blank!" and less confusing to the end-user. If this bugs you, you can always include a custom message which displays on top if all 4 parameters are equal to "<Select a Value>"; such as the instructions on selecting a dropdown option. :c)

Searches that didn't get me very far
  • ssrs dropdown to allow null value
  • ssrs allow blank dropdown
Category: SQL Server Reporting Services :: Article: 407

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.