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

[lookingforK]

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:

Code Select Expand

[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.

[CognosPaul]

What is the parameter Store Number returning, the set ID or an individual Store Number? Does your store table contain the set ID? I'm assuming that the Store Number parameter is returning the store number, which is being used in another query, or in a case statement in the main query.

There are a few ways that I can think of to handle this. Let's start with Framework.

Alias your stores table, and join the new one onto the original on the Set ID. Normally this would create a Cartesian join, but in this case you will only be filtering a single row on the new table. If you anticipate the need to filter on multiple stores, make sure to use determinants on this table.

Fact <--Store Number--> Store <--Set ID--> StoreForFilter

StoreForFilter will always return a single row, which will filter Store by the specified set.

A Report Studio only solution:
Create a new query (Query 2) that returns Set ID. Filter that query by store number. In your report query, use the filter [Set ID] in [Query 2].[Set Id]. This will effectively create a subquery in the filter that returns the correct set to the filter.

[cognostechie]

Create a calculated column in your store table as:

If
([Stock].[Store Details].[Store Number] in ('001','002','003')) then ('A')
Else If ([Stock].[Store Details].[Store Number] in ('004','005','006')) then ('B')
Else ('C')

Name it 'Store Size'. You can do this either in Framework or Report Studio. Doing this in Framework is preferable.

In the report , use the Store Size in the parameter and the filter.

This will work if you have limited number of store sizes.

[lookingforK]

Thank you both.

I don't have the authorization in using Framework.

So I use the method cognostechie provided. It works...

Thank you!