asked
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?
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')
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?
@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.
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.
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?
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!
at least the condition with @Action param should be: `... AND (MXMSERVCALL.ACTIONID IN (@Action) OR (MXMSERVCALL.ACTIONID IS NULL AND '-1' IN (@Action)))`
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
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.
Can anyone please help?
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?
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
@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.
@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.
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.
But then the MXMSERVCALL.ACTIONID is not NULL as you have described originally, but is an empty string.
