Report Calculation

[Former Member]

Hello Gurus,

I generated a report on cube will have following infoobjects

1.Fiscal Year,Fiscal Year period are Infoobjects

2.Monthly Revenue, Monthly Booking are Keyfigures

My report <b>variables</b> are Fiscal Year and Fiscal year Period

For Example User will pass Variable values Fiscal year = 2006 and Fiscal Year Period = 10

User Expecting Following report output

<b>Report Output</b>

Monthly Revenue

Last Year Monthly Revenue(In this case 2005 year)

Year to date Revenue(Cube having 2003,2004,2005,2006 total years Revenue)

Monthly Booking

Last Year Monthly Booking(In this case 2005 year)

Year to date Booking(Cube having 2003,2004,2005,2006 total years Booking)

How to calculate following values

1.Last Year Monthly Revenue

2.Year to date Revenue

3.Last Year Monthly Booking

4.Year to date Booking

Gurus your input would be appreciated

[Former Member]

Fiscal Year Period = 10 ??? Is it posting period = 10 FYP is 200710 not just 10.

Do you have posting period available - 0FISCPER3 ??

Monthly Revenue - Should this display only for user entered year for month 10 ?

Last Year Monthly Revenue(In this case 2005 year) - 2005 & for month 10 ?

Year to date Revenue - Is it not YTD - Summed up Revenue for year 2007 - From month 1 to month 10 ?

Message was edited by:

Jr Roberto

[Former Member]

Fiscal Year(IO) = 2006(User Enter)

Fiscal Year(IO) = 10(User Enter)

Monthly Revenue = Should display Monthly Revenue of 2006,Month(10)

Last Year Monthly Revenue(In this case 2005 year)= Should show 2005, upto month(10)

Year to date Revenue-Should show summed up from 2003,2004,2005,2006 upto month 10.

[Former Member]

Hi Roy

This is how do the the calculations using the BEX query designer.

How to calculate following values

1.Last Year Monthly Revenue

Create a restricted key figure, include Monthly Revenue, fiscal year and period, set the fiscal year to your variable and then in the next selection screen right click on your variable and select Variable Offset adn set it to -1. set the period to your period variable.

2.Year to date Revenue

Create your restricted key figure, make sure you include fiscal year and period, set fiscal year to your year selection variable and set a value range on fiscal period for between period 1 and your period selection variable.

3.Last Year Monthly Booking

repeat the step 1 but substitute monthly booking for revenue

4.Year to date Booking

repeat the step 1 but substitute monthly booking for revenue

[Former Member]

1.Last Year Monthly Revenue

Create a restricted key figure, include Monthly Revenue, fiscal year and period, set the fiscal year to your variable and then in the next selection screen right click on your variable and select Variable Offset adn set it to -1. set the period to your period variable.

I created Restricted Key figure as "<b>Last Year Monthly Revenue</b>" Under this I include the Fiscal year,Period,Monthly Revenue after this step i was confuse how to do the "fiscal year to your variable and then in the next selection screen right click on your variable and select Variable Offset adn set it to -1. set the period to your period variable. Could you please provide me more details

2.Year to date Revenue

Create your restricted key figure, make sure you include fiscal year and period, set fiscal year to your year selection variable and set a value range on fiscal period for between period 1 and your period selection variable.

How to do "set fiscal year to your year selection variable and set a value range on fiscal period for between period 1 and your period selection variable".

Could you please provide me step by step instruction I would appreciare

[Former Member]

1.Last Year Monthly Revenue

Open the RKF, double click the fiscal year, on the next screen click on the drop down beside show and select variables, here you can either select a content variable (assuming you activated some) or create one or one youve already created, the content variable 0P_FYEAR would do the trick. Once back in the screen Select Values for Fiscal Year, right click on the variable you selected and chose Set offset, set it to -1. This means when you are prompted for the year variable when you run the report the Last Year Monthly Revenue RKF will be a year earlier. Do the same for the period selecting the variable, 0P_PER3 is a content variable that would do.

