Prompt question

[dandanyang]

Hello, I am new as RS writer. Hope my question is easy to answer. Thanks in advance!

I have a report which need to run for ' Retail/Services Cost Centers'  vs. 'Retail only cost centers'.
where Retail/Services Cost Centers include a, b, c,d, e,f,g
Retail Only Cost Centers doesn't include f,g, which include a,b,c,d,e
Here a,b,c,d....are the example data of Cost Center field ( not real data)

I tried two ways:
1. Create Query Calculation:
Case when ?CostCenterPrompt? = 'Retail/Services Cost Centers' then 'a', 'b', 'c','d','e','f','g'
when ?CostCenterPrompt? = 'Retail Only Cost Centers'
then  'a', 'b', 'c','d','e'
else 'Unknown'
end

Add Filter:
Cost Center in Query Calculation

However, the case statement is not working. Error comes from "  then 'a', 'b', 'c','d','e','f','g' ". I could only add 'a' after "then". How can I get the list of a,b,c,d,e,f,g by using this statement?

2. Then, I tried another way:

Create Query Calculation:
case when
[Business Layer Standard].[xxxx__c].[Cost_Center__c] in('a', 'b', 'c','d','e','f','g')
then 'Retail/Services Cost Centers'
when [Business Layer Standard].[xxxx__c].[Cost_Center__c] in ('a', 'b', 'c','d','e')
then 'Retail Only Cost Centers'
else 'Unknown'
end

Add Filter:
Query Calculation = ?CostCenterPrompt?

However, when I test it, it return blank value. The reason comes from both of them having 'a', 'b', 'c','d','e' value. If I remove either "when ...then...", or remove  'a', 'b', 'c','d','e' from first 'in',  it will return value. But I can't get what I want if do so. How could I fix this? Do I need change 'in' into another function to make it work?

[BigChris]

I may be misreading your requirements, but I think you probably want something like this as your filter:

(?CostCenterPrompt? = 'Retail/Service Cost Centers' and [Cost Center] in ('a', 'b', 'c', 'd', 'e', 'f', 'g')) or (?CostCenterPrompt? = 'Retail Only Cost Centers' and [Cost Center] in ('a', 'b', 'c', 'd', 'e'))

[MFGF]

I may be misreading your requirements, but I think you probably want something like this as your filter:

(?CostCenterPrompt? = 'Retail/Service Cost Centers' and [Cost Center] in ('a', 'b', 'c', 'd', 'e', 'f', 'g')) or (?CostCenterPrompt? = 'Retail Only Cost Centers' and [Cost Center] in ('a', 'b', 'c', 'd', 'e'))

Splendid answer! :)

MF.

[dandanyang]

Thanks BigChris for quick response. I added the filter and tested. Unfortunately, it returns blank value.

Here is what I did:
Create prompt with Static Choices: 'Retail/Services Cost Centers', 'Retail Only Cost Centers' . Parameter as 'CostCenterPrompt'
In testing query, dragged Cost Center field. Without the filter, this field will return 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j' and so on. Then I added the filter you provided. Tested it by view tabular data. In prompt, if I type in Retail Only Cost Centers, I am expecting the list of 'a', 'b', 'c', 'd', 'e', right? However, it return blank. Same when I type in the other prompt value.

Did I miss something? Thanks!