SSRS - Adding static null to parameter query

Question

I'm looking for a way to add a value to a parameter whose available values are retrieved from a simple query. I just started messing with SSRS today, I hope I'm not doing it all wrong.

Executing the following procedure from the report project (messy, I know):

CREATE PROCEDURE RAW_TIME_VAR
        @EMPLID as varchar(6) = NULL
,       @EMPLNAME as varchar(80) = NULL
,       @CHARGENO as int = NULL
,       @TYPE as varchar(8) = NULL
,       @STARTDATE as date = NULL
,       @ENDDATE as date = NULL
AS
Select * from DBO.RAW_TIME
Where   ([EMPL ID] = @EMPLID OR @EMPLID IS NULL)
AND     ([EMPL NAME] = @EMPLNAME OR @EMPLNAME IS NULL)
AND     ([CHARGE NO] = @CHARGENO OR @CHARGENO IS NULL)
AND     ([CHARGE TYPE] = @TYPE OR @TYPE IS NULL)
AND     [CHARGE DATE] Between @STARTDATE AND @ENDDATE

There are two datasets in the Report Project (I'm using VS2008 Business Intelligence Development Studio). One generated from the procedure above (RAW_TIME_DATASET), and the other a query from the same view to populate employee ID's and names (NAMES):

SELECT DISTINCT [EMPL ID], [EMPL NAME]
FROM RAW_TIME
ORDER BY [EMPL ID]

I want to be able to populate the @EMPLNAME parameter with all employee names from the NAMES dataset while also being able to pass NULL by selecting value "All" in the parameter combobox. "All" can be passed by the query by using UNION, which places it in the combobox without it being in the View, but I cannot then pass it through the report as NULL. Errr... I hope this makes sense.

Solution 1

you are on the right track.

If you check the report_data tab, you will see a parameters folder. Expand it and you should see all the parameter from your initial query. Right click the EMPLNAME parameter, select parameters properties, go to available values and do the following configuration:

also, on the general tab, mark the parameter to accept null values

now, the trick is on the NAMES dataset. You have to select all the names, plus a row with a null value. There are a few ways of acomplish it, the easiest is add something like this to your query:

union SELECT null as [EMPL ID], 'NULL' as [EMPL NAME]

If you don't, the report will complain that the parameter cannot be null.

result (I only added 2 parameters for simplicity):

Solution 2

Edit: note that this answer will only work if the select is in the dataset, but not if the select is inside the stored procedure. See comments below the answer.

If you edit the @EMPLNAME parameter you can set it to be "allow multiple values" (msdn sample). If you do this, you have to update the where-clause-bit in your other query like this:

AND     ([EMPL NAME] IN (@EMPLNAME) OR @EMPLNAME IS NULL)

I believe SSRS will replace the (@EMPLNAME) bit with a comma-separated list before executing the query (you can't use this "IN @param" syntax in regular (non-dynamic) SQL afaik. Because employee name is a string heed the warning in the linked MSDN article and only combine it with a available-values list to prevent security issues.

Edit: the above is (hopefully) what you're strictly asking for, but wouldn't it make more sense to combine the @EMPLID parameter with the @EMPLNAME parameter? You could make the ID parameter multi-value, and use the ID as a value and the NAME as a label. With that you'd end up with the following main dataset query:

Where   ([EMPL ID] IN (@EMPLID) OR @EMPLID IS NULL)
-- AND     ([EMPL NAME] = @EMPLNAME OR @EMPLNAME IS NULL) -- not needed anymore

Comments

[JeffO]

Are you getting the error in your report code? You may be trying to do some null string conversion.

[Jaxaeon]

This doesn't answer my question, but it does propose a more efficient way of approaching the issue! Initially it's only working with one Employee selected (multiple & no selections are returning nothing). I assume this is being caused by how SSRS is formatting the selected list and passing it to the procedure. I tried the join trick to manually add the comma's to separate selected values: =Join(Parameters!EMPLNAME.Value,",") But it's still not happy. I might try to handle the parametrization within SSRS instead of the procedure? "The data source cannot be a stored procedure."

[Jeroen]

Glad I could help. Not sure though what the leftover problem is. Where do you do that Join? In the parameter default value?

[Jeroen]

@Jaxaeon Oi never mind, my bad. I now understand why you need to join, somehow the fact that your SSRS dataset calls a stored proc didn't get through to me. Basically my answer then only works if you do the select in the report and not a procedure. The join trick should then be the way to go for you. If you reach a solution, don't forget to post (and accept) it as an answer.