Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Creating a MDX REC that will create/update a row with the SUM of a GROUP BY.

Hi Experts!

I'm kinda new to MDX.

I Want to make a Script that will create or update a new row into a cube that will contain the SUM of an specific group by of dimensions.

I will try to explain What I want to do in SQL Logic.

Supose I have a certain table with n rows.

I want to create a new row, or update it if it already exists that will contain the SUM of all values of an aggregation based on two columns.

For instance.

I have this data:

DIM1     DIM2     DIM4     VALUE

name     name     stockx     10

name     name1   stocky     15

name     name     stockz     12

name1   name     stocku     21

name1   name1   stockl      30

name1   name1   stockm    42

I want to be able to run the script, and then get this result

DIM1     DIM2     DIM4          VALUE

name     name     stockx          10

name     name1   stocky          15

name     name     stockz          12

name1   name     stocku          21

name1   name1   stockl           30

name1   name1   stockm         42

name     name     stocksum     22

name     name1   stocksum     15

name1   name     stocksum      21

name1   name1   stocksum      72


In SQL, I would create a temporary table, containing all the Sums the table grouped by DIM1 and DIM2. Example:


SELECT DIM1, DIM2, SUM(VALUE) FROM Table GROUP BY DIM1, DIM2;


And then I would iterate trough each record, and store it in the Table, using the DIM 1 and DIM2 and creating a new DIM4 (In this case StockSum). then adding the added value to the column.

Is it possible using MDX in BPC?

How should I make my REC statement in order to get this result, or what should I use to make this script work?


Thanks a lot in Advance!!


Tags:
Not what you were looking for? View more on this topic or Ask a question