Value Prompt Filtering

[mike.coleman407]

Hi Masters,
I have a value prompt check box that is using a filter parameter like this:
[Project Summary Report Final Data Reporting].[Project Summary Report Final Data].[Rate Type (Actual/Target)] in (?prmpt_RateType?,'N')
Right now, the three RateTypes are being pulled into the value prompt - 'A', 'N', 'T'.
A = Actual
N = Expense
T = Target
Expense always needs to brought in, I'd like to be able to make this prompt only have choices 'A' and 'T'.
Thanks for any advice,
MC

[hespora]

In the query that feeds your prompt values, just filter on

[Project Summary Report Final Data Reporting].[Project Summary Report Final Data].[Rate Type (Actual/Target)] <> 'N'

(unless of course your prompt is populated by static choices, but then the solution is obvious ;))

[cognos-t]

Hi,

you can simply filter the unlying query of the promt in order to eliminate 'N' from the choices.

Within a relational model, the syntax should be like that:

[Project Summary Report Final Data Reporting].[Project Summary Report Final Data].[Rate Type (Actual/Target)] <>'N'

The prompt will only display 'A' and 'T'.

Best regards,

[cognos-t]

In the query that feeds your prompt values, just filter on

[Project Summary Report Final Data Reporting].[Project Summary Report Final Data].[Rate Type (Actual/Target)] <> 'N'

(unless of course your prompt is populated by static choices, but then the solution is obvious ;))

Hespora was faster ;) but the solution is the same  8)

[mike.coleman407]

Thanks guys. I made the change to the query item, and it gave me two options now, however, they options are now '1' and '0'. Any thoughts?

I appreciate all your support.

MC

[mike.coleman407]

Just figured it out! I applied the filter to an incorrect data item. I do have one more inquiry about this same prompt. Can I make the A = 'Actual' and T = 'Target'?

I would just like to make it a little pretty.

MC

[MFGF]

Just figured it out! I applied the filter to an incorrect data item. I do have one more inquiry about this same prompt. Can I make the A = 'Actual' and T = 'Target'?

I would just like to make it a little pretty.

MC

Sure! Your prompt values are retrieved using a query. Go to this query and add a query calculation using a case statement
CASE [your Rate Type item] 
WHEN 'A' THEN 'Actual'
WHEN 'T' then 'Target'
ELSE 'Other'
END

In the properties of your prompt, set this new calculated item to be the Display Value.

Cheers!

MF.