Post reply

Multi-field/Multi-value Selection in SSRS Report

  • July 13, 2011 at 9:17 am

    #253585

    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

    #1352264

    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

    #1353235

    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

    #1353334

    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

    #1353376

    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