Skip to Content
0

Help with MDX Member Formula in BPC 10.1

Feb 28 at 07:56 PM

108

avatar image
Former Member

Dear,

I have created a BPC 10.1 Standard model with the following dimensions:

- Category;
- Sales Org;
- Measure;
- Currency;
- Time.

For example, in the current Rolling Forecast volumes are stored in Measure "VOLUME", Category "RF201802". Actuals Volumes are stored in Measure "VOLUME" and Category "ACTUALS".

I have Measures like "Prior RF Volume" and "Prior Year Volume" and my idea is to use Member Formula to retrieve the Prior RF Volume for the same month and the Prior Year Actuals. I thought to use an attribute on Category to identify the Prior RF and an Offset on Time Dimension for the CURRENTMEMBER, but tried different MDX codes and not able to achieve it.

Does somebody know how can i do these two Measures using MDX?

Thanks,

Andre

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Vadim Kalinin Feb 28 at 08:06 PM
0

Sorry, but not clear! "I have Measures like "Prior RF Volume" and "Prior Year Volume" and my idea is to use Member Formula to retrieve the Prior RF Volume for the same month and the Prior Year Actuals." - what do you mean???

You are talking about custom measure or member formula?

Please spend some time to accurately explain your requirements with some data sample!

P.S. Not all dimensions specified for the model (Account?) - better to provide screenshot!

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 28 at 10:11 PM
0

Hi Vadim, thanks for your quick response.

These are the dimensions in my model (in the first post i translated form Portuguese to English):

I have three members in METRICA (Account) dimension, PREVISAO, PREVISAO_MES_ANTERIOR and HISTORICO_MES_ANTERIOR. In the screenshot below, I have inputs for PREVISAO for CICLO (Category) 022018:

For the next CICLO, which is 032018, PREVISAO_MES_ANTERIOR should show the values from the Previous CICLO, which is 022018:

For this scenario I thought to use a property in CICLO dimension to identify the Previous CICLO, and retrieve values using a Member Formula (MDX), but i couldn't achieve it using FILTER MDX command.

The second scenario is METRICA HISTORICO_ANO_ANTERIOR, which in this case, should retrieve Actuals 2017 values from CICLO = ACTUALS, doesn't matter the selected CICLO by the user:

For this scenario I thought to use a MDX formula to Offset the period, but couldn't find anything either.

Please let me know if it is clear now.

Thanks,

Andre


Show 3 Share
10 |10000 characters needed characters left characters exceeded

"For this scenario I thought to use a property in CICLO dimension to identify the Previous CICLO, and retrieve values using a Member Formula (MDX), but i couldn't achieve it using FILTER MDX command."

Please post your code!

"The second scenario is METRICA HISTORICO_ANO_ANTERIOR, which in this case, should retrieve Actuals 2017 values from CICLO = ACTUALS, doesn't matter the selected CICLO by the user:"

LAG?

P.S. Or better

ParallelPeriod
0
Former Member

Hi Vadim,

Follow below the code for the Member Formula:

([METRICA].[PREVISAO],FILTER([CICLO].AllMembers,[CICLO].CurrentMember.Properties("MES_ANTERIOR") = "X"))

