displaying prior year

[pooja]

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

[navissar]

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.

[pooja]

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.

[kattaviz]

Hi,

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

[pooja]

Thanks.

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

[kattaviz]

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

[pooja]

I missed one more info - sorry

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

[kattaviz]

Hi,

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

[pooja]

This is what I did, and still no sucess

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

[navissar]

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.

[Satheesh]

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

[navissar]

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

[pooja]

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

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

Thanks again....