Topic
1 reply Latest Post - ‏2013-06-16T05:33:33Z by OpherB
lookingforK
lookingforK
4 Posts
ACCEPTED ANSWER

Pinned topic How to create a dynamic filter based on the selection value of a Data Item?

‏2013-06-15T04:47:31Z |

Hi,

I am using Report Studio 8.4 to generate reports.

The data source is a relational package.

With a prompt parameter (i.e. ?Store Number?), I want to create a dynamic filter for stores with the same store size as the selected ?Store Number?.

For example, there are 3 store sizes (i.e. A, B, C) for all 9 stores, and the relational source does not provide the info for the store sizes.
* Set 1: The store size for Store Number 001, 002, and 003 is A.
* Set 2: The store size for Store Number 004, 005, and 006 is B.
* Set 3: The store size for Store Number 007, 008, and 009 is C.

When the selection value of ?Store Number? is A, in ('001', '002', '003'), the report should only display the info for Size A stores, i.e. 001, 002, and 003.
When the selection value of ?Store Number? is B, in ('004', '005', '006'), the report should only display the info for Size B stores, i.e. 004, 005, and 006.
When the selection value of ?Store Number? is C, in ('007', '008', '009'), the report should only display the info for Size C stores, i.e. 007, 008, and 009.

I tried to use the following expression in the filter, but it can't work:
[Stock].[Store Details].[Store Number] in
(When
Case ?Store Number? in ('001','002', '003') then ('001', '002', '003')
When
Case ?Store Number? in ('001','002', '003') then ('004', '005', '006')
When
Case ?Store Number? in ('001','002', '003') then ('007', '008', '009')
Else 'Other'
End)


How to deal with this issue and make a dynamic filter?

Thank you in advance.

  • OpherB
    OpherB
    664 Posts
    ACCEPTED ANSWER

    Re: How to create a dynamic filter based on the selection value of a Data Item?

    ‏2013-06-16T05:33:33Z  in response to lookingforK

    This is a common mistake that people make when trying to create complex filters. Filter expressions must be logical values only, but you are trying to return a string.

    Think of this filter: [City] = 'Chicago'

    That's a logical expression.

    You have another issue going on in how you explained the situation. You wrote:

    When the selection value of ?Store Number? is A...

    ...but in the filter expression you wrote:

    Case ?Store Number? in ('001','002', '003')

    So I don't understand if you are prompting for a store number or a letter indicating the Set.

    In my solution below, I assume you are prompting for the letter indicating a Set. I think once you see the expression you'll understand how to edit it if necessary.

    ?Set ID? = 'A' and [Stock].[Store Details].[Store Number] in ('001', '002', '003')

    or ?Set ID? = 'B' and [Stock].[Store Details].[Store Number] in ('004', '005', '006')

    or ?Set ID? = 'C' and [Stock].[Store Details].[Store Number] in ('007', '008', '009')

    That should work for you.

    Good luck,
    Opher