SSRS 2005 - Multi value parameter with null option??

I have managed to create a much simpler solution, after many attempts. @Action parameter dataset is now:

SELECT     ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM         MXMSERVACTION
WHERE     (DATAAREAID IN (@CompanyID))
UNION
SELECT     '' AS ACTIONID, '(Blank)' AS ACTIONDESCRIPTION, @CompanyID AS DATAAREAID
ORDER BY ACTIONID

This returned a ‘Blank’ value as well as all available action codes, in the parameter dropdown when running the report. I then referenced the parameter dataset in the main dataset as normal:

WHERE (MXMSERVCALL.ACTIONID IN (@Action))

Job done!

Thanks for the help guys, much appreciated, I think I may have underestimated SQL a little… but I have progressed my SQL understanding quite a bit so its all good.

Where do I enter the code you have created above? I wanted to test out the code and see if it will do what I need but I dont know how to test it. I have tried it by creating a new dataset in my SQL report in Visual Studio, depress the 'Generic Query designer' button, then pasting the following into the SQL pane:

    WITH Data AS (

    SELECT 'a1' AS ID, 'a1' AS VAL UNION ALL
    SELECT 'a2' AS ID, 'a2' AS VAL  UNION ALL
    SELECT 'a3' AS ID, 'a3' AS VAL  UNION ALL
    SELECT 'a4' AS ID, 'a4' AS VAL  UNION ALL
    SELECT 'a5' AS ID, 'a5' AS VAL  UNION ALL
    SELECT 'a6' AS ID, 'a6' AS VAL  UNION ALL
    SELECT 'a7' AS ID, 'a7' AS VAL  UNION ALL
    SELECT NULL AS ID, 'b1' AS VAL  UNION ALL
    SELECT NULL AS ID, 'b2' AS VAL
    )

    SELECT
    *

    FROM Data

    WHERE (ID IN (@Action) OR (ID IS NULL AND '-1' IN (@Action)))

I change some of the ID values, then when I press Run, the code automatically changes to:

         WITH Data AS (SELECT    'BR01' AS ID, 'Breakdown test 1' AS VAL
         UNION ALL
         SELECT     'BR01' AS ID, 'Breakdown test 2' AS VAL
         UNION ALL
         SELECT     'BR01' AS ID, 'Breakdown test 3' AS VAL
         UNION ALL
         SELECT     'BR02' AS ID, 'Breakdown test 4' AS VAL
         UNION ALL
         SELECT     'BR02' AS ID, 'Breakdown test 5' AS VAL
         UNION ALL
         SELECT     'BR02' AS ID, 'Breakdown test 6' AS VAL
         UNION ALL
         SELECT     'BR03' AS ID, 'Breakdown test 7' AS VAL
         UNION ALL
         SELECT     NULL AS ID, 'Breakdown test 8' AS VAL
         UNION ALL
         SELECT     NULL AS ID, 'Breakdown test 9' AS VAL)

SELECT     ID, VAL
 FROM      Data AS Data_1
 WHERE     (ID IN (@Action)) OR
           (ID IS NULL) AND ('-1' IN (@Action))

I then get a message asking me to enter a value for @Action. If I enter either 'BR01', 'BR02' or 'BR03', the result is correct. If I leave the parameter empty and press Enter, I get no records returned. If I enter NULL, in either CAPS or as 'null', I still get no records returned, even though there should be 2 records being returned. Am I doing something wrong?

Can anyone please help?

I now have the following code in my AllAction parameter dataset:

SELECT     ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM         MXMSERVACTION
WHERE     (DATAAREAID IN (@CompanyID))
UNION
SELECT     '-1' AS ACTIONID, '(Blank)' AS actiondescription, DATAAREAID
FROM         MXMSERVACTION AS MXMSERVACTION_1
WHERE     (DATAAREAID IN (@CompanyID))
ORDER BY ACTIONID

