x

SSRS 2005 - Multi value parameter with null option??

I am a complete newbie to SSRS 2005 so please reply as if I have no idea! :o)

I have been asked to create a report at work. The report is based on a dataset called AllCalls. AllCalls is a list of service calls, with a column called Action ID. There is a multi value parameter for this report. The parameter uses a dataset called AllActions, which is a list of all Action ID's and Action descriptions. So far so good. The report works fine when I select a value in this parameter field when running the report. The correct data gets returned and all is well.

However, some of the service calls dont actually have a action code populated, so these service calls are not being returned if I 'Select all' on the parameter when running the report. I think the correct technical term is null values, but I am not sure. How do I show these service calls, which dont have an Action code, when I choose to 'Select all'? Ideally I would also have an option in the parameters dropdown called 'Blank' which would return all service calls which dont have a Action code populated. Can someone please help with this?

The table for Service Calls is 'Mxmservcall', the table name of the actions is 'Mxmservaction'. The column name for Action ID is 'Actionid' in both tables. The column name for Service call number in mxmservcall is JobID. The parameter name is Action.

In the parameter dataset, in the code window, I already have the following code:

SELECT ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM MXMSERVACTION
WHERE (DATAAREAID IN (@CompanyID))
ORDER BY ACTIONID
Can anyone please help?
more ▼

asked Nov 23, 2010 at 08:57 AM in Default

bigweed gravatar image

bigweed
51 5 5 5

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

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.
more ▼

answered Nov 26, 2010 at 04:27 AM

bigweed gravatar image

bigweed
51 5 5 5

But then the MXMSERVCALL.ACTIONID is not NULL as you have described originally, but is an empty string.
Nov 26, 2010 at 05:23 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Nov 25, 2010 at 12:28 AM

bigweed gravatar image

bigweed
51 5 5 5

You need again to assign the parameter as I mentioned in some previous answers. Then put the values to some table in the reports. And preview the report. Also again you have the condition wrong in your code.. you are missing parentheses.

you have:

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

but should be:

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

The parentheses must be there as AND has higher precedence than OR. Check [BOL][1] for details.

Check also, the you have correct parentheses in your original query as I mentioned in my previous answer, that your problem should be wrong parentheses.

[1]: http://msdn.microsoft.com/en-us/library/ms190276.aspx
Nov 25, 2010 at 12:33 AM Pavel Pawlowski
To check the NULL side of the query you shouldn't be entering the word NULL for @Action, you should be entering -1 that should trigger the second half of the WHERE clause into returning all records where the ID is null.
Nov 25, 2010 at 12:57 AM BarneyL
@BarneyL, he should have '-1' in the @Action parameter with description '(Blank)'. But as I wrote, he will probably have problems with parentheses in the dataset using the @Action parameter.
Nov 25, 2010 at 01:01 AM Pavel Pawlowski
@Pavel, Yes it should be fine using the parameter list (and having applied your change to the parentheses). His last post up from here though mentions manually typing in the word NULL. I'm guessing he's either running the query from the data tab or hasn't linked the parameter to the query that is supposed to be listing the options and so getting a free text box.
Nov 25, 2010 at 01:24 AM BarneyL
(comments are locked)
10|1200 characters needed characters left
Can anyone please help?
more ▼

answered Nov 24, 2010 at 09:27 AM

bigweed gravatar image

bigweed
51 5 5 5

(comments are locked)
10|1200 characters needed characters left

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!
more ▼

answered Nov 24, 2010 at 04:26 AM

bigweed gravatar image

bigweed
51 5 5 5

at least the condition with @Action param should be:

... AND (MXMSERVCALL.ACTIONID IN (@Action) OR (MXMSERVCALL.ACTIONID IS NULL AND '-1' IN (@Action)))
Nov 24, 2010 at 04:56 AM Pavel Pawlowski
When I change it to the above, I still only get 2 records in the report, which are the only ones with an Action code present. Any further clues?
Nov 24, 2010 at 06:08 AM bigweed

Pavel, can you tell me where I can use the below information as I am not sure where it needs to be in the above code:

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

Maybe this is the solution, but I dont know how to use it! Is it to be inserted in the Where clause in the ServiceCalls dataset, or in the AllAction dataset somewhere? Thanks
Nov 24, 2010 at 06:11 AM bigweed

Check your data and conditions.

I've tested it with a sample dataset below and it worked correctly

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)))
You can create similar and put some of your real ACTIONID values as ID in the sample dataset. then try, whether it returns the data correctly for the @Action parameter. If yes, then you have problem somewhere in your condition. Maybe parentheses etc.
Nov 24, 2010 at 10:54 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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')

more ▼

answered Nov 23, 2010 at 10:38 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Hi Pavel

Thanks for the response. I get an error when doing the above, saying :

Query execution failed for dataset AllAction

Must declare the scalar variable @CompanyID

This only happened after I put in the above code to create a new parameter value in the parameter dataset. Also, the Filter column disappears in the AllAction dataset, when I run the above code. I noticed this before, whenever I have a UNION statement the Filter column disappears. The CompanyID parameter was in the filter column before it disappeared, in the 'dataareaid' row. Now there is an error with the CompanyID filter. It must be the WHERE statement, surely? Any clues as to what is happening?
Nov 24, 2010 at 01:22 AM bigweed
@bigweed check that you have correctly assigned parameters to the dataset after the modification. It happens that the BIDS broke the associations when you modify the query in dataset.
Nov 24, 2010 at 01:51 AM Pavel Pawlowski
How do I assign parameters to dataset after modification?
Nov 24, 2010 at 02:36 AM bigweed
Don't have the BIDS 2005 on hand, but somewhere in the dialog where you edit the dataset, there is a tab "Parameters" where where you can assign parameters to your query.
Nov 24, 2010 at 02:40 AM Pavel Pawlowski

Hi, its almost working now! Many thanks for your help!

Now I have the following code in the parameter dataset:

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

UNION

SELECT     NULL AS ACTIONID,
           '(Blank)' AS actiondescription,
           'Null dataareaid' as DATAAREAID
FROM        MXMSERVACTION AS MXMSERVACTION_1
WHERE     (DATAAREAID IN (@CompanyID))
ORDER BY ACTIONID

However, when I preview the report there is no value in the Action parameter dropdown called 'Blank'. How do I get it to show in there?

Nov 24, 2010 at 03:04 AM bigweed
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x553
x74
x31

asked: Nov 23, 2010 at 08:57 AM

Seen: 11113 times

Last Updated: Nov 24, 2010 at 03:17 AM