Stacked report , cell content format

[Former Member]

Version: EPM NW 10

Hi,

I am using two stacked reports in my worksheet as shown in attachment. Due to some intersection requirements, i have to use EPMSAVE for some cells.

In attached report format, cells I5:K6  will have EPMSAVE formulas referring to cells L5:N6  for report 1   and cells I8:K9  will have EPMSAVE formulas referring to cells L8:N9  for report 2

Of course these are dynamic reports so with zero suppression so rows will grow or shrink based on selections.

Now, cells in columns I,J,K have same formula except they have to refer to corresponding rows and columns with shift for some intersections.

Now i am using single dynamic format sheet for both reports

Cells in columns I:K are being populated based on  formatsheet cell content as below

'=EPMSaveData(L5,,$A$7,$A$8,$A$9,$A$26,$A$27,$A$28,$A$29,$A$30,AT$24,$B27,$C27,)

Ideally,  EPMFormat should take that formula for I5 and adjust formula in other cells accordingly with row and col shifts

This works fine for Report 1. But now the challenge comes for Report 2.

1) Cell formulas in RPT2 are starting with reference to L5 instead of L8

2) Assuming no other solution, i changed my formula to use volatile function to derive row using ROW() function combined with INDIRECT()

But EPM is not resolving the INDIRECT function at expansion time hence all cells are still referencing to column "L"

'=EPMSaveData(INDIRECT("L"&ROW()),,$A$7,$A$8,$A$9,$A$26,$A$27,$A$28,$A$29,$A$30,AT$24,$B27,$C27,)

in above formula INDIRECT("L"&ROW())  is not adjusted during expansion hence the all report cells refer to "L" column even though excel evaluates ROW() correctly at runtime.

While i am aware why epm couldn't evaluate an excel formula during expansion, i wonder how to handle it to refer to correct cell in this setup.

Appreciate any thoughts or alternate approaches.

-> need to use EPMSAVE

-> need to use stacked rpt

~Dilkins

[former_member186338]

"EPMSaveData" is a bad idea in general

And cell content override will insert formula as is without references adjustment.

Can you show the result of =INDIRECT("L"&ROW())

Vadim

[former_member186338]

Just for test, I have created formula with INDIRECT and it's working as expected:

'=EPMSaveData(INDIRECT("F"&ROW()),,$A$1,$A$2,$A$3,$A$4,$A$5,$B$7,INDIRECT("A"&ROW()))

Result:

On the formatting sheet the formula is entered in US standard with "," as argument delimiter

On the report it will be with local delimiter ";"

Vadim