Hide Summation in template

[former_member229754]

Hi,

I want to hide the values where Hierarchy member is a node for a specific account

for example:

Product, Qty, Price, Value

ProductGRP A, 35, 20, 3500

-- Product 1, 15, 10, 1500

-- Product 2, 20, 10, 2000

ProductGRP B, 20, 35, 2500

-- Product3, 10,10, 1000

-- Product4, 10,15, 1500

Assume that Qty and Price Are input for user and this is a hierarchy

How can I hide from

ProductGRPA the 35 and 20

and ProductGRP B the 20, 35?

Thanks.

Regards,

[Former Member · Accepted solution]

Screenshot of your template layout please ??

[former_member229754 · Accepted solution]

[Former Member · Accepted solution]

May be you can do this,..

Use local member instead of hierarchy(productGRPA,B) to fetch only total for 'value' field.

neglect QTY & Price cells.

[former_member229754 · Accepted solution]

I did not understand.

Can you please share me an example.

Dank

[Former Member · Accepted solution]

find image for reference

Go to Edit Report-->insert loc member(use only formula for value field)

Only 'value' field gives you total then rest of the fields remain as shown above.

[former_member186338 · Accepted solution]

Just some explanation required: in the loocal member formula use if condition for the column header cell - if it's "value" then sum, if not then empty sting "".

Vadim

[former_member229754 · Accepted solution]

Thanks.

But, I forgot to mention that there is a chance they overwrite the value, or just put value directly.

[Former Member · Accepted solution]

use summation formula it gives you results for 'value' field

[former_member186338 · Accepted solution]

"they overwrite the value, or just put value directly." - who will overwrite? User? Use protection!

[david_ings · Accepted solution]

Hi,

You could also try using the EPM formatting sheet for those intersections with content override. E.g. for ProductGRPA, account QTY, content override of: '="" so that those values are now always blank.

Then as Vadim suggests, add protection to those cells as well so noone can input on them, and also best add them as Read Only members in the EPM report editor so that the system won't try and clear your values.

David

[former_member229754 · Accepted solution]

Dear All,

The issue here is that I have many columns with different accounts.

What I did is:

Legend:

Column R = Product.Calc = Y (return if the Hierarchy member is Parent or not parent)

Column AE is a target column that I want to fill the color based on that

Sub AFTER_REFRESH()

    Dim LastRow As Long

    Dim i As Long

    LastRow = Range("R" & Rows.Count).End(xlUp).Row

    For i = 36 To LastRow

        If Range("R" & i).Value = Y Then

            Range("AE" & i).Interior.ColorIndex = 1 (black)

        End If

    Next i

End Sub

I also believe this might be not a recommendable solution since it is VB and can take perfomance issues later on.

I will find another way later on to optimize this solution based on BPC standards using EPMFormatting

Thanks again!

[former_member229754 · Accepted solution]

Hi,

The user can plan in two ways

1 - Quantity (then from script logic it will calculated qty * price)

or

2 - directly in Values directly (because sometimes for specific products they play directly based on amount)

that's why I cannot use Local Members here, or I'm wrong?

Only thing I can protect here is define a property for the products planned for amount.

[former_member186338 · Accepted solution]

Hi,

I do not understand why do you need VBA in this case - correct local member formula will do the job. And the color formatting can be done using formatting sheet based on property...

Vadim

[former_member229754 · Accepted solution]

Hi,

The user can plan in two ways

1 - Quantity (then from script logic it will calculated qty * price)

or

2 - directly in Values directly (because sometimes for specific products they play directly based on amount)

that's why I cannot use Local Members here, or I'm wrong?

Only thing I can protect here is define a property for the products planned for amount.

[former_member186338 · Accepted solution]

I am talking about local member for product group...