cancel
Showing results for 
Search instead for 
Did you mean: 

How to retrieve transactional data value using script logic - BPC 10.0 NW

Former Member
0 Kudos

Hi experts,

I have the following report displaying KPIs in function of dimension TIME :

             M1   M2   M3   T1   M4   M5   M6   T2   M7   M8   M9   T3   M10   M11   M12   T4   TOTAL

KPI 1     10    10    20    40   11    22    22    55    1     1      22    24   76       3       2      81     200

KPI 2    200  200  200         200  200  200        200  200    200         200    200   200

KPI 3      5      5    10

The KPI dimension contains members like: KPI1, KPI2, KPI3

The TIME dimension has the following structure :

TOTAL

   T1

      M1

      M2

      M3

  T2

      M4

      M5

      M6

  T3

      M7

      M8

      M9

  T4

      M10

      M11

      M12

- The values of KPI1 are entered.

- KPI2 = KP1 when TIME is TOTAL, which means 200 in our case

- KP3 = (KPI1 / KPI2) * 100

How can I retrieve the TOTAL value and copy it in KPI2 in order to calculate KPI3 using script logic ?

I tried this script but it doesn't work

*WHEN KPI

*IS KPI1

*REC(EXPRESSION =(SUM(DESCENDANTS([TIME].CURRENTMEMBER, 20, LEAVES))), KPI = KPI2)

*REC(EXPRESSION =((KPI1/KPI2)*100), KPI = KPI3)

*ENDWHEN

Thanks

Maha

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Hi Maha,

Using script logic to calculate KPI like KP3 = (KPI1 / KPI2) * 100 is a bad idea. Please, use dimension member formulas instead:

In your case you have KPI1 and you want to calculate percent of total year as KPI3:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="MONTH",[KPI1]/([TIME].CURRENTMEMBER.PARENT.PARENT,[KPI1]),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="QUARTER",[KPI1]/([TIME].CURRENTMEMBER.PARENT,[KPI1]),1))

Vadim

former_member186338
Active Contributor
0 Kudos

Even better dimension formula for KPI3:

[KPI1]/(ANCESTOR([TIME].CURRENTMEMBER,[TIME].[LEVEL00]),[KPI1])

Where ANCESTOR([TIME].CURRENTMEMBER,[TIME].[LEVEL00] will return YEAR.TOTAL for the current TIME member.

Vadim

Former Member
0 Kudos

Hi Vadim,

I tried your last formula and it worked.

Thank you

Regards

Maha

Answers (1)

Answers (1)

former_member200327
Active Contributor
0 Kudos

Hi Maha,

Not sure what is the input to your script and is it supposed to run in DEFAULT or DM Package, but to get SUM of the Year you can try something like [2014.TOTAL] in EXPRESSION.

Also, second REC in same WHEN/ENDWHEN is not going to get results of the first REC statement.

Regards,

Gersh

Former Member
0 Kudos

Hi Gerch,

Thanks for your reply

My script runs in DEFAULT. I'm using it for calculating other KPIs but in this case I tried the last formula given by Vadim and it worked.

For your suggestion, using [2014.TOTAL] will work only for one year (2014 for example), while I need to do the calculation for all TIME members.

Regards

Maha

former_member186338
Active Contributor
0 Kudos

Ratio KPI's (something divided by something) have to be calculated using dimension member formulas. With correct formulas you will also have meaningful results on the nodes of other dimensions.

Vadim

former_member200327
Active Contributor
0 Kudos

Hi Maha,

2014 was just an example to give you an idea of syntax. It can be a FOR/NEXT loop with a variable, like [%YR%.TOTAL] depending on how you get the year value or something else.

I'm glad that Member Formula worked for you, but you probably know that it affects performance.

So, if your requirements allow for storing those ratios then script would be a more efficient way to do it, but in most cases (as Vadim pointed out) it can lead to wrong aggregations.

Regards,

Gersh

former_member186338
Active Contributor
0 Kudos

Hi Gersh,

FOR/NEXT loop is also not perfect in terms of performance. If for some unknown reason I decided to calculate division of moth value by year total in script logic, then:

1. Create the new property in TIME dimension like YEARMEMBER and fill it with respective YYYY.TOTAL for all months.

2. Use the following script:

*LOOKUP SameCube

*DIM KPI1YEAR:TIME=TIME.YEARMEMBER

*ENDLOOKUP

*XDIM_MEMBERSET KPI=KPI1

*WHEN KPI

*IS *

*REC(EXPRESSION=%VALUE%/LOOKUP(KPI1YEAR),KPI=KPI3)

*ENDWHEN

But in this case I think that dimension member formula is the right solution.

B.R. Vadim