best practices on building epm report containing 2 reports

[Former Member]

hi bpc experts:

I m building a epm report which has to show data of 2 different models, the layout of the report is as follows:

As you can see the third table is just a excel table which has a formula to subtract amount or Report1 and Report2, but what happens is that Report1 and Report2 can grow in the number of rows (both show the same values in accounts), so my excel table will crash since it has harcodes excel formulas...is there a way to accomplish this in a practical way to avoid harcoded formulas on the "excel table" ?

regards

mah

[former_member186338 · Accepted solution]

Hi Mah,

You can use INDIRECT Excel function like:

=INDIRECT("C"&ROW()-20)

to get the value located 20 lines up related to the cell with this formula. Assuming you have same number of accounts in both reports you can calculate required shifts.

Vadim

[Former Member · Accepted solution]

Hi Arnold / Vadim:

If I have to keep on report above the other one, is there a way that the Report2 move dinamically down (when Report1 grows) in order to avoid a 'FPMXLClient.Common.CantWriteBecauseOfOverwrittingException' error ?

regards

mah

[former_member186338 · Accepted solution]

Never have this issue - I have 6 reports with shared column axis one after one and I can change number of rows in each without any issue!

Vadim

[Former Member · Accepted solution]

Hi Vadim:

I got your idea, so my report now looks like this: Report1(Model A) and Report2(Model B) are sharing the column axis...so far so good...

But the last table is a excel table to show the differences...but this table is affected when Report1 and Report2 grow vertically (more accounts in rows)...is there some way to make this table more dynamic?

regards

mah

[former_member186338 · Accepted solution]

Without VBA you can't have dynamic number of lines in check table. But you can have it long enough!

To address the report lines look on INDIRECT function with calculated offsets.

Vadim

[former_member186338 · Accepted solution]

Example:

For E21 the formula will be:

=INDIRECT("E"&8+ROW()-ROW($E$21))-INDIRECT("E"&((ROW($E$21)-11)/2)+8+ROW()-ROW($E$21))

8 - first row of first report

11=8+3, where 3 is the rows between end of second report and first comparison line.

You can copy this formula to E22, E23...

Vadim

[Former Member · Accepted solution]

Thanks Vadim for your time.

Your answer was very helpful.

Regards

mah

[Former Member]

Hi,

do the reports have to be in the above layout? If not you could try to have them side by side and therefore create the third report using localmembers.

BR,

Arnold