Print Page - displaying prior year

Title: displaying prior year
Post by: pooja on 14 May 2014 12:45:21 PM

Hi All,

I am using substring function to display two digit year on the report and working fine. Below is the function-

substring(ParamDisplayValue('Select Period'),6,7) = this gives me current year, if input = 2012 then it shows 12, if input = 2011 then it shows 11....

Now how to get prior year in this case-?

like if input = 2012 then it should 11, if input = 2011 then it should shows 10

Thank you
P

Title: Re: displaying prior year
Post by: navissar on 14 May 2014 02:12:04 PM

That is not nearly enough data to try to help. Are you on relational or dimensional? Is the prompt a text box prompt or a select prompt, and if the latter where does the data come from? Where is this item to be used? Please try to provide as much detail as possible.

Title: Re: displaying prior year
Post by: pooja on 14 May 2014 02:17:22 PM

Sorry ...I missed a lot of information.

1)We are working against dimensional model(cube based).
2) Yes, ParamDisplayValue('Select Period') coming from prompt selection and we are using it as report expression.
3) This has been using on columns header to display 2 digit prior year against any period prompt selection.

Hope this help.

Title: Re: displaying prior year
Post by: kattaviz on 14 May 2014 02:53:10 PM

Hi,

Best way is calculate the Prior Year based on the Prompt value and display the last 2 digits of Prior Year

Title: Re: displaying prior year
Post by: pooja on 14 May 2014 02:59:47 PM

Thanks.

Can you please advise how to do that, based on my expression?

Title: Re: displaying prior year
Post by: kattaviz on 14 May 2014 03:04:21 PM

Hi,

Example;

If the user selects 2012 as the Current Year, build a calculation "_add_years(ParamValue('parametername'),-1)"

This will give you 2011. Similarly for other time periods use the corresponding function to get the Prior time periods.

HTH

Title: Re: displaying prior year
Post by: pooja on 14 May 2014 03:18:08 PM

I missed one more info - sorry

we have period value coming as MM YYYY format. ie 01 2012, 02 2012.....

Title: Re: displaying prior year
Post by: kattaviz on 14 May 2014 03:50:19 PM

Hi,

Even better. get the last 4 digits of the input value and subtract 1 from it (2012-1=2011)

Title: Re: displaying prior year
Post by: pooja on 14 May 2014 05:11:53 PM

This is what I did, and still no sucess

substring((ParamDisplayValue('Select Period')4,7),-1)

Title: Re: displaying prior year
Post by: navissar on 15 May 2014 01:42:24 AM

If all you need is the report expression for the title, try the following logic:
substring(ParamDisplayValue('Select Period'),6,7) gives you the selected year.

string2int32(substring(ParamDisplayValue('Select Period'),6,7)) will turn it into a number

string2int32(substring(ParamDisplayValue('Select Period'),6,7)) -1 will subtract 1.
That should do it...

If you're looking for the logic of actually doing the filtering, that's a whole different thing.

Title: Re: displaying prior year
Post by: Satheesh on 15 May 2014 05:01:54 AM

Hi,

Do like below,

cast( substring(ParamDisplayValue('Select Period'),6,7) , number) -1

Substring will provide you the result in string that you need to cast in to number and do minus one.

it will work for sure

Thanks & Regards
Sateesh

Title: Re: displaying prior year
Post by: navissar on 15 May 2014 05:08:40 AM

1. NO CASTING IN DIMENSIONAL!
2. No casting in report expressions.

Title: Re: displaying prior year
Post by: pooja on 15 May 2014 04:44:44 PM

Thank you so much. It is working now. This is what I did-

number2string (string2int32(substring (ParamDisplayValue ('Select Period'),6,7))-1)

Thanks again....

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: pooja on 14 May 2014 12:45:21 PM