The following error happens when i refresh the report:
code: UJO_READ_EXCEPTION_000
severity: error
description: MDX statement error:"Internal error : no result set"
log id: 051MhcwE7jY7gxxn4WB1AW
DATAVALUE:Internal error : no result set
MDX:MDX WITH
MEMBER [METRICA].[PARENTH1].&[PREVISAO_MES_ANTERIOR] AS '([METRICA].[PARENTH1].&[PREVISAO],FILTER([CICLO].AllMembers,[CICLO].CurrentMember.Properties("/CPMB/KIP71IP") = "X"))' SOLVE_ORDER=5
MEMBER [METRICA].[PARENTH1].&[PREVISAO_VALORADA] AS '[METRICA].[PARENTH1].&[SALDO] * ([CICLO].[PARENTH1].&[CICLO_NA],[VERSAO].[PARENTH1].&[VERSAO_NA],[METRICA].[PARENTH1].&[PRECO])' SOLVE_ORDER=5
MEMBER [METRICA].[PARENTH1].&[SALDO] AS '[METRICA].[PARENTH1].&[PREVISAO] - [METRICA].[PARENTH1].&[DEVOLUCAO]' SOLVE_ORDER=5
MEMBER [Measures].[PERIODIC] AS 'IIF([METRICA].[PARENTH1].CURRENTMEMBER.PROPERTIES("/CPMB/ACCTYPE")="INC",-[Measures].[/B28/S_SDATA],IIF([METRICA].[PARENTH1].CURRENTMEMBER.PROPERTIES("/CPMB/ACCTYPE")="EXP",[Measures].[/B28/S_SDATA],IIF([METRICA].[PARENTH1].CURRENTMEMBER.PROPERTIES("/CPMB/ACCTYPE")="AST",([Measures].[/B28/S_SDATA], CLOSINGPERIOD([TIME].[PARENTH1].[LEVEL 02],[TIME].[PARENTH1].CURRENTMEMBER)),IIF([METRICA].[PARENTH1].CURRENTMEMBER.PROPERTIES("/CPMB/ACCTYPE")="LEQ",-([Measures].[/B28/S_SDATA], CLOSINGPERIOD([TIME].[PARENTH1].[LEVEL 02],[TIME].[PARENTH1].CURRENTMEMBER)),-[Measures].[/B28/S_SDATA]))))' SOLVE_ORDER=3
SELECT NON EMPTY
{[PRODUTO].[PARENTH1].&[200001681] } *
{[METRICA].[PARENTH1].&[DEVOLUCAO],
[METRICA].[PARENTH1].&[HISTORICO_ANO_ANTERIOR],
[METRICA].[PARENTH1].&[PAV],
[METRICA].[PARENTH1].&[POG],
[METRICA].[PARENTH1].&[PREVISAO],
[METRICA].[PARENTH1].&[PREVISAO_MES_ANTERIOR],
[METRICA].[PARENTH1].&[PREVISAO_VALORADA],
[METRICA].[PARENTH1].&[REAL],
[METRICA].[PARENTH1].&[SALDO] } ON 1,
{[TIME].[PARENTH1].&[2018.01],
[TIME].[PARENTH1].&[2018.02],
[TIME].[PARENTH1].&[2018.03],
[TIME].[PARENTH1].&[2018.04],
[TIME].[PARENTH1].&[2018.05],
[TIME].[PARENTH1].&[2018.06],
[TIME].[PARENTH1].&[2018.07],
[TIME].[PARENTH1].&[2018.08],
[TIME].[PARENTH1].&[2018.09],
[TIME].[PARENTH1].&[2018.10],
[TIME].[PARENTH1].&[2018.11],
[TIME].[PARENTH1].&[2018.12],
[TIME].[PARENTH1].&[2018.TOTAL],
[TIME].[PARENTH1].&[2019.01],
[TIME].[PARENTH1].&[2019.02],
[TIME].[PARENTH1].&[2019.03],
[TIME].[PARENTH1].&[2019.04],
[TIME].[PARENTH1].&[2019.05],
[TIME].[PARENTH1].&[2019.06],
[TIME].[PARENTH1].&[2019.07],
[TIME].[PARENTH1].&[2019.08],
[TIME].[PARENTH1].&[2019.09],
[TIME].[PARENTH1].&[2019.10],
[TIME].[PARENTH1].&[2019.11],
[TIME].[PARENTH1].&[2019.12],
[TIME].[PARENTH1].&[2019.TOTAL] } ON 0
from [SID_DEMANDA]
WHERE ( [CICLO].[PARENTH1].&[032018],
[CLIENTE].[PARENTH1].&[CLIENTE_NA],
[ESTRUTURA_VENDA].[PARENTH1].&[40494],
[RPTCURRENCY].[PARENTH1].&[LC],
[VERSAO].[PARENTH1].&[INICIAL],
[Measures].[PERIODIC] ) CELL PROPERTIES FORMATTED_VALUE

