Skip to Content
0

BPC Report : Local Calculation display for each Row & Total row in the Report

Apr 24 at 07:42 AM

40

avatar image

Hello everyone,

I have created a report in BPC 10.1 which shows Monthly Budget & Actual Data in columns for 12 months & I have 4 different G/L Accounts in row axis. TOTAL (Row 3) in the Row Axis is the Local members created which shows the sum of all the 4 G/L Accounts. My requirement is to create a Full Year (Column P) & to apply different calculations for all 4 G/L Accounts separately which I have shown in screenshot 1.

In Full Year Column P, I want to perform different calculations for the different G/L Accounts & TOTAL mentioned in the screenshot attached. I have created one column in excel after disabling the Activate Local member recognition and applied the excel calculation in the Full Year (Column P) for all the G/L Accounts & TOTAL which shows correct output for individual G/L Accounts & TOTAL but the after I refresh the report, TOTAL (14,774) disappears for Full Year (Column P) from the report ( Screenshot 2).

Kindly suggest , How to keep the TOTAL calculation for Full Year ( Column P) highlighted in screenshot 2 ( RED Color) on refresh of the report.

screen-shot-2.jpg (68.5 kB)
screen-shot-1.jpg (69.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Vadim Kalinin Apr 24 at 07:49 AM
0

If you want an answer, please edit your question and instead of "Insert File" use correct "Insert Image". Currently your question is unreadable!

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim,

I have done the required changes and now you will be able to see the screen shots.

Thanks

Komik Shah

0
Vadim Kalinin Apr 25 at 07:18 AM
0

To my mind the logic of this report is strange and confusing! Different calculations in the same column will result in user questions!

Theoretically you can use local member in Full Year with conditional Excel formula like:

=IF(EPMDIM_CURRENT_MEMBER(ACCOUNT)="G/L1",...,IF(EPMDIM_CURRENT_MEMBER(ACCOUNT)="G/L2",...,IF....

But I think you have to discuss the report logic with the customer again!

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Apr 24 at 07:12 PM
0

Sorry, but not clear what do you want to have in Total column! Please explain requirements in details!

Also provide screenshot of your local member definition!

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim,

Total is the local member created in the BPC report. The definition is given below.

My requirement to have Full Year ( Column P) column to be created. You can find Full Year Column in Screenshot 1 of the trail message.

I would like to show different calculation for each G/L account in Full Year Column and the definition is shown in screenshot 1 (Column Q) which is why I can't create Local Member as Full Year. So, I have disabled Activate Local Member Recognition from sheet options and created Full year as local excel column. I was able to apply different calculation for each G/L account and it works fine but the Total (Row 3) should show sum of all 4 G/L accounts which shows correctly from Column C to Column O but doesn't work for Full Year column after I refresh report. Screenshot 1 shows the Total of 14,774 ( Marked in Red) Disappear from Screenshot 2 after I refresh the same report.

Please let me know if it is still not clear.

Regards,

Komik Shah

0

Sorry, but I am still unable to understand what do you want to have in Full Year Column!

"I would like to show different calculation for each G/L account" - not explained!

What do you have in Accounts? What do you mean by Actual and Budget?

What is the business value of the calculations?

...

0

Thanks for your quick response. I have explained below in detail.

In My BPC report, I have Actual and Budget Category which means If I am in January then from April till December it will show Actual Data and from January to March it will show Budget Data.

In Row axis, I have different GL Accounts like G/L 1, G/L 2, G/L 3 & G/L 4. So, from April 17 till March 18 data is straight forward coming from my Actual and Plan data entered in Input template for each G/L. Now, Business wanted to have two columns in the report. One is YTD Actual ( Column O - Screenshot 1) and one is Full year ( Column P - Screenshot 1) Data.

In YTD Actual (Column O), I had to show Actual data of the last month only. So, from above explanation if I am in December then YTD Actual ( Column O) should show data of January month which shows correctly in my BPC report.

in Full Year (Column P), I had to show different result for each G/L accounts and Total. For Example,

1. G/L 1 should show sum of Actual data only ( Which means it should sum data from April till December)

2. G/L 2 should show sum of Budget data only ( Which means it should sum data from January till March)

3. G/L 3 should show GL 1 - GL 2 ( Where GL 1 & GL2 values to be considered as calculated above).

4. G/L 4 should show GL 1 - GL 2 + GL3 ( Where GL 1, GL2 & GL 3 values to be considered as calculated above).

And Total Row should show sum of all 4 GL's. So, Full Year Column P where each cell should show different result for each G/L.


Regards,

Komik Shah

0

P.S. Never use Local member recognition, always create local members manually!

0