Invalid Variable After Being Validated (Cognos BI)

Question

I was asked to put a special case when sales region contains the string 'Europe', my chart should start from March until the later months instead. This is the expression filter I used

Case When ParamDisplayValue('p_salesregion') contains('Europe')
Then [Revenue By Region].[Month (numeric)]>=3
Else [Revenue By Region].[Month (numeric)] 
End 

When I press on validate is shows no error but when I run the report It gives me this error: 'An error occurred at or near the position '69'. The variable named '[Revenue By Region].[Month (numeric)]' is invalid.'

Answer 1

Assuming you'll probably want to do this in a detail filter, which will filter the chart accordingly.

You could try something like

[Revenue By Region].[Month (numeric)] >= if (?p_salesregion?) contains('Europe') then (3) else (0)

Answer 2

It's odd that your would want to omit one sixth of the sales data from your Europe offices. Are you sure you have the requirement correct? Maybe the fiscal year starts in March, so you want to show March - February for Europe and January - December for all others?

ParamDisplayValue() is a report function, not a query function. I'm surprised you don't get RSV-VAL-0010 Failed to load the report specification. XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: 'ParamDisplayValue' is not a recognized built-in function name.

And you can't perform a comparison in the THEN part of a CASE statement.

Use one of these, depending upon how much flexibility you need.

[Revenue By Region].[Month (numeric)] >= 
Case
    When ?p_salesregion? contains('Europe')
        Then 3
    Else 0
End

[Revenue By Region].[Month (numeric)] >=
Case
    When #prompt('p_salesregion')# contains('Europe')
        Then 3
    Else 0
End