sql-serverreporting-servicesssrs-2012rdl

SSRS Report parameters cannot pass empty values to stored procedure

I have a SSRS report. When I click button 'View Report', all records are displayed by considering all filters present.

I have dropdown report parameter, which is distinct values of 'Location'. Everything works fine, but when 'Location' has empty values, it fails to work.

When I click button 'View Report', a stored procedure will be called and records are displayed.

My stored procedure is A okay.

The problem is my stored procedure doesn't get any empty values at all from SSRS reports.

Screenshots of dropdown report parameter:

enter image description here

enter image description here

enter image description here

Dropdown in report looks like this:

enter image description here

When I click button 'View Report', the stored procedure must call like

SP_DisplayResults @Location=N',CM,SC'

But since blank value is not passed from ssrs report, it is executed like

SP_DisplayResults @Location=N'CM,SC'
Solution

  • I've done something similar before by replacing the blank value in the Available Values dataset with a sort of place holder value that is then handled in the stored procedure.

    e.g. in your DistinctLocation dataset query do something like

    SELECT DISTINCT CASE 
WHEN LocationType = '' THEN '(blank)' 
ELSE LocationType 
END as LocationType
FROM Location

    Then in your SP_DisplayResults stored procedure you can handle the '(blank)' value