How to sum entity using property

[Former Member]

Hi there,

I face that problematic,

I 'd like, in a report, sum all account for the entity selected.

This is easy, and native.

But, for the entity selected, i 'd like to only have the the sum of all entity corresponding to a certain property.

I explain :

Selection:

An Entity Node

Entity Property = Type

Line : Account

Result :

Only one report, which sum diyamicaly all entity for the corresponding TYPE property

I think about two alternatives :

-> using dimension formula

-> using script

Problem :

For each solution, i need to create new entity members to store the result.

In the reality, I have around 15 value for one property.

what should be your advices to do this ?

thanks a lot,

[Former Member]

For futher test to be clearer :

My formula :

sum(FILTER(descendants([ENTITY].CurrentMember,
[ENTITY].properties("ENT_TYP")="MAGASIN"),measures.periodic),solve_order=15

I get :

- [ENTITY].[#VIEW_MAG]: Parser: The end of the input was reached.

Or :

SUM(DESCENDANTS([ENTITY].[H1].CURRENTMEMBER.properties("ENT_TYP")="MAGASIN"),LEAVES)

  - [ENTITY].[#VIEW_MAG]:
CustomRollupFormula([Entity].[Entity_ID6].[VIEW_MAG]) (1, 17) The ENT_TYP dimension attribute was not found.

IIF([ENTITY].[H1].CURRENTMEMBER.PROPERTIES("CALC")="Y",sum(descendants([ENTITY].CurrentMember,[ENTITY].[H1].[ENTITY].PROPERTIES("ENT_TYP")
="MAGASIN"),LEAVES))

  - [ENTITY].[#VIEW_MAG]:
CustomRollupFormula([Entity].[Entity_ID6].[VIEW_MAG]) (1, 1) Too few arguments
were passed to the IIF function. The minimum argument count for the function is 3.

IIF([ENTITY].[H1].CURRENTMEMBER.PROPERTIES("CALC")="Y",SUM(Descendants([ENTITY].CurrentMember,[ENTITY].[H1].[ENTITY].PROPERTIES("ENT_TYP")
="MAGASIN"),LEAVES)

  - [ENTITY].[#VIEW_MAG]: Parser: The end of the input was reached.

Any help ?

Tks,

[Former Member]

Hi Olivia,

what is your release?

You want select one node and have a list of entities grouped by a property?

With the EPM this is native.

Could you please explain better your need?

Regards

Andrea

[Former Member]

Hi Andrea ,

Thanks for your reply,

Yes for sure, it is native from the EPM if i want to a list of entities,

But, my need is not exactly that : Actually, I 'd like to only have the total (as a sum; i.e. only one line) of entities filtered by property.

Tks for your help,

---

I m working with EPM SP 19

BPC 10 MS SP 12

[Former Member]

Hi Olivia,

what you need is a local member to sum the entities and a filter to extract only the entities with a specific property.

If the property is fixed you can select the property and the value directly in the Member Selector of the Report Editor and clicking on "Add Dynamic Filter" you will add your selection to the report.

If the value of the property can change (e.g. according to a drop down box) you need only to add in a cell of your Excel sheet the formula EPMDimensionOverride("000","YOURDIMENSION",'PROPERTY="&A1)

where "000" is the ID of your report

YOURDIMENSION is the name of your dimension 🙂

and in the cell A1 there is the value of your property e.g. MAGASIN

so your formula will be EPMDimensionOverride("000","Entity",'Ent_Type="&A1)

In both case you can add a Local Member (in the Report Editor)

with the formula

=SUM(EPMALLMEMBERS)

Insert After

Attached to Row Axis

it will sum every rows in your report then your entities filtered by your property.

Hope it's clear but feel free to ask me more information

Regards

Andrea

[Former Member]

Hi Andeas,

For sure,

Tks for this,

But, sorry if my need was not so clear, but  actually, i do not want to have the detail of my entities, but only a total -> that ws why i was think about script or dimension formula ?

What do you think of ?

Tks a lot

[Former Member]

Hi Andrea,  with your response to below....how can that work if you have local members in your columns that cannot be summed up at the end?  Like a vlookup or a percentage? I know you can chose row or column as a priority for a local member but what if it is different for more than a few columns/rows on the same report?

Thanks, Tracey

In both case you can add a Local Member (in the Report Editor)

with the formula

=SUM(EPMALLMEMBERS)

Insert After

Attached to Row Axis

it will sum every rows in your report then your entities filtered by your property.

[Former Member]

Hi Olivia,

got it.

Actually it works well if you have a fixed entity in your formula but I need to check how it can work with a variable member.

Let me try 🙂

Regards

Andrea

[Former Member]

Hi Tracey,

just yesterday I had a similar problem and I solved using VBA.

But I don't know exactly your need.

I had to sum all members (dynamic expansion) but in a cell out of the report.

Could you design more reports sharing the row/column axis and giving different priorities to each report according to your local members?

If you attach a screenshot maybe we can find a solution

Cheers

Andrea

[Former Member]

Hi Andrea,

I tried attaching an example we made in app shell but it said content type of this attachment is not allowed.  We've had this problem a while.  We have had to keep a lot of templates as EVDRE in BPC 10 since we couldn't resolve.  If you wouldn't mind taking a look, I'd appreciate it.  I could send you the file to open in App Shell?   But here is a brief example as well. Thanks a lot, Tracey

	Entity	a	b	c	d
	Member1	500	vlookup on Member 1	20	0.04
	Member2	100	vlookup on Member 2	40	0.40
Bitmap Bitmap	Member3	600	vlookup on Member 3	60	0.10
epmsumall members		1200	needs to be blank	120	0.10
					needs to be =d/a not sum

[Former Member]

Hi Olivia,

I think that I found a potential solution.

You need to select an entity and sum all descendants filtering by a specific property.

My idea is to use a formula in another dimension (e.g. DATASRC) in this way

Imagine that data is stored in the datasrc INPUT

You can create a new DATASRC: CALC with this formula

'SUM(FILTER(DESCENDANTS([ENTITY].[H1].CurrentMember,99,LEAVES),[ENTITY].[H1].CURRENTMEMBER.PROPERTIES("ENT_TYPE")="MAGASIN"),[DATASRC].[INPUT])

It retrieves the SUM of the filtered entities directly in the datasrc CALC.

It works for me.

Best regards

Andrea

[Former Member]

Hi Andrea,

Tks a lot for this efficient answer,

This should match my case, except if Dimension Formula does not accept to use nodes inside the MDX formula , like :

'SUM(FILTER(DESCENDANTS([ENTITY].[H1].CurrentMember,99,LEAVES),[ENTITY].[H1].CURRENTMEMBER.PROPERTIES("ENT_TYPE")="MAGASIN"),[DATASRC].[TOTAL])

Do you know it is supported ?

With AuditTrail agregation like this :

*Node1-Total

----Input

----Reclass

*Node2-Calc

Tks a lot,

[Former Member]

Hi Olivia,

try in this way

'SUM(FILTER(DESCENDANTS([ENTITY].[H1].CurrentMember,99,LEAVES),[ENTITY].[H1].CURRENTMEMBER.PROPERTIES("ENT_TYPE")="MAGASIN"),[DATASRC].[H1].[TOTAL])

It should work 🙂

Cheers

Andrea

[Former Member]

Thanks a lot for this,

We will test this this week,

just for my better understanding, as i m not an expert un mdx syntax,

what does 99 & Leaves words refers to ?

Tks,

[Former Member]

Hi Olivia,

it's similar to your EPM report.

You can retrieve the BASE Members (Leaves) until the level 99.

Instead of Leaves you could use a different argument as well as SELF, AFTER, BEFORE....

Best regards

A.

[Former Member]

Tks a lot for all your helpful answer.

Best regards

[Former Member]

You're welcome 🙂