Using -1 without the ' ' characters resulted in an error saying that the "conversion failed when converting nvarchar to int". So I assume that because the Action ID is a mixture of letter and number is what caused the error. So I kind of guessed that maybe I should put the -1 inside ' ' characters. Dont know if thats a bad thing or not, seeing as I have never used SQL before! And the following is my ServiceCalls dataset code:

SELECT     MXMSERVCALL.DATAAREAID, MXMSERVCALL.SITEID, MXMSERVCALL.JOBID, 
                MXMSERVCALL.LOGGEDDATE, 
                MXMSERVCALL.CUSTPURCHASEORDER, MXMSERVCALL.FAULTID, 
                MXMSERVFAULT.FAULTDESCRIPTION, MXMSERVCALL.ACTIONID, 
                MXMSERVACTION.ACTIONDESCRIPTION, MXMSERVCALL.DETAILS, 
                MXMSERVSITE.CUSTOMERACCOUNT, MXMSERVCALL.SCHEDULEDENGINEER, 
                MXMSERVCALL.STATUSID, MXMSERVCALL.LOGGEDTIME, MXMSERVCALL.VISITDATE,
                MXMSERVCALL.EQUIPMENTPRODUCT, MXMSERVCALL.EQUIPMENTSERIAL, 
                MXMSERVCALL.SITENAME

FROM         MXMSERVCALL INNER JOIN
                 MXMSERVSITE ON MXMSERVCALL.SITEID = MXMSERVSITE.SITEID AND
                 MXMSERVCALL.DATAAREAID = MXMSERVSITE.DATAAREAID INNER JOIN
                 MXMSERVFAULT ON MXMSERVCALL.FAULTID = MXMSERVFAULT.FAULTID AND 
                 MXMSERVCALL.DATAAREAID = MXMSERVFAULT.DATAAREAID LEFT OUTER JOIN
                 MXMSERVACTION ON MXMSERVCALL.DATAAREAID = 
                 MXMSERVACTION.DATAAREAID AND MXMSERVCALL.ACTIONID = 
                 MXMSERVACTION.ACTIONID

WHERE     (MXMSERVCALL.DATAAREAID IN (@CompanyID)) AND (MXMSERVCALL.SITEID IN 
               (@Site)) AND (MXMSERVSITE.CUSTOMERACCOUNT IN (@Customer)) AND 
               (MXMSERVCALL.STATUSID IN (@Status)) AND (MXMSERVCALL.LOGGEDDATE >= 
               @StartDate) AND (MXMSERVCALL.LOGGEDDATE <= @EndDate) AND 
               (MXMSERVCALL.FAULTID IN (@Fault)) AND (MXMSERVCALL.ACTIONID IN (@Action)) 

OR           (MXMSERVCALL.ACTIONID IS NULL) AND ('-1' IN (@Action))

ORDER BY MXMSERVCALL.JOBID

When I preview the report, I get an option called '(Blank)' for the Action parameter. If I select this option, it returns nothing at all. I get a blank report. If I select all options within the Action parameter EXCEPT the '(Blank)' option, 2 records get returned, which are the only 2 records with an action code. However there are lots of service calls without an Action code against them. Am I doing something very obviously wrong? If I remove the Action parameter from the report, then 'Select all' for all the other parameters, all of the records get returned in the report. So it must be something to do with the Action parameter? Somebody please help!

Multi value parameters does not allow null values. You can make a workaround and add another item into the multi colum parameter representing a null value. Then interpret the value as null.

You can add the value like:

SELECT 
  ACTIONID, 
  ACTIONDESCRIPTION, 
  DATAAREAID 
FROM MXMSERVACTION 
WHERE (DATAAREAID IN (@CompanyID))

UNION ALL

SELECT
  'NULLID' AS ActionID, --maybe you will put some it value or what are your values in the first query
  'NULL Value Description' AS ACTIONDESCRIPTION,
  'NULL DATAAREAID' AS DATAAREAID 

 ORDER BY ACTIONID

Then when processing the selected values, you can use in your query you can convert the "NULLID" to NULL using NULLIF(yourColumnOrParameter, 'NULLID')