Hi, Using SSRS 2005 I have a detail report that takes three parameters. Two are date parameters and are not a problem. I use the third parameter as a lookup for a staff ID. I have it setup as a multi-value, allowing blanks and pulls from a data set. This basically works, however sometimes this field is left blank in the database set as NULL. To also capture the NULL records I have the following in the WHERE clause of the dataset query: AND (staff.staff_id IN (@staffid) OR staff.staff_id IS NULL) The trouble with this is that if someone selects a sub set of the staff IDs the records with staff ID = NULL always gets reported too. I would like to change this so that if you select a specific staff ID only records with that staff ID get reported. If you select all staff IDs then all records would be reported including records with the staff ID set to NULL. It would really be nice if I could somehow select just the records that have NULL in the staff ID field as well. Does that make sense? Any suggestions on how to do this? I was thinking I could add a Boolean type parameter but I haven’t figured out how to structure it into the WHERE clause yet. Can you do an IF… THEN… ELSE… construct in a SQL Select WHERE clause? Thanks in advance, Linn

Hi Linn, If you can add following record in the selection collection for staff ID parameter: Value: 0, Label: "Not Used" then you can use modified query AND ISNULL(staff.staff_id, 0) IN (@staffid) Remember to mark as an answer if this post has helped you.

Hi Linn, I tried this it is working fine. Use like this WHERE (Isnull(Company, 'Null') In (@pOrg) Or (Company In (@pOrg))) Parameter query: SELECT DISTINCT IsNull(Company, 'Null') AS Org Let me know if it works or not. Thanks Tarak

Hi Igor, Sorry for the delay in responding. I'm not sure how I can add the Value: 0, Label: "Not Used" in the selection collection. I'm using the following query in the dataset: SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' I don't understand why I just can't mark the "Allow null value" in the report parameter dialog box. I tried it but it says it is not a valid property. Thanks, Linn

Hi Tarak, I tried your suggestion but then it excludes the records with NULL sales rep. I'm not sure I understand the parameter query suggeston. Here is what I am using currently: SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' Thanks, Linn

Hi, In you query parameter dataset where you are going to bind the parameter to the dataset try to convert the NULL values to some default value. If you are using the below query as your parameter dataset query SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' Then SELECT ISNULL(Staff.staff_id,'NULL') as Staff_id, Isnull(Staff.LastName,'NULL') as lastname From staff Where LEFT (Staff.Staff_ID,1)='5' HTH, Ram

Hi Ram, Thanks for the suggestion but I guess I’m not expressing my issue correctly. The issue isn’t that there are NULLs in the staff table, the issue is that there are NULLs in the main dataset query. It looks something like this: SELECT pt_case.sales_rep_staff_id, staff.last_name, pt.patient_id, CAST(RTRIM(pt.last_name) + ', ' + RTRIM(pt.first_name) AS varchar(40)) AS 'Patient', ar_detail.ar_accounting_period_code, SUM(CASE ar_detail.source WHEN 'CLAIM_LINE' THEN ar_detail.amount ELSE 0 END) AS 'Claimed', SUM(CASE ar_detail.source WHEN 'PREADJUST' THEN ar_detail.amount ELSE 0 END) AS 'PreAdjust', SUM(CASE WHEN ar_detail.source = 'ADJUSTMENT' AND (ar_detail.ar_adjustment_code_code = 'BE' OR ar_detail.ar_adjustment_code_code LIKE 'CA%' OR ar_detail.ar_adjustment_code_code LIKE 'C/A%') OR ar_detail.ar_adjustment_code_code LIKE '[0-9]' OR ar_detail.ar_adjustment_code_code LIKE '[0-9][0-9]' THEN ar_detail.amount ELSE 0 END) AS 'Adjustment' FROM pt_case JOIN patient AS pt ON pt.patient_id = pt_case.patient_id JOIN claim ON claim.case_id = pt_case.case_id JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id LEFT JOIN staff ON staff.staff_id = pt_case.sales_rep_staff_id WHERE ar_detail.transaction_date BETWEEN @start_date AND @end_date AND ar_detail.status IN ('BILLED', 'CREDIT', 'CREDITED', 'POSTED', 'ONCLAIM') AND (staff.staff_id IN (@salereps) OR staff.staff_id IS NULL) GROUP BY pt.patient_id, pt.last_name, pt.first_name, ar_detail.ar_accounting_period_code, pt_case.sales_rep_staff_id, staff.last_name ORDER BY pt_case.sales_rep_staff_id, pt.last_name, pt.first_name, ar_detail.ar_accounting_period_code The pt_case.sales_rep_staff_id will be NULL in some cases. What I would like is to be able to return a specific sales rep’s records, which I can do now. But I would also like to be able to return just the records that don’t have a sales rep associated with them. The way this query works now is that regardless of the sales rep selected in @salereps the NULL records are also returned. But I have no way to get just the NULL sales rep records. Does that make more sense? Thanks, Linn

