How do I translate multiple Dynamic Filters into EPMAxisOverride formula?

[Former Member]

I'm trying to replicate an entity selection I have made in the member selector screen with multiple filters into a EPMAxisOverride formula but I have not been successful. The output of the original selection (see attached file) maintains the hierarchy order while testing for each of the filters. When trying to recreate the EPM formula, the output is grouped by filters which is not the intended result. I'm pretty sure it's a matter of using the correct syntax in the EPMAxisOverride formula. Any help on this would be appreciated.

Thanks!

Daniel.-

[Former Member]

Does anyone else have any additional options to solve this issue? Currently, I have bypassed this problem by using the Entity Context in the member selector window to allow user entity selection in the report. However, if user wants to copy report into multiple tabs, then they are limited to just one entity because the reports will all be linked to the entity context, thus my original post trying to replicate multiple filter using the EPMAxisOverride.

[former_member186338]

Looks like without corrections of EPMAxisOverride/EPMDimensionOverride syntax logic by some new SP it's not possible to achieve the goal. Try to ask SAP support.

Vadim

[Former Member]

Thanks Vadim...I'll ask SAP support next : - )

Daniel.-

[former_member186338]

Hi Daniel,

May be it's better to use EPMDimensionOverride? I tested and found no issues with multiple filters by property and hierarchy order. Please explain your requirements in details (dimensions on axis, selection criteria etc...)

B.R. Vadim

[Former Member]

Vadim,

I was trained on EPMAxisOverride formula. I'm not familiar with the EPMDimensionOverride? What's the difference and why would it work in one and not the other one formula?

Columns: Accounts

Rows: Entities

Users will select an Entity (most of the time a brand level) and then, the report pulls Product group, PCNONE, and Profit Center entities where PG and PCNONE are descendants of Profit Centers.

I need to maintain the same hierarchy structure as in the Entity dimension as shown below.

     PG123

     PG443

     PG4509

     PCNONE23

PC23 (Total)

     PG345

     PG325

     PG908

     PCNONE34

PC34 (Total)

I don't want to group the different entity types into big groups, as I stated above, they most maintain its hierarchy order.

Thank you!

[former_member186338]

In general EPMDimensionOverride is practically equivalent of EPMAxisOverride (for single dimension).

But can you show the formula you are using?

Vadim

[Former Member]

=EPMAxisOverride("000","TRUE","ENTITY","ALL(TTL_HO_LESS_CRMRS) AND (ENTITYTYPE=PG OR ENTITYTYPE=PCNONE OR ENTITYTYPE=PC),  TTL_HO_LESS_CRMRS")

[former_member186338]

Ok, I will check tomorrow!

B.R. Vadim

[Former Member]

Thanks! By the way, the current formula is just one of many attempts trying to replicate the Member Selection and Dynamic Filters using the Edit Report option to no avail.

[former_member186338]

Ups, just checked - no override happens:

=EPMAxisOverride("000";"TRUE";"TITLES";"ALL(1ALLINFILE) AND (PLANBE=BE1000 OR PLANBE=BE3000 OR PLANBE=BE4000),1ALLINFILE")

(In my case ";" is Excel argument separator)

But with single property value it's working:

=EPMAxisOverride("000";"TRUE";"TITLES";"ALL(1ALLINFILE) AND PLANBE=BE1000,1ALLINFILE")

It's also working like this (for 2 property values):

=EPMAxisOverride("000";"TRUE";"TITLES";"ALL(1ALLINFILE) AND PLANBE=BE1000,ALL(1ALLINFILE) AND PLANBE=BE3000,1ALLINFILE")

Vadim

EPM SP18 Patch 2 NET4

[Former Member]

I'm not sure how to get the argument separator in Excel to be ";". Certainly, the formula doesn't like it when I use that syntax.

[Former Member]

Vadim - When I use your approach with the filters, here is what I get:

PG123

PG234

PG2344

...

PCNONE23

PCNONE34

...

PC23

PC34

The hierarchy order is broken because it groups the results by entity type.

[former_member186338]

";" - is the argument separator for Excel Russian local settings, just ignore it (you have to use comma).

Vadim

[former_member186338]

"your approach" - what approach? You mean:

=EPMAxisOverride("000";"TRUE";"TITLES";"ALL(1ALLINFILE) AND PLANBE=BE1000,ALL(1ALLINFILE) AND PLANBE=BE3000,1ALLINFILE") ?

yes, it will break the order.

but my first attempt to have

=EPMAxisOverride("000";"TRUE";"TITLES";"ALL(1ALLINFILE) AND (PLANBE=BE1000 OR PLANBE=BE3000 OR PLANBE=BE4000),1ALLINFILE")

resulted in not working function.

Looks like no syntax available to preserve the hierarchy order with EPMAxisOverride having multiple OR.

Vadim

[Former Member]

Got it. Yes, my first formula wasn't working. That was just one of many attempts to get the formula working. I wonder what arguments is the member selector passing to the server in order to retrieve the data while maintaining the right hierarchical order. I guess, I can use context(ENTITY) as the entity in the member selector and then add a context selector formula in the report for users to click and choose the desired entity. I feel there's got to be a way to group the dynamic filters in a formula but it is not as common as a simple data pull.

[former_member186338]

In general filters by property and hierarchy order is not a good combination. Just imagine - you don't have property correctly set on nodes - only on the base members. In this case hierarchy order will looks a bit strange

Vadim

[albert_prats]

Hi Daniel,

Try this: =EPMAxisOverride("000";;"ENTITY";"PARENTH1=TTL_HO_LESS_CRMRS AND (ENTITYTYPE=PG OR ENTITYTYPE=PCNONE OR ENTITYTYPE=PC) OR ID=TTL_HO_LESS_CRMRS "

Good luck!

Albert

[former_member186338]

Hi Albert,

PARENTH1=TTL_HO_LESS_CRMRS is not equal to Descendants

Vadim