This RKF will now represent the prior year monthly revenue for what ever year and period input you use.

2.Year to date Revenue

Open the RKF, double click the fiscal year, on the next screen click on the drop down beside show and select variables, here you can either select a content variable (assuming you activated some) or create one or one youve already created, the content variable 0P_FYEAR would do the trick. Double click the period and on the Select Value for Period screen click the drop down and select Value Range, chose Between and select 1 and the period variable you used/created for the Last Year Monthly Revenue RKF, click the blue arrow pointing to the right and click ok.

this RKF will give you the revenue for the year you select from period 1 to the period you select.

You can repeat this for the other 2 RKF.

[Former Member]

After Seting "set it to -1" the system is giving following error message

Error Message: Run-time error '-2147417848(80010108)

Automation error (The object invoked has disconnected from its clients"

I am using Query Designer 3.5 and BI 7.O version

I am getting above error message when i try to restrict

is there any alternative way to set it to -1?

[Former Member]

I resolve the error message but still I am not getting

Last Year Month Revenue Actual(I set the Fiscal Year Variable -1 and Period as 0)

YTD Revenue actuals(I set the Fiscal Year variable 0 and Period as (between 1 and period variable)

Last Yr YTD Revenue Actual(I set the Fiscal Year Variable offset -1 and period variable between 1 and period variable)

Note: I am creating query on Multiprovider

My query Variable as follows

1. Fiscal Year (With Variable)

2. Period No(With Variable)

[Former Member]

Last Year Month Revenue Actual(I set the Fiscal Year Variable -1 and Period as 0)

- the fiscal year var -1 should be the offset and period should be the input variable, why have you restricted to period 0?

YTD Revenue actuals(I set the Fiscal Year variable 0 and Period as (between 1 and period variable)

- fiscal year should be the same variable used in the last year month rev actual RKF but without the offset,

Last Yr YTD Revenue Actual

-this look fine

what is the problem you are having?

[Former Member]

The values are coming correctly when i do this below.

Last Year Month Revenue Actual(I did Fiscal year(IO) selected "constant selection" and Period also checked "constant selection")

YTD Revenue actuals(Same thing with this also)

Last Yr YTD Revenue Actual(Same thisnk withthis one also)

I don't know what is the functionality of the "CONSTANT SELECTION"

[Former Member]

If i select "Constant Selection" when i drilldown report the amounts are coming wrong.

Let me Explain More detail Restricted Keyfigures

1.Last Yr Monthly Booking Actual Under this RKF I included

Fiscal Year(IO) Variable(VUFSYR01) offset "-1"

Period Number(IO) Variable(VUFSPR01) no offset value

2.Last Yr YTD Revenue Actual Under this RKF I included

Fiscal Year(IO) Variable(VUFSYR01) offset "-1"

Period Number(IO) Variable(VUFSPR01) between 1 to VUFSPR01

3.YTD Booking Actual Under this RKF I included

Fiscal Year(IO) Variable(VUFSYR01) no offset

Period Number(IO) Variable(VUFSPR01) between 1 to VUFSPR01

I am not getting correct amount for these RKF

I am thinking same Variable(VUFSYR01) I am using in all RKF some time I am deleting offset -1 inthe next RKF also I am using the same Variable I am thinking I am getting Variable value as 2006-1 = 2005 that is wrong I need 2006

How to make Different variable names and pass the user input value?

[Former Member]

The offset on the variable will only effect the rkf in which the restriction is made, so you can have several rkf all using the same var with different offsets with no problems.

Your current restrictions look fine, but you could be missing some, it could be useful to reconcile the data in the cube to the value your expecting that way you can see all the restrictions on the data.In BI transaction RSA1 display data on the infoprovider you have written the query on, try to reconcile one row against one column, so try to find the Last Yr Monthly Booking Actual against a specific cost centre or for whatever characteristic you have in the rows. the data may have some additional version, value type, currency type, fiscal variant, etc restrictions that you have no accounted for.