Filter expression to reproduce Crystal's Else True 1

[dianemarie]

Hello, I'm trying to filter my SSRS 2005 report by the clientID field, via my @ClientID parameter. I've got that working with a multi-value parameter. However, sometimes they want to see all clients. In Crystal, we would put a default of 0 in the parameter and in the select statement:

if {?ClientID} <> 0 then {ClientID} = {?ClientID} else True

This would allow the user to run all records or select specific client(s). (They would have to be sure to delete the default 0 if they ran by specific clients.) How do I replicate the "else true" part of this in the filter, allowing for the report to run all clients if desired. I tried defaulting the parameter to 0 and using an expression in the value field of the filter but it's not working with True. The field in the filter is ClientId and operator is In. Value field expression:

=if Parameters!ClientNumber.Value <> 0,Parameters!ClientNumber.Value,true)

I could put this in the where statement of the query as well, but again, I can't figure out how to get the Crystal "else true" equivalent.

Thank you in advance for any help.

 

[RiverGuy]

I'm not exactly sure what you are trying to do. The multi-value parameter should have a short cut to select all of the options, which would be the same as not having a filter at all. Am I missing something?

 

[dianemarie]

Well, we have thousands of client IDs. So for available values, I just leave it as Non-queried and the default value of null. Then they type in the (usually around 4 or 5) client IDs themselves in the blank drop-down box. Actually, I've been playing around with this so much I'm not sure I even got that to work. What I did get to work was putting this in the Where cause of the query, but I couldn't figure out how to make it default to All client IDs as an option. (I use a split function for adding the commas between and do not select multivalue for the parameter):

clientID IN (SELECT value FROM fn_Split(@clientID, ','))

I don't think this needs to be in the query, although I'm not averse to it being there, if I can just figure out how to return all clients as an option. This way, they can only select specific clients. Sorry if this is confusing. All I'm trying to do is allow them to select EITHER specific client(s) OR all clients from one parameter option.

 

[RiverGuy]

I usually do these types of things in the WHERE clause. If I'm doing a "choose one or all," in a drop down, I will union an "*ALL*" record into the datasource of the drop down, and modify the query like:

WHERE (@ClientID = 0) OR (ClientID = @ClientID).

But since you're allowing multiple values or all, it might be easier to just add a checkbox to "Show All Clients." This would pass a boolean value into your query parameters and you could do something like:

WHERE (@ShowAllClients = 1) OR (ClientID INT (SELECT value FROM fn_Split(@clientID, ',')))

This is assuming you're using SQL Server (by famous fn_Split function).

 

[dianemarie]

Yes, SQL. I wasn't aware the split function was famous, I inherited it. (I always think of it as the "effing" split function.) So anyway...I create a new parameter called ShowAllClients and make it Boolean? Then how do I set it up so it will return all clientIDs if 1? I'm a little lost. Thanks for your help.

 

[RiverGuy]

The last bit of code I wrote:

WHERE (@ShowAllClients = 1) OR (ClientID INT (SELECT value FROM fn_Split(@clientID, ',')))

This is referencing two variables you are bringing in. The @ShowAllClients is the boolean/bit parameter. When this query is run, if you pass in True/1, it's basically the same as putting no filter in. It's basically like saying:

WHERE 1 = 1. It will always evaluate to true, therefore not filtering any clients. So, in English, this would be like saying Return everything where 1 = 1 or the client is in the following list of clients...

HOWEVER, if you pass in False/0, it's as if you are saying: WHERE 0 = 1. This will always evaluate to false, so it's going to pick up the criteria in your OR statement in this case.

 

[dianemarie]

Hi Riverguy, still battling with this. I created a boolean parameter called "ShowAllClients". Available values are True/False with no other options (such as 1). Default is True. My ClientID parameter is an integer, multivalue is checked, with Available Values and Default Values non-queried and blank. I put exactly what you said in the Select statement, except I changed 1 to True:

((@ShowAllClients = True) OR (ClientID in (select value from fn_split(@ClientID,','))

My problem always comes down to this. I have to check Allow Nulls for the ClientID (if True on ShowAllClients) but a multivalue parameter can't be null. I tried playing around with a second dataset to use a 0 as a default (for ALL), but I just can't get this to work. I've been all over Google and most posts point toward your first suggestion of a Valid Values query to insert the "All" or in my case 0, to get this to work. Here's my union query and the query select statement, but when I default the ClientID to 0, it gives me an error in regards to my Start Date parameter, which works fine until I add the other stuff. I don't want multivalue parameter to return the long list of client id's so I'm trying to throw in the split function too. Maybe that's the problem?

Full where:

WHERE
calldatetime >= @StartDate and calldatetime <= @EndDate and
((@ClientID = 0) OR (clientID in (select value from fn_split(@clientID,','))

Second dataset to get default client id of 0 (aka All):
Select
ISNULL(noa.Account_Name, oa.Account_Name) AS ownAccount_Name,
r.ownerid
from recall r
left outer join accounts oa on oa.id = r.ownerid
left outer join amosdata.dbo.Nordic_account noa on noa.nm_account_id = oa.nm_account_id
union
select null, 0
order by 2,1

Thanks again. If this is getting too convoluted I certainly understand.

 

[RiverGuy]

I'm probably missing something, but here goes. If you keep your multi-value combo box, unbound to a datasource, it doesn't matter what someone types in the box if the "Show All Clients" box is checked. You can default it to 0. If they leave the 0 in there, or if they type some Client ID's, or if they type some invalid client ID's, the query is set up to still return all clients.

If the box is not check, that's when the query would actually apply the filter based on your combo box.

 

[dianemarie]

I appreciate you sticking it out. OK, I see exactly what you're saying but can't get my report to do it. I think my trouble might be in how I'm setting up the parameters. Select statement:

WHERE
calldatetime >= @StartDate and calldatetime <= @EndDate and
((@ShowAllClients = True) or clientid IN (SELECT clientid FROM fn_Split(@ClientID, ',')))

I don't know how you would get 1 for @ShowAllClients as it only offers me True or False as values. The two params are set up accordingly: @ShowAllClients = Boolean, nothing checked, available values = Non queried True and False, default = true. @ClientID = integer. I've tried selecting Multi-value, and unselecting multi-value, Allow nulls and not allow nulls, several combinations of those. I always end up with "must declare scalar variable" errors, either on the @StartDate param or the @ClientID param.

 

[RiverGuy]

In SQL Server, the boolean concept is represented by the BIT data type. BIT uses 0 for false and 1 for true. So if your report displays "true," SQL Server will see this as a 1 in your T-SQL code. So your SQL Server code should read: @ShowAllClients = 1...

 

[dianemarie]

Hi RG. I ended up getting this to work using your suggestion of a separate datasource with a UNION to bring in an ALL option (I like using '*' as opposed to 'ALL', as it always comes to the top, our users know * = All). I'd rather have done it in such a way that instead of a drop-down picklist, I could have used the split function and let the users type in the numbers they want separated by commas. I think I'm going to run into issues as our data grows but by then I hope to have it working. In the meantime I can move forward. Thanks for all your help.

 

[RiverGuy]

No problem. I know all too well that it can be hard at times to manage the functionality and usability versus the technology, and finding the best middle ground.