Dynamic Filter depends on dependent dimensions in EPM Report

[Former Member]

Hello,

I come across a case like suppose there are 2 dimension which are dependent say ENTITY and PRODUCT, and both the dimensions are going to be take in column axis and ACCOUNT dimension in row axis in the report.

Here is the issue like for each entity there are restricted number of products lets say C1000 has 3 products, C2000 has 5 products, C3000 has 4 products and so on. I need to display only these products when we select specific entity in the report dynamically.

I tried in couple of ways but i couldn't make it. Any suggestions to make this to be done.

Thanks in Advance,

Siva Nagaraju

[former_member186338]

Hi Siva,

Each product corresponds to single entity only - is it correct?

Vadim

[Former Member]

Hi Vadim,

Yes each product corresponds to single entity only, but as i said entity1 has 4 products, entity2 has 3 products, entity 3 has 5 products and so on....

Siva

[former_member186338]

Then easy: create property for Product dimension like PENTITY and store corresponding entity for each product. In report select products by EPMDimensionOverride on this property. Entity in member selection will be based also on this property.

Vadim

[Former Member]

Hi Vadim,

Now i can able to display only one entity products at a time, I need all the entities to be displayed corresponding to Products. Can this achievable, and how can we pass multiple members to the EPMDimensionoverride dynamically, i mean to say that without hardcoding the members.

Thanks,

Siva

[former_member186338]

Hi Siva,

Your original question was about display of single entity with related products:

"I need to display only these products when we select specific entity in the report dynamically"

In this case:

=EPMDimensionOverride("000","PRODUCT","PENTITY="&$D$1&" AND CALC=N")

And in $D$1:

=EPMSelectMember("","[ENTITY].[PARENTH1].[C1000]","","ENTITY",FALSE)

or:

=EPMContextMember(;"ENTITY")

If you properly configure Row axis MemberSelector for ENTITY to use PENTITY property of PRODUCT dimension then you will have dynamic report for single selected entity.

You can also show all products for all entities without any EPMDimensionOverride (select all base products). Just use Member Sorting and Grouping in Member Selector for PRODUCT to Group By Property: PENTITY (you can even add subtotal local member after each entity).

But there is no simple (without VBA) way to select some number of entity members and to have the report only for this group. Manually the EPMDimensionOverride formula for number of entities will look like:

=EPMDimensionOverride("000","PRODUCT","PENTITY=C1000 AND CALC=N OR PENTITY=C2000 AND CALC=N OR PENTITY=C3000 AND CALC=N")

The problem is to generate the bold string.

Vadim

[former_member186338]

It's possible to generate multiproperty override condition, but it's really comlex!

1. First create dummy EPM report with ENTITY as row axis and other fixed dimension members.

2. Create a local member for this report that will produce concatenated line in the first report line:

="PENTITY="&I8&" AND  CALC=N OR "&J9

where:

I8 - top member (ENTITY) on row axis

J9 - previous line of local member column (assuming that tom local member column will be J8)

3. Select required members for ENTITY in row axis member selector. You will have report with number of lines (number of entities), were in J8 you will have the result like:

"PENTITY=C1000 AND CALC=N OR PENTITY=C2000 AND CALC=N OR PENTITY=C3000 AND CALC=N OR "

with extra unwanted tail: " OR " - 4 characters.

4. On the same worksheet create second normal EPM report like we discussed before with EPMDimensionOverride.

Use the following for EPMDimensionOverride:

=EPMDimensionOverride("001";"PRODUCT";LEFT($J$8;LEN($J$8)-4))

5. In the Sheet Options - > Refresh Set "Keep Formulas Static That Reference Report Cells"!

Result: If you select number of ENTITY members for the first report and perform refresh - the second report will get required combinations of PRODUCT/ENTITY.

Vadim