For the ParallelPeriod, I've searched some syntax and used the code below, which doesn't work as well:

((PARALLELPERIOD([TIME].LEVEL00,-12,[TIME].CURRENTMEMBER),[METRICA].[PREVISAO])

I've received "No internal set" error when validating the formula.

THanks,

Andre

0

Ups, you are writing formulas with some strange syntax... I will try to help!

0
Leila Lappin
Feb 28 at 10:27 PM
0

Hi,

I think your question is more in regard to runtime MDX query. If I am correct then the following maybe helpful to provide a perspective for your design questions.

The following are important factors

  • BPC measure formulas work with account types and Time dimension. This is easy to see by just checking BPC standard measure. In this regard the account and time dimensions are the most important in the measure formulas.
  • Dimension formulas can be defined on any dimension and they will become part of the runtime MDX query.

The runtime MDX query that is generated for a report is the result of report structure, member selection and so on, measure formulas and dimension formulas.

Please let me know if you are interested in having more overall information overall about runtime MDX queries and how they are constructed.

Best Regards,

Leila Lappin

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Leila,

About BPC measure formulas you refer to Periodic, YTD and QTD right? These ones work on the ACCTYPE Account attribute to determine if the SDATA should or shouldn't be reversed, if i understand you right.

My question is about Dimension formula for those two specific members in "METRICA" dimension that I explained in my previous answer, the "PREVISAO_MES_ANTERIOR", which should retrieve values from previous member in "CICLO" dimension and "HISTORICO_ANO_ANTERIOR", which should retrieve values from the same month of the Previous Year. How to build Member Formulas to achieve the requirements for these two specific members?

Thanks,

Andre

0

Hello,

Thank you for the clarification. I see that you have already provided more details. Thank you

Best Regards,

Leila Lappin

0
Vadim Kalinin Mar 01 at 06:56 PM
0

For the first case with property currently I don't know a solution (at least for my BW750 SP06 BPC standard).

The correct formula has to be like (test in the copy of ENVIRONMENTSHELL):

Property for CATEGORY dimension has to contain the full name of prev CATEGORY member:

PREVCAT property: for member Plan contain: [CATEGORY].[Budget] etc...

([PL010],STRTOMEMBER([CATEGORY].CURRENTMEMBER.PROPERTIES("PREVCAT")))

[CATEGORY].CURRENTMEMBER.PROPERTIES("PREVCAT") will return string "[CATEGORY].[Budget]"

and

STRTOMEMBER(...) will convert the string to member

For example STRTOMEMBER("[CATEGORY].[Budget]") will return correct result

Unfortunately combination of PROPERTIES and STRTOMEMBER is not working correctly (old bug, still not solved).

For the second case ParallelPeriod will work fine! Correct formula:

([PREVISAO],PARALLELPERIOD([TIME].LEVEL00,1,[TIME].CURRENTMEMBER))

1, not -12 ! "1" - means lag one year!

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Vadim, I appreciate your help on testing and fixing the codes. Thanks!

If i understood, for the first case won't work now due to a not solved bug, correct? For the second case I've tried the formula but it didn't work. I've disabled HANA MDX, cleaned HANA Temp tables and refreshed Dim Cache, but still not working. Is the syntax for the Level correct? I've tried LEVEL00, [LEVEL00] and [LEVEL 00] but none is working.

Thanks again,

Andre

0

For the first case I tested only on BW750 SP06 without HANA. May be on SP 08 the result will be different due to the note: https://launchpad.support.sap.com/#/notes/2431828

Second formula works absolutely fine in my case:

PL250 - member with a member formula, reading account PL010 same month from prev year.

dform.png (26.9 kB)
0

For HANA MDX it has to be LEVEL 00

https://launchpad.support.sap.com/#/notes/2164827

0

P.P.S.

May be syntax like:

([PREVISAO],PARALLELPERIOD([TIME].[LEVEL 00],1,[TIME].CURRENTMEMBER))


0