@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 ?? )