'Renaming' Dimension Values in reports

[cogprog]

Hi,
I am having a perticular requirement in my report which needs the value of the dimension to be renamed depending upon one of its value. In my reports I always have to fetch data from 2 adjescent years. A prompt gives choice to user to select a year. Selected year(e.g.2000) as well as prior year's(2001) data has to be displayed. Now in reports I don't have to show values in year boxes to be 2000,2001 rather I need them to display "Current year" and "Prior Year".
e.g.

A normal BO report shows :
2000 2001
jan feb mar apr ... jan feb mar apr
EstCost 200 270 800 67 137 290 302 118


But requirement is :
Current Year Prior Year
jan feb mar apr ... jan feb mar apr
EstCost 200 270 800 67 137 290 302 118

The report is a drillable report. Need help to achieve this task.

Cogprog.

 

[rbuttens]

Cogprog,

Need to use the following if statement which you can then define as a variable and use in place of the old one.

if <year> = '2001' then &quot;Prior Year&quot; else &quot;Current Year&quot;

rbuttens

 

[cogprog]

Thanks for ur response . But situation is not that easy. As at the report time we are choosing the year so the value of current or prior year is never fixed that is why I can't apply the formula u suggested.

cogprog

 

[blom0344]

Hello cogprog,

The real solution to your problem should be made outside the report-realm, if you really want a good fix. The way to go is to create a small additional table in the database that contains a column with years and one column with the descriptions like 'current year', 'prior year' etc. Link this table to the facttable(s) you are using and use the description object from the additional table both as object retrieved and in the prompting.
Keep the additional table-contents up-to-date throughout the years and every report could benefit from it. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[rbuttens]

Cogprog,
Further to my suggestion you could create a Max & Min Year object at design level. When you run query bring in the year object and also the max & min objects. Formula could then be:-

if <year> = <Max Year> then &quot;Prior Year&quot; else &quot;Current Year&quot;

This will work as long as you are querying just the 2 years.

rbuttens