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

avatar image

bigweed
51 5 5 6

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

5 answers: sort voted first

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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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

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

avatar image

bigweed
51 5 5 6

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

Can anyone please help?

more ▼

answered Nov 24, 2010 at 09:27 AM

avatar image

bigweed
51 5 5 6

(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

avatar image

bigweed
51 5 5 6

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

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

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

avatar image

bigweed
51 5 5 6

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

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:

x2034
x709
x95
x42

asked: Nov 23, 2010 at 08:57 AM

Seen: 12959 times

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

Copyright 2018 Redgate Software. Privacy Policy