0
Former Member
May 31, 2016 at 10:43 AM

# Stacked report , cell content format

29 Views

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

Attachments

report.JPG (85.1 kB)