Please correct me if i am wrong. When you execute the above query in the main dataset you get NULL values in the pt_case.sales_rep_staff_id and you need to group all those NULL values and show it in the report. If this is the case then modify your main dataset. You just need to add a ISNULL function and place a default value for the NULL records. SELECT ISNULL(pt_case.sales_rep_staff_id,'A') ,........................rest of the query Instead of A you can use soime other value so that you woint get any NULL records at all. But make sure you do the same changes to your parameter query as well if you are using the pt_Cas.sales_rep_Staff_id. Please let me know if this is not clear. HTH, Ram

Yes, I do think I understand where you are going with this. I am not allowed to add anything permanent to my staff table so the question is; How do I inject another record into the parameter query? Here is what I've come up with, let me know if there is another option. SELECT '1NULL' AS 'staff_id', 'No Sales Rep Specified' AS 'last_name' union SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' Then I modified the SELECT statement as follows: SELECT ISNULL(pt_case.sales_rep_staff_id, '1NULL') AS 'sales_rep_staff_id', ISNULL(staff.last_name, 'No Sales Rep Specified') AS 'last_name', But that doesn't work either. The NULL records are included no matter which sales rep I select. All this does is change the information that is displayed in the report, it doesn't actually change the way the report runs. Which makes sense. Seems to me that I need to affect the WHERE clause of the main dataset query.

