EPM - Sharing Dimensions over Multiple Sheets

[Former Member]

Hello Experts,

I have a question about building reports in Excel with the EPM Add-in.  Is it possible to Share Axis' over multiple sheets?  I have done it on one sheet before.  My example is we have a workbook with multiple Reports for different entities on their own tab(sheet) within the workbook.  The reports are all the same structure.  Once a month, an update is needed to be made to the TIME Dimension.  Currently, we have to update each individual report.  I was hoping there is a way to link them or share an axis so one update could update multiple reports in the same workbook.  Any ideas would be great.

Thanks,

Sean

[former_member186338]

Hi Sean,

You can use Excel link inside axis member formula. Link it to the same sheet.

B.R. Vadim

[Former Member]

Thank Vadim,

Would you be able to provide any more details or an example?

Thanks,

Sean

[former_member186338]

Hi Sean,

My solution is applicable if you have "static" TIME axis. "Static" means that number of members in this axis is constant. Simple example - you have only one column in your report with the time member on each sheet. Then:

1. Put somewhere this time member (not on the axis!). Instead of direct typing the time member you can use =EPMSelectMember(...) function. Lets assume this member is on Sheet1 in the cell D1

2. Enable automatic member recognition.

3. On the time axis replace (by typing) the existing EPMOlapMemberO formula with the link =Sheet1!$D$1 on all sheets.

4. Member recognition will automatically convert this link to the correct EPMOlapMemberO formula with the link inside.

5. Now, if you change member in Sheet1!$D$1 and Refresh - all sheets will have proper time axis.

6. This can be done for more then 1 TIME member - use Excel formulas to generate offset etc.

B.R. Vadim

[Former Member]

Thank you Vadim,

This is getting me closer.  Would it work with Nested dimensions above TIME also?  They are static, but I am having touble getting them to work. It keeps attempting them to move into the Page Axis. Below is a screen shot of what I want the header to look like.  Previously I had Excluded many members due to the multiple nested dimensions.

The user wants to be able to change the Times, specifically the Budget month and apply it to all sheets, but I need the other Nested Dimensions like Categorty and AuditTrail locked down.

Thank you for the help.

[former_member186338]

Hi Sean,

You are talking about asymmetric column axis with 4 dimensions involved. The same approach (with Excel link to cell) is applicable to this scenario. I have the same report but with 3 dimensions (TIME, CATEGORY and MEASURE) and it's Ok  with all members defined as links to some cells!  Hint: Turn on Repeat Column Headers!

B.R. Vadim