How to show Total amount and distributed amounts in one report

[Former Member]

Hi,

I have the Plan Amounts distributed across Fiscal Year Periods for various WBS Elements in the cube.

I have to give a report wherein I should display the Total Plan amount for each of the WBS Element in one column, and the plan amounts across the fiscal year periods in the corresponding columns.

The user enters a Plan Start Date and a Plan End Date as input. The fiscal year periods to be displayed should be between this selection. For example if the user enters Plan Start Date as March07 and Plan End Date as Jun 07, the data should be displayed as follows:

WBS Element Total Plan Mar07 Apr07 May07 Jun07

WBS1 5000 3000 1000 0 1000

WBS2 8000 0 2000 4000 2000

If I take WBS Element in the row, Amount and Fiscal Year Period in the column, I am getting Plan Amounts distributed across the Fiscal Year Periods. I am not able to display the Total Plan amount.

If I take WBS Element in the row, and Amount in the column, I am getting Total Plan Amounts for each WBS but I am not getting amounts distributed across the Fiscal Year Periods.

I have tried by creating restricted key figures for all the fiscal year periods, but this doesn't work as the Fiscal Year Periods to be displayed are dynamic based on the user selection of Plan Start Date and Plan End Date .

Is there any way this can be handled by writing some ABAP code??

Please suggest on what can be done.

Regards,

Srini.

[Former Member · Accepted solution]

Hi Srini,

There are few ways this could be done...

First of all, you can give a BPS Total column which would total the plan values for you and display it on the layout. In the Data column setting of layout creation, create a row with heading, say TOTAL and check theTOTAL checkbox...you would find another cell enabled for entry wherein you could mention the "row" for which you want the total to be done ( in this case, the second "row" of data column modelling screen would be the dynamic allocation for bringing the periods you plan upon, for which just enter C(2) in the TOTAL row.

A particular problem here would be that you will be able to view the total only when you press the save for your plan values and not otherwise.

Another method would be create an excel formula for an excel column to total all the entries you make across the different periods. Here you must know how many columns exist prior or else you will not be sure how many columns you want to extrapolate the formula to.

To overcome this there can be 2 more possible methods:

One could be providing a button via BPS in the Folder wherein on clicking the button, a FOX function retrieves the planned data from the buffer in the particular keyfigure and calculates the total for you.

Another convenient method is by the use of macros: here again you can have 2 possibilities. You can provide a button within the Excel layout or you can create an Event via VB to recognize any changes in the cells the person plans upon and give you the result in the totals accordingly. This is similar to the excel formula you might use, but, here you can keep a check during runtime, on what all columns are actually user entry and hence have to be totalled from.

Take your pick...

Regards,

Aby