July 13, 2011 at 9:17 am
I have a database that represents, among other things, businesses that have many category descriptions. On the data user interface, they are represented by checkboxes (Y or N) for various business types - Clothing, Groceries, Commuications, etc.
For the SSRS report, I want to be able to SELECT based whether multiple of those checkboxes are 'Y' (along with other parameters). I've written SELECT's before for multiple values of a single field, but am having trouble figuring out how to do it for multiple 'Y's in multiple fields. In other words, show me all companies that are either Clothing or Groceries.
I've got the parameter built that will generate the values that I need (Clothing, Groceries), but can't figure out the SELECT.
Guidance??
July 13, 2011 at 9:49 am
Please show us the code you have so far. Also, a sample of the data would be helpful.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
July 15, 2011 at 2:03 am
Could you not rather do your categories as one parameter, with a drop-down list?
Then your query becomes a simple WHERE Category IN (@Category)
July 15, 2011 at 5:43 am
Unfortunately, no. I realize that that would be much easier, but I am working with an existing database, and that is the hand I was dealt.
Thanx
July 15, 2011 at 6:21 am
Usually, in such a case, you might be better served with a stored procedure, using dynamic SQL.
If you want to keep your query in the report, however, you can do something like this (note that this just one way to do it, and others may tell you there are better ways to do it - and they would probably be right):
WHERE Condition1 = @Parameter1
AND Condition 2 = @Parameter2
...
AND (Clothing = (CASE WHEN @Clothing IS NOT NULL
THEN @Clothing ELSE 'N' [*]
END)
OR (Groceries = (CASE WHEN @Groceries IS NOT NULL
THEN @Groceries ELSE 'N' [*]
END)
OR ...)
[* - or another value outside of the possible range of responses, e.g. if you only have 'Y' and 'N', use 'X' for ELSE value]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply