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

SELECT        
	Name, 
	ItemID
FROM
	Catalog
WHERE
	Type = 2
ORDER BY 
        Name ASC

NEW: Dataset to populate the dropdown

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

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

< Prev		Next >