BusinessObjects Board

About Yearly report

I have created a prompt in the universe like


@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…


sritri :us: (BOB member since 2004-12-11)

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 )

For more information on how to use the WHERE function, check out this Reporter FAQ entry.


Andreas :de: (BOB member since 2002-06-20)

Andrea,

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 ?? ) :oops:

I hope and pray that the results match.

Thanks


sritri :us: (BOB member since 2004-12-11)