BPC Report : Subtotal by Dimension Property's value

[Taketa]

Dear BPC Experts,

I'd like to use the subtotal function per the dimension "property's value".

I think the "excel" subtotal function would be a possible solution,

but is there any way to achieve this via using the BPC functionality?

Here's an example:

[ Dimension A ]

ID| |EVDescription||PropertyA

100||Description A||1000 ==> ACCOUNT Expense 100 USD

101||Description B||1001 ==> ACCOUNT Expense 200 USD

102||Description C||1002 ==> ACCOUNT Expense 300 USD

103||Description D||1001 ==> ACCOUNT Expense 400 USD

104||Description E||1000 ==> ACCOUNT Expense 500 USD

105||Description F||1002 ==> ACCOUNT Expense 600 USD

106||Description G||1002 ==> ACCOUNT Expense 700 USD

In this example, I'd like to get the subtotal value of ACCOUNT Expense

per PropertyA value which is not a dimension ID in my report(Row:Dimension A, Column:ACCOUNT)

like below;

PropertyA||Expense

Subtotal 1000|| 600 USD

Subtotal 1001|| 600 USD

Subtotal 1002||1600 USD

Any advice would be highly appreciated.

Thanks in advance.

Fumi

[Former Member · Accepted solution]

Hi,

If you have hierarchies in your account dimension, as per the requirement, you will get

the subtotals automatically. Or else you can sort and give sub-totals there also you will

the results as per your requirement.

Raghu B.S.

[Taketa]

Hi Petar,

Thanks for your info!

I'll try it and update the result later.

Regards,

Fumi

[Former Member]

If you are creating a report using EVDRE (or multiple EVDRE's) you have the option to aggregate the values based on the retrival set. If you right click in the memebrset say of rows expansion, you should see the proces for building a filter. It would end up being a value like PROPERTYA="A". Then with that set, you may have to add an AFTER parameter for the rows, that would then contain the EVSUM definition which would tell the query to aggregate the values.

For multiple Properties, you may build the requests in the row memberset using Pipes, "|" , to call seperate property sets, but remember to add more row key rangeareas for every piped area.

Hope this helps.

[Taketa]

Hi Raghu,

Thanks for your information!

According to your advice, it works fine when I created a hierarchy.

However, in the customer's case, the dimension is not account, but a user-difined dimension for the project.

This project dimension has the property "Classification no." which has to be maintained frequently...

So, it might not be acceptable by customer to create a hierarchy everytime the user maintains the project master,

but I'll try to ask the customer to do it.

>Or else you can sort and give sub-totals there also you will the results as per your requirement.

The above means "Excel" functionality, not BPC's specific functionality, right?

Thanks in advance,

Fumi