Restrict the hierarchy based on Property

[former_member229754]

Hi,

I'm having one issues about Dimension Override.

I have:

Row:

Product, Vendor.

Columns:

Time, Category etc.

Vendor is attribute/property from Product

I placed the vendor =EPMDimensionPropertyValues(,"PRODUCT","Supplier",J27) using this function

whole vendors are coming into combox.

then I'm using the function =-EPMDimensionOverride("000","PRODUCT",="Vendor= "&J27) which also work fine.

till here no problem, and data is restricted correctly by the vendor I choose in the report.

the issue here is: I have the The Product is a Hierarchy (Members & Children)

when I refresh it.. its only show the the products, do not show me the Product hierarchy with the restricted Vendors, in another words, I want to maintain the hierarchy filter by the chosen vendors.

How can I sort it out?

Thanks.

Regards,

[former_member186338 · Accepted solution]

Try:

=EPMDimensionOverride("000";"PRODUCT";"ALL;Vendor="&J27&",SELF,PARENTAFTER")

For account dimension it works fine:

=EPMDimensionOverride("000";"ACCOUNT";"ALL;ACCTYPE=INC,SELF,PARENTAFTER")

Will show the hierarchy view starting from node selected in context and with ACCTYPE=INC

Vadim

[former_member229754 · Accepted solution]

Thanks.

But still did not worked out.

How can I do If I do not want to select from the Context and select by Member?

Thanks.

Regards,

[former_member186338 · Accepted solution]

Select "by Member" what?

If you are talking about PRODUCT top node:

=EPMDimensionOverride("000","PRODUCT","ALL(SOMETOPNODE);Vendor="&J27&",SOMETOPNODE,PARENTAFTER")

My test 202 - EBIT account, containing both INC and EXP under this node

=EPMDimensionOverride("000";"ACCOUNT";"ALL(202);ACCTYPE=INC,202,PARENTAFTER")

I am using ";" as Excel argument separator...

Vadim

P.S. Please illustrate with screenshots if something is not working...

[former_member186338 · Accepted solution]

Screenshot:

If we look on member selector for INACCT:

Vadim

P.S. Formula with the link to the cell with parent node:

=EPMDimensionOverride("000";"INACCT";"ALL("&$C$3&");ACCTYPE=INC,"&$C$3&",PARENTAFTER")

[former_member229754 · Accepted solution]

Hi

I tried your logic and did not worked.

=EPMAxisOverride("000",TRUE,"PRODUCT",H27,"SUPPLIER","ID= V")

H27: ="ALL(J20);PO1001= 100072,J20,PARENTAFTER"

J20 :  =10

also tried with "10"

PO1001 is property 100072 is a value which i want to restrict.

(10 is key of top level)

and did not worked

Thanks

[former_member186338 · Accepted solution]

"I tried your logic and did not worked." - incorrect, you tried EPMAxisOverride(...)

Use EPMDimensionOverride for the first test.

Vadim

[former_member186338 · Accepted solution]

P.S. And H27

="ALL(J20);PO1001= 100072,J20,PARENTAFTER"

is absolutely incorrect  - what do you mean by J20 in the text string???

[former_member229754 · Accepted solution]

Hi Vadim,

Thanks.

J20 is CELL for reference.

i tried to put as Key and as String and did not worked.

P.S. And H27

="ALL(J20);PO1001= 100072,J20,PARENTAFTER"

              X                                         Y

Which J20 are you talking?

X or Y?

dank!

[former_member186338 · Accepted solution]

J20 inside text string is just the text J20 - not the cell reference! I have already provided you the correct syntax with concatenation! Another sample:

="ALL("&J20&");PO1001= 100072,"&J20&",PARENTAFTER"

Vadim

[former_member229754 · Accepted solution]

Hi Vadim,

Tried the same logic with Override.

What is happening is

it is coming like

<whole products with that property 100072> - this is appearing like Base level

<Hierarchy from 10> (top node) -> appearing whole Hierarchy (including another propertys

Your help is much appreciatted.

Thanks.

Regards

[former_member186338 · Accepted solution]

Sorry, without screenshot I can't help you! Looks like you are doing something wrong. If you look on my sample here:

you will see that only descendants of account 202, having ACCTYPE=INC are shown on the report.

Descendants of 202 having ACCTYPE=EXP are not shown (a lot of accounts ).

Vadim