How to share a multimember page axis

[Former Member]

Environment: EPM Addin 10 SP15  NW version

I have a workbook with multiple sheets with different row/column axis but Entity as page axis in all sheets. We want user to be able to select multiple entities in one sheet and apply the same selection for all other sheet reporting.

1) Any thoughts/idea/pointers would be appreciated.

2) Which event will be triggered when user double click and select a multiple entities on the page axis cell. I tried BEFORE_REFRESH, BEFORE_CONTEXTCHANGE but both didn't get triggered.

~Dilkins

[Shrikant_Jadhav · Accepted solution]

Hi Andrew,

You can try to giving reference to first sheet cell where entity is selected, go through the following link

Link shows the single member override but you try to modify

For  VBA approach , go through the following link, you will get some idea

Shrikant

[Former Member · Accepted solution]

Srikanth,

Thank you for helpful thought. I will try and see if it meets the need.

When double click on cell with multi member selection  which event will be triggered in VBA. Any idea?

~Dilkins

[Shrikant_Jadhav · Accepted solution]

Hi Andrew,

You can try something like Use BEFORE_REFRESH and change the Page axis of all templates.

Shrikant

[Former Member · Accepted solution]

Shrikanth,

Thank you. As I  mentioned above  Multi Member page axis selection  not triggering BEFORE_REFRESH. Does it trigger for you? If so, what is the configuration specific.

~Dilkins

[meenubansal87]

Hi Andrew,

As you mentioned that Entity dimension is in Page Axis then it will create an OLAP member formula and there is an option "Allow Multi-Selection" which allows you to select mutiple members.

Also to refer the selected multiple entities to other sheets you can refer that cell to rest all the sheets.

And for VBA worksheet_beforedoubleclick is there. Please refer the below link for that -

Excel VBA BeforeDoubleClick Event - Easy Excel Macros

Regards,

Meenu

[former_member186338]

Hi Meenu,

"Also to refer the selected multiple entities to other sheets you can refer that cell to rest all the sheets." - not possible , just test yourself!

"worksheet_beforedoubleclick" will happen before user will change the page axis member Also not an option - after user will select new members no event will happen.

Vadim

[Shrikant_Jadhav]

Hi Meenu,

Have you tested your solution ?

You can not refer that cell on other sheet as page axis .

Shrikant

[meenubansal87]

Hi Vadim/ Shrikant,

Yes, I have tested my given solution 1 for referring the cells to rest all sheets and its working perfectly for me.

And Yes Vadim, you are correct on point 2 - "worksheet_beforedoubleclick" will happen before user will change the page axis member. And as per my understanding Andrew is looking for this only.

Regards,

Meenu

[former_member186338]

"Yes, I have tested my given solution 1 for referring the cells to rest all sheets and its working" - but it will not work for page axis member cell. This cell has to contain:

=EPMOlap...

but not =Sheet2!E13

"And as per my understanding Andrew is looking for this only" - incorrect, he is looking for event that will be triggered before automatic refresh of the report as a result of page axis change, but after the user selected the new page members. Unfortunately no events available.

Vadim

P.S. Sample - report on Sheet2:

Instead of =EPMOlapMultiMember("BE1000,BE3000";"000";"[BE].[PARENTH1].[BE1000]";"[BE].[PARENTH1].[BE3000]") I put the reference to Sheet1 with the same formula:

Result: BE dimension immediately disappeared from page axis on Sheet2:

Message was edited by: Vadim Kalinin - P.S. added

[Former Member]

Meenu  thanks for your suggestion but its not meeting my requirement.

~Dilkins

[Former Member]

Vadim,

You are absolutely right. I am still trying to put together a working solution. Unable to understand standard solution gap for such common requirement.

~Dilkins

[former_member186338]

Hi Andrew,

1. It's not common - we use hierarchy parents!

2. "But it will not trigger if you double click the cell in the page axis - EPM bug " - please, open a support request to SAP - it's a real bug.

3. "You can create a special button to select multiple members with OpenMemberSelector and based on the result generate and insert page member formula." - it's working! Not perfect - OpenMemberSelector will show already selected members on the right with hierarchy collapsed on the left, but...

Vadim

[meenubansal87]

Hi Andrew,

It should meet atleast your referring cell value requirement. Please see my post which I have just created -

However regarding your refresh requirement, it cannot be possible as report automatically refreshes if OLAP member changes.

Regards,

Meenu

[former_member186338]

Hi Meenu,

See my comment on your document. There is no issues with single member link in page axis, the issue is multiple members!

Vadim

[Former Member]

