@Select(Calculations\Effective Year) IN @Prompt('Enter Year as YYYY','N','Calculations\Effective Year',MONO,FREE) OR
@Select(Calculations\Effective Year) IN @Prompt('Enter Year as YYYY','N','Calculations\Effective Year',MONO,FREE) -1

Now when I use this universe in WEBI reporter, the sql comes out as

SELECT
SYSADM.PS_WFA_WKFRCE_FACT.ANNUAL_BCE_AMT,
SYSADM.PS_WFA_WKFRCE_FACT.DEPTID
FROM
SYSADM.PS_WFA_WKFRCE_FACT
WHERE
( ( TO_NUMBER(TO_CHAR(SYSADM.PS_WFA_WKFRCE_FACT.PER_DIM,'YYYY')) ) IN 2004 OR
( TO_NUMBER(TO_CHAR(SYSADM.PS_WFA_WKFRCE_FACT.PER_DIM,'YYYY')) - 1 ) IN 2004 -1
)

I have 3 columns in the report…The first is the business unit, next is profit for the prompted year and the last is profit-for-the-(prompted year-1) as below :

Business Unit BCE Amt for Current Year BCE Amt for Previous Year

How do I get the report to populate all these three columns ??
Thanks a bunch…

You have to bring back column “SYSADM.PS_WFA_WKFRCE_FACT.PER_DIM” as a result object. You can then create local report variables for the years 2004 and 2003 using the Business Objects reporter functions: UserResponse and WHERE

For example:

“Flag - Is Previous Year” defined as:

= <Year of Revenue> = ToNumber (UserResponse (...)) -1

You might have to use the Reporter functions ToNumber or FormatNumber to convert the data types properly.

and “Revenue Previous Year” defined as:

= <measure> WHERE ( <Flag - Is Previous Year> = 1 )

Thx for the tip. I brought in PER_DIM. However I could not figure out how to do the variable thingy on WEBI…So I did a crosstab report with Business Unit per Period and Aggregated figure. ( This gives away the secret that I am pretty dumb isnt it ?? )