Only need choose dimension "Scope"

[Former Member]

Hi

As our consolidation report,  we need choose the dimension "Scope" and the corresponding dimension "Entity"  member.  Like below:

Scope:                                                                               ENTITY:

            World                                                                                                             World

Asia         Europe     Africa                                                                      Asia          Europe      Africa

The Scope and Entity have the same strucure,  if user want to see "Asia"  report,  it need choose Scope-Asia and Entity-Asia twice. In order to reduce user manual activity,  by using macro,  when user choose Scope-Asia,  Entity's member automatically be changed to "Asia".

Now, we found the solution make many troubles.

I think our problem is common, so do you have the best practice for it.  Thanks.

Regards,

[former_member225135 · Accepted solution]

Hi,

In simple way, you can create a property in Entity dimension for SCOPE and refer that property while creating report.

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul

Thank your input.

The Entity dimension is in page axis, not in the row axis or column axis. I do not think the Entity dimension is able to refer the scope by property.

Regards

[former_member225135 · Accepted solution]

Hi,

Can you share report layout and there can be one to many mapping (entity and scope)?

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul

Please see the below picture, thanks.

Regards

[former_member225135 · Accepted solution]

Hi,

I can't see neither entity nor scope dimension in report axis.

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul

yes, the two dimensions are not in the report axis,  they are controlled by context. I can only add them to the page axis, but how to do later?

Regards

[former_member225135 · Accepted solution]

Hi,

1. Create a property of scope in entity dimension.

2. keep scope dimension in context and entity in page axis

3. use epmdimensionpropertyvalue to call list of entity with selected scope

You will require a macro for this

4.

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul

Thank your input again.

Now we also use macro  [EPM.SetContextMember CONNE, "ENTITY", strMemID"'] to achieve the requirement.  But  we do not want to use macro for it.

I think the requirement is very common for consolidation project, why the SAP does not provide the standard solution?

Regards

[former_member186338 · Accepted solution]

VBA macro?? What for?

If member ID's in scope and entity are different - create property ENTITY in scope dimension and fill it with entity member ID

Put ENTITY dimension in page axis

ENTITY formula has to be:

= EPMOlapMemberO(EPMMemberProperty(;"[SCOPE].[PARENTH1].["&EPMContextMember(;"SCOPE")&"]";"ENTITY");"[ENTITY].[PARENTH1].[Asia]";"Asia";"";"000")

[former_member225135 · Accepted solution]

Hi,

If you have one to one relation between entity and scope, then you will not require the Macro, the solution suggested by vadim will work (assign the property directly to page axis ),  but if you have one to many relation (one scope is linked to multiple entities- which is common in consolidation scenraio), then you will require Macro.

Regards,

Rahul

[former_member225135 · Accepted solution]

Hi Vadim,

But what if there is one to many relationship (one scope is assigned to multiple entities)?

Regards,

Rahul

[former_member186338 · Accepted solution]

In the original message it was stated as one to one

With one to many multiple members has to be selected for page axis dimension - very bad report performance etc...

[former_member225135 · Accepted solution]

Agreed.

In the recent post, i have mentioned if there is one to one relationship, your solution holds good and if there is one to many relationship (which is common in consolidation scenario), use VBA if entity has to be used in page axis.

Regards,

Rahul

[Oleks_Master]

Hi DC Consultant,

Could you try to lock and hide Entity dimension as "World" member, then let users to use Scope only.

I believe there is no data on other intersections with Scope: Asia except Entity: Asia.

Best regards,

Oleksandr

[Former Member]

Hi Oleksandr

The dimension Entity is secured, we can not give every user the authority of "World" member.

Regards.

[Oleks_Master]

Hi,

I am not saying to give security access. Just try to ignore Entity dimension - let users to work with only Scope dimension.

Regarding security, they will see only allowed data.

Best regards,

Oleksandr

[Former Member]

Hi

As what I said before, I just tested in my system, it still not work.

Test process below:

1. I locked the dimension Entity as "World"

2. User  BPC01 has not the access authority for entity member "World"

3. BPC01 open the report, the dimension Entity is unlocked automatically and changed to one random member

4. So the report result is incorrect

Regards

[former_member225135]

Hi,

Is there is one to one relationship between scope and entity?

Regards,

Rahul

[former_member186338]

Sorry, but can you test my formula? It's working absolutely correctly...

Vadim

[Former Member]

Hi Rahul

The relationship almost is one to one.

[Former Member]

Hi Vadim

Thank your input,

I just tested, but found some problems.

In the English language environment, it work, but in the Chinease language environment, it not work, because the system says " it cannot recognize the Entity member XXXXX, so the entity dimension is losed from page axis.  I am digging out the root cause.

Regards

[former_member225135]

Then, the solution suggested by vadim will work.

Once again, highlighing the solution:

1. Create scope property in entity dimension.

2. Keep scope dimension in context.

3. refer the scope dimension member to get the entity details (epmmemberproperty) will work

4. refer the entity with entity dimension in page axis.

Regards,

Rahul

[former_member225135]

Can you share screen shot of the error message?

Regards,

Rahul

[former_member186338]

First in some cell (B2) create a formula: =EPMMemberProperty(;"[SCOPE].[PARENTH1].["&EPMContextMember(;"SCOPE")&"]";"ENTITY")

Then activate member recognition

In the cell where you have ENTITY dimension page axis formula - manually type "=B2"

Member recognition will create:

= EPMOlapMemberO(B2;"[ENTITY].[PARENTH1].[Asia]";"Asia";"";"000")

Disable member recognition.

Then change B2 to EPMMemberProperty(;"[SCOPE].[PARENTH1].["&EPMContextMember(;"SCOPE")&"]";"ENTITY")

Clear cell B2

Vadim

[Former Member]

Hi Rahul

Please see the below picture, in the end, the dimension entity is away from page axis when user open the report.

Regards

[former_member186338]

May be you have incorrect value in the property or make some errors in the formula!

Please use the steps below!

[former_member225135]

Do you have sufficient authorization?

Regards,

Rahul

[Former Member]

Hi Vadim

I can understand your all formula. But something seem incorrect.

The formula EPMContextMember("","Scope")      return value is       Scope:LC3009

It lead to next formual is incorrect, it says   [SCOPE].[PARENTH1].[SCOPE:LC3009] is invalid.

Regards

[former_member186338]

Ok, it's due to same members in SCOPE and ENTITY (it's a bad practice in general to have the same member id's in different dimensions)! Try:

=EPMMemberProperty(;EPMContextMember(;"SCOPE");"ENTITY")

Vadim

[Former Member]

Hi Vadim

Sorry, I change the formula, but it still not work.

When data language is English, it work; but it is Chinease, it not work.

Regards

[former_member186338]

Sorry, but how you want me to help you, without detailed screenshots on Chinease with translation and explanation?

[Former Member]

Hi Vadim

I appreciate your effort.

As language factors, let me firstly find the root cause on my own.  I also will post the latest progress here.  Thanks.

Regards

[Former Member]

Hi

Sorry, it is so long to update the status, I found the root cause is dimension Enity and Scope have the same ID, so now I change the Entity IDs. Thank your help.

Regards

[former_member186338]

Then close the discussion!