Meenu,

I appreciate your enthusiasm to help. But please confirm your solution works for multi member selection. It works for single member selection and it is well known.

For a multi member selection, just referring to the previous sheet wouldn't do the trick. You may see the values from different sheet but it will not be applied to page axis. Try it.

You may want to correct your blog as it is misleading.

~Dilkins

[meenubansal87]

Hi Andrew/ Vadim,

I am going out of city and I will test my solution again once back so till then deleting the blog so that no one can mislead with my given solution.

Regards,

Meenu

[meenubansal87]

Hi Andrew/ Vadim,

I just checked the given solution today and it seems I was wrong. It is referring multiple members in 2 sheet which is selected in 1 sheet but it is not creating OLAP Multi-member formula or we can say not retrieving the correct value.

We can take the page axis values from 1 sheet and can override it in 2 sheet by taking that dimension in row or column axis but that doesn't fulfill these requirement.

Thank you for correcting me..

Regards,

Meenu

[former_member186338]

Hi Andrew,

Multimember format for page axis has very specific syntax - it's not possible to generate it using Excel formula. Try VBA, but in general it's a bad practice. Instead of multimember in page axis use parent in hierarchy - create additional hierarchies as required.

B.R. Vadim

[Former Member]

Vadim,

While principally I agree with you to create a parent node to meet the need, some requirements necessitate adhoc selections.

How do you recommend to trigger the VBA on multi member selection?

Regards

~Dilkins

[former_member186338]

Hi Andrew,

BEFORE_REFRESH is triggering if you change multi member selection for page axis using EPM bar - Page Axis. But it will not trigger if you double click the cell in the page axis - EPM bug

You can create a special button to select multiple members with OpenMemberSelector and based on the result generate and insert page member formula.

Vadim

[Former Member]

Vadim,

To satisfy my requirement i did few things and in general it is working fine but its finicky.

1) I created a selection sheet with a hidden dummy report and a cell with page axis for Entity exposed.

2) User can double click on the cell and make multi or single member selection - works great

3) When user click a "Generate Report" button - VBA is called

4) Some of my sheets need the multi selection as is in page axis but some sheets need multi selection for dimension override in row axis

-> In VBA I use Target_Cell.formula = source_cell.formula  to assign page axis to page axis different sheets

     Assignment works great most of the time. But when user does a single member selection the formula changes to "EPMOlapMemberO(" and some sheets randomly complains "Unable to determine, Using original report definition" error and refers the page axis with context member selection.

-> I am using "GetPageAxisMembers" to loop and convert to a comma separated list for using in dimension override formula with a cell reference

    Strangely when the concatenated string is "1111,2222" and it is assigned as a cell value in VBA, excel is automatically converting it into "11112222". It works fine when the converted string is like "BAS(PARENT),2222".

Any thoughts?

~Dilkins

[former_member186338]

1. "Assignment works great most of the time. But when user does a single member selection the formula changes to "EPMOlapMemberO(" and some sheets randomly complains "Unable to determine, Using original report definition" error and refers the page axis with context member selection." - have to be checked - wait a moment!

2. Strangely when the concatenated string is "1111,2222" and it is assigned as a cell value in VBA, excel is automatically converting it into "11112222". It works fine when the converted string is like "BAS(PARENT),2222" - nothing strange - comma is interpreted as thousand separator

Prefix value with '

Like:

'1111,2222

Vadim

[former_member186338]

About first question - tested! It's working correctly!

Look on the following code (assuming A1 is the cell with page axis for BE dimension in my sample):

strFormula = "=EPMOlapMultiMember(""BE1000,BE5000,BE3000,BE1300,BE1100"";""000"";""[BE].[PARENTH1].[BE1000]"";""[BE].[PARENTH1].[BE5000]"";""[BE].[PARENTH1].[BE3000]"";""[BE].[PARENTH1].[BE1300]"";""[BE].[PARENTH1].[BE1100]"")"

strFormula = "= EPMOlapMemberO(""[BE].[PARENTH1].[BE1000]"";"""";""BE1000"";"""";""000"")"

ThisWorkbook.Worksheets("Sheet1").Range("A1").FormulaLocal = strFormula

epm.RefreshActiveSheet

I can comment and uncomment the second line and the report will be fine (switching between single and multimember page axis).

May be immediate epm.RefreshActiveSheet is the key! Then before changing page axis in some sheet activate it with ThisWorkbook.Worksheets("Sheet1").Activate, change page axis cell, refresh sheet with epm.RefreshActiveSheet and then process other sheets.

Vadim