Using a Case When to Calculate a Fiscal Year From Prompt Page

[Cognos_Jan2017]

Among other prompts, we have a Month prompt, and a Year prompt.

A data item is added with a Case When to calculate a Fiscal Year ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then ?p_TCalYr? - 1
End

The ?p_TMonth? uses the number of a Month but displays the Month Name.

There are NO dates in the Query object, instead Month Names, and Calendar/ Fiscal Years.

It fails when trying to subtract 1 from ?p_TCalYr?

Using _add_years requires making a timestamp, but we would rather just subtract 1
from the entered ?_p_TCalYr? prompt.

Gotta be simple?

TIA, Bob

[New_Guy]

Hi,
Did you try the below expression?

Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then cast(cast(?p_TCalYr?, int)-1),varchar(4))
End

Good luck
New guy

[Cognos_Jan2017]

Thank you New guy.

Just tried ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then cast(cast(?p_TCalYr?, int)-1),varchar(4))
End

It gave a parsing error near position 125 which begins at ...
varchar(4))

[New_Guy]

Hi,
Try this

cast(((cast(?p_TCalYr?, int))-1),varchar(4))

Good luck
New guy

[Cognos_Jan2017]

THat DOES calculate the correct year, bur for some reason calling that DEF, and ...
_make_timestamp([DEF],10,1) errors as ...
'SQLPrepareWithOptions' status='-56'

I tried, again ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then ?p_TCalYr? - 1
End
... calling that ABC, and doing
_make_timestamp([ABC],10,1) ... works fine.

I will continue to use the Cast info and determine why it errored as '-56'

THANK you for the Cast info, Bob