How To Achieve Total Of All Columns of Dynamic Report

[former_member394086]

Hi Experts,

1. I have one Dynamic report develop for which Profit Center in Row Axis and time + Category in Column Axis.

2. There are total 17 Columns i.e. Jan to Dec and Q1 to Q4 and Total.

3. All the main report section we have one formula set for each cell using EPM Formatting sheet since this report is dynamic. This calculation formula we have wrote(hard coded) in formatting sheet using "Time.Calc Member Flag =N" and set "Content" for cell format.

4. Now I wants to take a Total of all these columns and to achieve this I did following but it didn't work out.

- Created Local Member After Row Axis and set formula =SUM(ALLEPMMEMBERS). But after executing report it is picking up prior cell formula only and not showing Total of all cells. In short Sum(Allepmmembers) is not working here.

- Try to Create "SUB TOTAL" using edit report option but SUB TOTAL Local member is not getting created.

- Can someone suggest me how to achieve this.

- I am also thinking to go for macro code for this total if it will not work out using EPM function. So can some one please provide me example vba code which will give me correct total after refresh.

5. Since its a dynamic report hence number of Rows not fixed. Hence looking for total for this dynamically. Please advice.

Parag

[former_member394086]

HI Vadim,

Getting an issue with uploading screenshot. But will again try to explain. Below formula is just to show an example. Have use different calculation in report. Now This formula will give me some value. Have fix this formula in formatting sheet. and it is then automatically taking further cell references.

But issue start when I am trying to achieve total for column 2018.01 ,02 , 03 and so on. Created "TOTAL" as a Local member and assign formula SUM(ALLEPMMEMBERS) but still it is showing previous cell formula i.e. =F4*F5/A$1. So in short it is coping previous row formula but not showing total. Tried sub total option also but it is not creating local member.

Profit Center 2018.01 2018.02

PC_001 =F3*F4/A$1 =G3*G4/B$1

PC_002 =F4*F5/A$1 =G4*G5/B$1

TOTAL

[former_member186338]

Sorry, but without screenshot I am unable to help you! What is the issue??

And in general - formulas in Content of Formatting sheet is a very bad practice.

[former_member186338]

Sorry, but not clear ! Please provide report screenshot and detailed explanation.