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')
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!
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?
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.