Half Year Filter

[vani65]

Hi All,
I’m new to Cognos report studio, I created a simple cross tab report with Months as columns and sales as rows. Basically it is budget report. Months showing as ‘Jul-08,Aug-08, Sep-08, Oct-08, Nov-08, Dec-08, Jan-09, Feb-09, Mar-09, Apr-09, May-09, Jun-09,
The requirement is to show only First Half Year budget and Second Half Year budget as column names
That means First Half Year should cover ‘Jul-08,Aug-08, Sep-08, Oct-08, Nov-08, Dec-08,

And Second Half Year should cover Jan-09, Feb-09, Mar-09, Apr-09, May-09, Jun-09,

I tried to create a filter using case statement could not worked out – could any one there to help me out.

 

[blom0344]

Can you post the case statement you are trying to get to work?

Ties Blom

 

[almeids]

Was your original, simple crosstab working the way you wanted? The reason I ask is that if it was, there's no reason to change your filter, what you need to change is your columns dimension data item. Create a calculated data item like:

if ([month]>6) then ('First Half Year') else ('Second Half Year')

and use that for your columns. If you don't have a month column you may need to use date functions on your date column to extract it. If you don't have a date column you'll need a more complex condition:

case substring([month],1,3)
when 'Jan' then 'Second Half Year'
when 'Feb' then 'Second Half Year'
...
else 'First Half Year'
end

...or something like that.

 

[vani65]

Hi almeids / Ties blom
Thanks for your response, still the case statement not working in “Filter” - complaining the following Error. but the same statement works on SQL editor, don’t find the reason. .
Could you please explain what the meaning of this error.

CASE substring([Month],1,3)
When 'Jan' Then 'Second Half Year'
When 'Feb' Then 'Second Half Year'
When 'Mar' Then 'Second Half Year'
When 'Apr' Then 'Second Half Year'
When 'May' Then 'Second Half Year'
When 'Jun' Then 'Second Half Year'
else 'First Half Year'
End

Error:
QE-DEF-0459 CCLException
QU-DEF-0478 Invalid coercion from 'level' to 'string' for 'CASE substring(([Month],1,3)When 'Jan' Then 'Second Half Year' ........................

RVS-VAL-0044 The mrmber caption can only be used in the scope of crosstab or chart

 

[blom0344]

If you are using a dimensional datasource ,then the following might work:

substring(caption([Month]),1,3)

Ties Blom

 

[almeids]

Syntax aside, I'm still not sure why you are doing this in a filter - the CASE expression you listed is not a filter, it's a calculation, and should be in a data item. Your filter should be selecting a time period (halves of 2 calendar years since it sounds like your time dimension isn't based on your fiscal calendar). If you are using a dimensional data source as it sounds from the error message, and the time dimension includes quarters, you can simplify your calculation by doing it at the quarter level. Can't give you specific syntax/suggestions since I'm not on C8.