Controlling/Rolling Forward Dynamic Multi-Tab Reports BPC 10.0 MS

[Former Member]

Hello,

We are currently live in BPC 10.0 MS, and I'm trying to find a way to control parameter selections of multi-tab Excel workbooks.  Our reporting is built with the business requirement of one department(entity dimension) on one single tab.  (50 departments = 50 tabs in one single workbook)  We have the account dimension in the row axis with time and category in the column axis.  My problem is that when I want to roll-forward from say Q1 to Q2, I have multiple maintenance points on each tab. (very manual process)  Does anyone know how to link parameter selections across tabs?  In my example we would have one "landing page" or tab that would allow you to make a global change selection for time and category that would flow across 50 tabs.  This would eliminate literally thousands of mouse clicks. 

I've seen this work in cell-based reporting.  It basically works like a simple native Excel link from one tab to the next. but I'm having trouble accomplishing this with the dynamic reporting of BPC 10.  In a perfect world we would simply share the column axis across tabs, but I don't think this functionality exists. 

Anyone experiencing this?  Any ideas?

Thanks,

Jason Cobb

BPC 10.0  8

EPM 12.2

[Former Member · Accepted solution]

Hi Jason,

If your sheets are dynamic, then you can have the selections in the first sheet. All the other sheets can use the EPMDimensionOverride function, and can fetch the member selected in the first sheet.

Hope this helps.

[Former Member · Accepted solution]

Hi Nilanjan,

Thanks for the response.  Where do I insert the EPMDimensionOverride function?  Is there anything special about the syntax?  I have inserted some screenshots below.  Image 1 shows what my first tab looks like and image 2 is the second.  As you know, I need the yellow highlighted dimensions (Time and Category) to feed from tab 1 into tab 2.  My cursor is in cell G13 on the tab 1 example and G15 in tab 2. (so you can see the Fx formula display)  I have Time selected for 2012 and 2013, while I also have three category selections: Actual, Budget Final, and Q2 Forecast. (with excluded members for the unwanted combinations)

Tab 1:

Tab 2:

Thanks again for any assistance you can provide,

Jason

[JohnL · Accepted solution]

Hi Josh,

Have a look at the EPM Add-In Academy for some ideas about dynamic reporting.

You appear to be on the right track.

Have a look at the EPMDimensionOverride syntax by downloading the EPM Add-In Help file.

Thanks,

John

http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy

[Former Member · Accepted solution]

Thanks John and Nilanjan!

Correct answer for my scenario:  When using EPMDimensionOverride or EPMAxisOverride to link to another tab with multiple members, you must include all dimension members in the linked cell.  (it appears you cannot link multiple cells from another tab)

Working formula inserted at the top of Sheet2:

=EPMDimensionOverride("000","Category",'Sheet1'!E3)

Contents of cell E3 on Tab 1 = Actual,Budget_Final,Q2_Forecast  (This will pull all three members because cell E3 includes all three members separated by comma's )

This formula will not work:

=EPMDimensionOverride("000","Category",'Sheet1'!E3,'Sheet1'!E4)  where E4 contains the second member, etc.

Thanks for the help guys! 

Jason

[arunkumar_s]

Hi Jason,

Did you try to keep the selection (for example time) only in the first tab and link it in all other sheets? I think it is possible to refer other sheets within the EPM formula.

Hope it helps.

Arun

[Former Member]

Hi Arun,

That's my goal.  I would like to find a way to link the tabs/category axis.  I've attached some screenshots in my response to Nilanjan below.  If you have any other thoughts I would certainly appreciate it. 

Thanks,

Jason