Hi, Since you need to effect the selection of staffs with NULL value based on All Staffs selections in the parameter, I suggest the below 1. Add a dataset named datStaffCount and set its query as below    select count(*) as StaffCount FROM staff WHERE LEFT(staff.staff_id, 1) = '5' 2. Add a parameter named pStaffCount. Set its default value is to be from above dataset (datStaffCount) and select the value field as StaffCount 3. Now open the dataset that has the main query. Click on the expression button for the query and alter your query as given below ="SELECT  pt_case.sales_rep_staff_id, staff.last_name, pt.patient_id,  CAST(RTRIM(pt.last_name) + ', ' + RTRIM(pt.first_name) AS varchar(40)) AS 'Patient',   ar_detail.ar_accounting_period_code,    SUM(CASE ar_detail.source WHEN 'CLAIM_LINE' THEN ar_detail.amount ELSE 0 END) AS 'Claimed',  SUM(CASE ar_detail.source WHEN 'PREADJUST' THEN ar_detail.amount ELSE 0 END) AS 'PreAdjust',  SUM(CASE WHEN ar_detail.source = 'ADJUSTMENT' AND                 (ar_detail.ar_adjustment_code_code = 'BE' OR                 ar_detail.ar_adjustment_code_code LIKE 'CA%' OR                 ar_detail.ar_adjustment_code_code LIKE 'C/A%') OR                 ar_detail.ar_adjustment_code_code LIKE '[0-9]' OR                 ar_detail.ar_adjustment_code_code LIKE '[0-9][0-9]'                 THEN ar_detail.amount ELSE 0 END) AS 'Adjustment'   FROM pt_case JOIN patient AS pt ON pt.patient_id = pt_case.patient_id JOIN claim ON claim.case_id = pt_case.case_id JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id LEFT JOIN staff ON staff.staff_id = pt_case.sales_rep_staff_id   WHERE ar_detail.transaction_date BETWEEN @start_date AND @end_date   AND ar_detail.status IN ('BILLED', 'CREDIT', 'CREDITED', 'POSTED', 'ONCLAIM')  " &   iif(Parameters!pStaffCount.Value=Parameters!Staff.Count,"", " AND (staff.staff_id IN ('" & Join(Parameters!salereps.Value,"','") & "')) " &   "GROUP BY pt.patient_id, pt.last_name, pt.first_name,   ar_detail.ar_accounting_period_code,   pt_case.sales_rep_staff_id,   staff.last_name   ORDER BY pt_case.sales_rep_staff_id, pt.last_name, pt.first_name, ar_detail.ar_accounting_period_code" As per above, the filter on the staff_id is applied only when the selection on parameters is not all. Hence the NULL records are not returned when only a staff or a few staffs are selected from the parameter. Hope this helps. Please feel free to discuss if you have any questions. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi BH, Just a couple questions about this line from your suggestion. iif(Parameters!pStaffCount.Value=Parameters!Staff.Count,"", " AND (staff.staff_id IN ('" & Join(Parameters!salereps.Value,"','") & "')) " & SSRS is complaining about a missing ) in the query, I'm assuming it has to be from this line I am just not sure yet where it belongs, I'm getting lost in all the quote marks. Let me break it down and see where I'm getting lost. First off is the logical portion of the IIF(). You have: Parameters!pStaffCount.Value=Parameters!Staff.Count - There is no parameter staff, do you mean salereps.count? In the true section you have two double-quotes which I assume you mean to return an empty set. Then the rest is in the False section of the IIF() and thats where I get confused. You have " AND (staff.staff_id IN (' " & Join(Parameters!salereps.Value, " ', ' ") & " '))' " I count 5 single-quotes and 6 double-quotes. Shouldn't there be an even number of single-quotes too? I'm not sure what the difference is in the significance of the single vs. double-quotes. Thanks, Linn

I'm also confused by the Join(Parameters!salereps.Value... entry, what does that do? I haven't been able to find any reference to join used in this way when I Google it. If I take the entire IIF() structure out of the query so it isn't there at all, then the query fails. The message is: Query execution failed for data set. Incorrect syntax near the keyword 'AS'. If I take out the equals sign and double-quotes surrounding the query it works fine. Does this method of presenting the query to ssrs really work? I've never seen this used before. Thanks, Linn

Hi Linn, Long story short, implement this:- iif(Parameters!pStaffCount.Value=Parameters!Staff.Count,"", " AND (staff.staff_id IN ('" & Join(Parameters!salereps.Value,"','") & "')) " ) Thanks KumarKG, MCTS

Thanks KG, Now I get the error: "The commandText expression for the data set 'CHR_HCN' refers to a non-existing report parameter "Staff". If I change the Parameters!Staff.Count to Parameters.salereps.Count like I believe it should be then I get the error: "Incorrect syntax near keyword 'AS'. Any thoughts on that? Thanks, Linn

Hi Linn, Please paste your data-set queries? Thanks KumarKG, MCTS

Dataset CHR_HCN: ="SELECT pt_case.sales_rep_staff_id, staff.last_name, pt.patient_id, CAST(RTRIM(pt.last_name) + ', ' + RTRIM(pt.first_name) AS varchar(40)) AS 'Patient', ar_detail.ar_accounting_period_code, SUM(CASE ar_detail.source WHEN 'CLAIM_LINE' THEN ar_detail.amount ELSE 0 END) AS 'Claimed', SUM(CASE ar_detail.source WHEN 'PREADJUST' THEN ar_detail.amount ELSE 0 END) AS 'PreAdjust', SUM(CASE WHEN ar_detail.source = 'ADJUSTMENT' AND (ar_detail.ar_adjustment_code_code = 'BE' OR ar_detail.ar_adjustment_code_code LIKE 'CA%' OR ar_detail.ar_adjustment_code_code LIKE 'C/A%') OR ar_detail.ar_adjustment_code_code LIKE '[0-9]' OR ar_detail.ar_adjustment_code_code LIKE '[0-9][0-9]' THEN ar_detail.amount ELSE 0 END) AS 'Adjustment' FROM pt_case JOIN patient AS pt ON pt.patient_id = pt_case.patient_id JOIN claim ON claim.case_id = pt_case.case_id JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id LEFT JOIN staff ON staff.staff_id = pt_case.sales_rep_staff_id WHERE ar_detail.transaction_date BETWEEN @start_date AND @end_date AND ar_detail.status IN ('BILLED', 'CREDIT', 'CREDITED', 'POSTED', 'ONCLAIM') " & iif(Parameters!pStaffCount.Value=Parameters!salereps.Count,"", " AND (staff.staff_id IN ('" & Join(Parameters!salereps.Value,"','") & "')) " ) & " GROUP BY pt.patient_id, pt.last_name, pt.first_name, ar_detail.ar_accounting_period_code, pt_case.sales_rep_staff_id, staff.last_name ORDER BY pt_case.sales_rep_staff_id, pt.last_name, pt.first_name, ar_detail.ar_accounting_period_code" Dataset SalesReps: SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' Dataset datStaffCount: select count(*) as StaffCount FROM staff WHERE LEFT(staff.staff_id, 1) = '5'

Aplogies for the incorrect syntax in the iif expression with a mission paranthesis which Kumar has corrected perfectly. I guess you are entering the query expression directly in the query text box. Rather you should first click the expression button and go to the expression editor and then paste expression there. Here are the steps 1. Double click the dataset CHR_HCN. This will open the dataset dialog window. 2. Click the expression button located to the right of query text box. This will open expression editor 3. Paste the code in the expression editior. Click ok and close the expression editor. 4. When you returned to the dataset window the query expression should display <<expr>> in dimmed font Please observe this and reply. A good example to know about the Join function is given at http://lukehayler.com/2009/08/the-join-function-in-ssrs/ Please lets know how you progress. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Aplogies for the incorrect syntax in the iif expression with a mission paranthesis which Kumar has corrected perfectly. I guess you are entering the query expression directly in the query text box. Rather you should first click the expression button and go to the expression editor and then paste expression there. Here are the steps 1. Double click the dataset CHR_HCN. This will open the dataset dialog window. 2. Click the expression button located to the right of query text box. This will open expression editor 3. Paste the code in the expression editior. Click ok and close the expression editor. 4. When you returned to the dataset window the query expression should display <<expr>> in dimmed font Please observe this and reply. A good example to know about the Join function is given at http://lukehayler.com/2009/08/the-join-function-in-ssrs/ Please lets know how you progress. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Aplogies for the incorrect syntax in the iif expression with a missing paranthesis which Kumar has corrected perfectly. I guess you are entering the query expression directly in the query text box. Rather you should first click the expression button and go to the expression editor and then paste expression there. Here are the steps 1. Double click the dataset CHR_HCN. This will open the dataset dialog window. 2. Click the expression button located to the right of query text box. This will open expression editor 3. Paste the code in the expression editior. Click ok and close the expression editor. 4. When you returned to the dataset window the query expression should display <<expr>> in dimmed font Please observe this and reply. A good example to know about the Join function is given at http://lukehayler.com/2009/08/the-join-function-in-ssrs/ Please lets know how you progress. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Ok, that didn't work at all. The steps you specify in the instructions don't exactly work for me so I followed them as precisely as I could. I opened the expression window and pasted the code in it. I did not observe the <<expr>> in dimmed font as you suggested. It simply shows the code like I had it before. So I tried deleting the whole thing, opened the expression window and pasted it in and now I can't even get to the parameters when previewing. New error message says: An error occurred during local report processing. The definition of the report '/Sales Rep Revenue Detail Report' is invalid. The Value expression for the textbox 'txtClaimed_Total' refers to the field 'Claimed'. Report item expressions can only refer to fields within the current data set scope, if inside an aggregate, the specified data set scope. Are your instructions based on SSRS 2008? We are only on 2005 here. Thanks, Linn

Hi, my comments were based on SSRS 2008 or above. Sorry I am not having 2005 environment and as such unable to provide any help specific to 2005.

That's alright BH, that is what I was beginning to suspect. Thanks anyways. I'll continue to look for a solution. Thanks, Linn

Ha!... Figured it out! Found a discussion on another news group which was basically the same question. That along with the discussions here led me to my final solution. http://ask.sqlservercentral.com/questions/28659/ssrs-2005-multi-value-parameter-with-null-option (hope no one minds me posting the link) First I needed to inject a record to represent a NULL field value into the dataset that is the source for my multi-select parameter, like this: (Apparently, in SSRS you can't have a NULL value in a multi-select parameter so you have to trick it.) SELECT '-1' AS 'staff_id', 'No DM Assigned' AS 'last_name' UNION SELECT staff.staff_id, staff.last_name FROM staff WHERE LEFT(staff.staff_id, 1) = '5' ORDER BY 1 Then I needed to modify my WHERE clause in the main dataset to include this line: AND (pt_case.sales_rep_staff_id IN (@salereps) OR ((pt_case.sales_rep_staff_id IS NULL) AND ('-1' IN (@salereps))) ) Seems rather simple and some problem might rear its ugly head down the line but so far it is working with all my testing. The user can now select any or all the sales reps, including or excluding records that have no sales rep assigned, as they wish. Records with no sales rep have NULL fields in the database. I even have a summary report which can drill through to this detail report, just have to remember to pass '-1' from the summary report if I want NULL sales reps. Now all I need to do is publish it and let the users know where to find it. Yay! Thanks to everyone who offered assistance, Linn