ALL parameter for SSRS Report

Question

I want to be able to let the business manually enter a USERID into a parameter box or leave it blank for it to bring back all USERID's.

I have setup another parameter for CUSTOMERID which uses a dataset to bring back a list of customer ID's. Due to the amount of USERID's I don't want to use the same solution but instead have them manually enter the USERID or leave it blank and bring back ALL.

SELECT 
    CUSTOMERID,
    CASE 
    WHEN STATUSCODE = 200 THEN 'Successful Logon' 
    ELSE 'Unsuccessful Logon' 
    END as LogonStatus,
    COUNT( * ) COUNTOFACCOUNTS

FROM 
    MA4EQNG.APPLICATIONLOG
WHERE 
    CUSTOMERID in ('"+join(Parameters!CustomerID.Value, "','")+"')
    AND (Cast(DATETIME as Date) >= '"& Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "' 
    AND Cast(DATETIME as Date) <= '" & Format(Parameters!ToDate.Value, "yyyy-MM-dd") & "') 
    AND COMPONENTDESCRIPTION = '/eq/auth/v1/logon'
    AND METHOD = 'POST' 

GROUP BY 
    CUSTOMERID,
    CASE 
        WHEN STATUSCODE = 200 THEN 'Successful Logon' 
        ELSE 'Unsuccessful Logon' 
    END

ORDER BY
CUSTOMERID ASC

Please let me know if you need anything else.

You can check if the parameter `IS NULL`? So something like `WHERE (USERID = @UserID OR @UserID IS NULL)` — NickyvV, Aug 16 '16 at 08:40
I tried the following and got an error. I entered the following line to the code and (USERID = Parameters!UserID.Value OR Parameters!UserID.Value IS NULL) I also enabled NULL values in my parameter in Visual. — JoelG, Aug 16 '16 at 09:03
Where do you want to use the parameter in the above query? I think you can add my code to your SQL. — NickyvV, Aug 16 '16 at 09:24
I want to add the condition into the WHERE clause like I have for the CUSTOMERID and DATE parameters that are already there. The only difference is I want the business to be able to leave NULL for all values or type in a USERID into the parameter. — JoelG, Aug 16 '16 at 09:43
Is the above query a dataset? Why aren't you using parameters in the SQL Query (like `@CustomerID` and `@UserID`) for example? What error message are you getting? — NickyvV, Aug 16 '16 at 09:51
Yes it is a dataset. Report is being written in Visual Studio's 2008. Error message is SQL0104: Token not found... But in past the error message has been un-useful. This is the error is I try and add it in the way you mentioned. AND USERID = UserID OR UserID IS NULL) — JoelG, Aug 16 '16 at 09:59

score 0 · Answer 1 · answered Aug 16 '16 at 09:17

0

You can set the USERID parameter to allow blank value then use IF condition on your main dataset sql script.

Try

IF LEN(@USERID)>0
*put the sql script with UserId filter in where clause*
ELSE
*put the sql script WITHOUT UserId filter in where clause*

answered Aug 16 '16 at 09:17

Aldrin

756
6
18

That didn't work. I tried adding it to my where clause as you described and get a error. – JoelG Aug 16 '16 at 09:40

score 0 · Answer 2 · edited May 23 '17 at 11:51

Normally you would just script something like this in the WHERE clause.

WHERE isnull(@CustomerID,'') = '' OR (CUSTOMERID in('"+join(Parameters!CustomerID.Value, "','")+"'))

And long term, I would create a function that takes in a delimited string, and converts them to rows, then you can just join on the results from the table valued function.

How to split a comma-separated value to columns

ALL parameter for SSRS Report

2 Answers2