calculate difference dynamically and average in runtime in SAP BCP 10.0

[former_member189599]

Hi experts!

I really need your help.

I'm working with SAP BPC 10.0. I don't have experience with this tool.

I need your expertise to solve two problems.

First problem.

I have a CFM_SEC_ID dimension, but what I really need to display is a property of this on the report.

I highlighted in the image.

Can I display this values and don't display the ID on the report?
I suppose it's possible, but I can not find the necessary formula EPM.

Second problem.
I need to calculate the average between two days. One of them, I have it on dimensions property (I highlighted in the image with internal format of date in SAP). The another day, it's dynamically calculated when the report is executed.

For example, for the product's type "S10" with the two first records. If the report was executed on 31.05.2015, then I would have to calculate:

Dimension's   Property	Date of   exectution	differente   between two day
16-08-2020	31-05-2015	1904
01-09-2017	31-05-2015	824
AVERGAGE of S10		1364

Is this possible with out script logic?

[Shrikant_Jadhav · Accepted solution]

Hi Lucas,

First problem.

You need to take CFM_SEC_ID dimension in row axis and Use local member to display property value (EPMMemebrProperty). then hide that axis display only property column.

Refer user guide Page no 283

http://help.sap.com/businessobject/product_guides/boeo10/en/EPMofc_10_user_en.pdf

Second problem.

Use local member and place it after column axis.

Shrikant

[former_member189599 · Accepted solution]

I'm trying to implement your solution, but I need some extra help.
I wrote the local member formula as the image.

So, I have an error: "Error, you can not apply the formula of local component."
In Cell A9 I have the dimensión (CMF_SEC_ID) that I need the propriety (TIPO_PROD) with the following formula:
= EPMOlapMemberO("[CFM_SEC_ID].[].[1]";"";"1 - TEST";"";"000")
Can you tell me, what is wrong in the formula, please?

I understood,that if I attached the formula for the local member on Column axis, the local member will be in all rows. Is it correct?

Thanks in advance!

LUCAS

[former_member186338 · Accepted solution]

Local member formula has to be formatted using US English Excel locale delimiter and excel function names!!! It's a common mistake!

Use comma "," instead of ";"!

Vadim

[former_member189599 · Accepted solution]

I Vadim!!

I really appreciate you!

Thanks!!

[former_member189599 · Accepted solution]

Hi Shirkank!

When you tell me:

then hide that axis display only property column.

How I hide the axis column?

I can't find the option.

Thanks,

LUCAS

[former_member186338 · Accepted solution]

Using Excel "hide" column Nothing related to BPC.

Vadim

[Shrikant_Jadhav · Accepted solution]

Hi Lucas,

If you don't want to display CFM_SEC_ID dimension ( it in row axis ) as per your first post, hide that column by using using excel function.

e.i.

          A                         B    

CFM_SEC_ID               TIPO_PROD

Hide column A.

Shrikant

[former_member189599 · Accepted solution]

Hi Shrikant and Vadim!
I thought about that option.
If I do that, then I show at a detailed level. When I need is to show a line for the type of product with average of "Rend" columns and "Days".

I going to try to explain with the following images:
The report right now.

With hidden dimension CFM_SEC_ID (note column C).

What i need. (here with an Excel pivot table)

I hope I have been clearer.

Thanks!

LUCAS

[former_member186338 · Accepted solution]

Hi Lucas,

But why do you think it will be possible to aggregate average value using  some property value?

No way! For sure you can create a separate pivot table...

And what is the Tipo Producto? May be you can put it in the hierarchy of CFM_SEC_ID?

Vadim

P.S. And to use some dimension member formulas...

[former_member189599 · Accepted solution]

Vadim Hi!

What a bad new!

I thought it was like SAP BW.

Is there any way I can get the average in BPC or should solve in BW?

Now I have the dimension "product type" in the cube level.

"Product type" is a property of the CMF_SEC_ID dimension (an attribute from the side of BW).

Please if you have any ideas.

Thanks....

LUCAS

[former_member186338 · Accepted solution]

"Product type" is a property of the CMF_SEC_ID - but why not a hierarchy node?

[Former Member · Accepted solution]

This message was moderated.

[Shrikant_Jadhav · Accepted solution]

Hi Venkat,

Just do simple search,  you will get many threads,

Kindly go through the below link for reference

SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

http://help.sap.com/businessobject/product_guides/boeo10/en/EPMofc_10_user_en.pdf

EPM Add-in Academy - Enterprise Performance Management - SCN Wiki

Enterprise Performance Management (EPM) How-to Guides - Business Process Expert - SCN Wiki

Shrikant

[Former Member · Accepted solution]

Thank you Shrikant!

Best Regards

Venkat...

[former_member186338]

Hi Lucas,

"Can I display this values and don't display the ID on the report?" - what values you are talking about? Fin Period? Then Shrinkant is correct!

For the correct Excel presentation of date property use this formula in the local member:

=DATE(LEFT(EPMMemberProperty(, EPMDIM_CURRENT_MEMBER(CFM_SEC_ID), "FINPERIOD"),4),MID(EPMMemberProperty(, EPMDIM_CURRENT_MEMBER(CFM_SEC_ID), "FINPERIOD"),5,2),RIGHT(EPMMemberProperty(, EPMDIM_CURRENT_MEMBER(CFM_SEC_ID), "FINPERIOD"),2))

where FINPERIOD is the NAME of the property (not description!)

Place this local member before column axis.

Then create second local member:

=TODAY()

after the first local member.

Then - third:

=D7-E7

assuming D - column of the first local member and E - second.

Vadim