How to write Macro to aggregate data from other Key Figures

[Former Member]

Hi SAP Experts,

I have a scenario as following:

  - I have Locations L1, L2, L3 and products P1, P2, P3. Then I combine CVCs:

L1-P1, L1-P2, L1-P3,

L2-P1, L2-P2, L2-P3,

L3-P1, L3-P2, L3-P3

  - As requirements, I create and assign to each Location a Key Figure and allow user of that Location to work on respective Key Figure only:

+ Key Figure KF1: only contains Products of Location 1: L1-P1, L1-P2, L1-P3

+ Key Figure KF2: only contains Products of Location 2: L2-P1, L2-P2, L2-P3

+ Key Figure KF3: only contains Products of Location 3: L3-P1, L3-P2, L3-P3

After each Location having calculating data on respective Key Figures, I want to aggregate data of all Key Figures into Key Figure KFTOTAL.

(I know that, usually, we can create one Key Figure only and we can create and assign Selection Profiles but my above case is to sastify special requirement)

Please help me to write Macro to satisfy this Scenario

Thanks very much for your kindly help!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

You can use 2 simple macros to achieve your objective. I may be attempting to refine Rajesh M's idea.

Macro1 - Data view formatting (add it to default event)

Step1 : Capture location value (1 iteration)

LAYOUTVARIABLE_SET( 'LOC_FILTER' ; ACT_IOBJNM_VALUE( '<INFOOBJ name of your location characteristic>' ) )

Step 2: Initialize (1 Iteration) (assumption: selection profiles explicitly choose only one selection value)

KF1(attr) = ROW_VISIBLE(1)

KF2(attr) = ROW_VISIBLE(1)

KF3(attr) = ROW_VISIBLE(1)

Step 3: Make rows visible (1 Iteration)

IF

CONDITION

     LAYOUTVARIABLE( 'LOC_FILTER' ) = 'L1'

KF1(attr) = ROW_VISIBLE(1)

KF2(attr) = ROW_VISIBLE(0)

KF3(attr) = ROW_VISIBLE(0)

ELSEIF

CONDITION

     LAYOUTVARIABLE( 'LOC_FILTER' ) = 'L2'

KF1(attr) = ROW_VISIBLE(0)

KF2(attr) = ROW_VISIBLE(1)

KF3(attr) = ROW_VISIBLE(0)

ELSEIF

CONDITION

     LAYOUTVARIABLE( 'LOC_FILTER' ) = 'L3'

KF1(attr) = ROW_VISIBLE(0)

KF2(attr) = ROW_VISIBLE(0)

KF3(attr) = ROW_VISIBLE(1)

ENDIF

Macro 2: Key figure summation (manual or background execution after users key in their values and there is no filter on location in the selection profile)

Step 1:  Aggregate the key figures (1 iteration)

VEC_SUM(

Area:  [ $KFTOTAL (INITIAL) ; $KFTOTAL (end of horizon ) ]

Area:  [ $KF1 ( INITIAL ) ; $KF1 (  end of horizon ) ] )

VEC_SUM(

Area:  [ $KFTOTAL (INITIAL) ; $KFTOTAL (end of horizon ) ]

Area:  [ $KF2 ( INITIAL ) ; $KF2 (  end of horizon ) ] )

VEC_SUM(

Area:  [ $KFTOTAL (INITIAL) ; $KFTOTAL (end of horizon ) ]

Area:  [ $KF3 ( INITIAL ) ; $KF3 (  end of horizon ) ] )

Thanks,

Rajesh

[Former Member · Accepted solution]

Dear Mr  Rajesh Jagadeeswaran and Mr Rajesh Kumar Manoharan

Thanks very much for your very helpful answers!

Mr Rajesh Jagadeeswaran,

About Macro 2: If I have a number of Key Figures, let's say 60 KF (KF1, KF2,...,KF60) (each KF for each Location) then is there any way to write Macro with only one Loop control statement(or While or For,.. or any other iteration control statement)?

Thanks very much for your help!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

If you have such a huge number of key figures, it's better to go for a function module macro. It allows to you access the PLOBS in ABAP program (i.e. function module). You can access the key figure flexibly and even call the vector sum macro function within your ABAP function module.

Thanks,

Rajesh

[Former Member · Accepted solution]

Dear Mr Rajesh Jagadeeswaran,

I write Macro as following:

But when I load the Dataview, data is not calculated correctly:

I do not set Macro as default. I assign it to a button to test. When I continue pressing this button,  system sums Accumulatively KFTOTAL with KF2 as following:

and then

Actually, KFTOTAL should always be: 21,863,000 (according to Formular: KFTOTAL = KF1+KF2)

Please Kindly help me to write correct Macro as above requirements.

Thanks very much for your kindly help!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

Your macro needs to be corrected. Please check the following screenshot for reference.

• Replace 'Forecast' with KF1
• Replace 'External Sales Order' with KF2
• Replace 'Demand' with KFTOTAL

The test results are given below. As you see, the above example is working as expected.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr Rajesh Jagadeeswaran,

Thanks very much for your helpful answers.

However, I should clarify my requirements more details:

+ Key Figure KF1: only contains Products of Location Z_0112: (Z_0112;A224_APO), (Z_0112;A255_APO)

+ Key Figure KF2: only contains Products of Location Z_0212: (Z_0212;A224_APO), (Z_0212;A255_APO)

      After each Location having calculating data on respective Key Figures, I want to aggregate data of all Key Figures into Key Figure KFTOTAL.

      Actually, KFTOTAL should play the role of a container only. It contains data of all Key Figures (KF1, KF2, KF3…) as exactly each KF does.

      Macro must add Products by Location so that when user drills down, he still finds Data of each location on KFTOTAL same as data on respective KF1, KF2…Below is my expectation:

However, current Macro adds all Product of all Location (all KFs) and then disaggregates equally to all Locations and then to Products as following (this does not satisfy requirements):

Please help me to change Macro to satisfy above requirements.

Thanks very much for your helpful supports!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

Please post a screenshot of your macro implementation. It will help me to understand and fix the issue.

Thanks,
Rajesh

[rajkj · Accepted solution]

Hi Duyennx,

Sorry for my oversight. I understood your issue. Since you used the key fig summation at aggregated level, the values were disaggregated in equal proportions at detail levels.

To avoid complex macro implementation, it is better to avoid KFTOTAL display in your pivot type display. Otherwise, you need to consider the following pseudo code

1. Aggregate by product

2. Aggregate by location

3. Then, disaggregate by location

4. Disaggregate again by product

5. Now, execute the key figure summation

Useful macro functions,

1. AGG_LEVEL

2. DRILL_UP

3. DRILL_DOWN

You can refer to 9AExamples macro book for templates and usage.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr Rajesh,

Thanks very much for your answer.

Currently, if I do 5 steps as you said above by clicking step by step on screen, system will give result as I expect.

1. Aggregate by product

2. Aggregate by location

3. Then, disaggregate by location

4. Disaggregate again by product

5. Now, execute the key figure summation.

Can you show me more details on how to write Macro so that Macro will automatically aggreate and disaggregate and execute summation, user does not have to click manually.

I have tried to write Macro but it did not run as expected.

Thanks very much for your support!

Duyennx

[rajkj · Accepted solution]

Hi Duyenx,

Here are the screenshots that can serve as a template.

Example to aggregate or roll up based on characteristic location. It's required to ensure all all the values are properly drilled down to required detail level.

You may need to repeat this macro to roll up based on product characteristic.

Example of disaggregate to lower levels based on product characteristic

You need to drill down again based on location.

At this point, your data will be at very detailed level (based on location and product characteristics).

Then, you can run the summation macro.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr. Rajesh,

Thanks very much for your guide!

However your screenshot of "Example to aggregate" does not appear on your last reply.

Could you please post that screen shot again?

Thanks very much for you help!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

Sorry for the oversight. As the images were uploaded correctly, I thought it would be ok and did not checkup the post after submitting my response.

I edited my post with the missing image and hope it serves you as a workable reference.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr. Rajesh,

Thanks very much for your answer!

However, I should say sorry because I did not clarify my expectation.

Actually, I want the Macro to aggreagate and disaggregate automatically but these action should run in back ground, not show on screen.

Currently, when I load screen, then press button to run Macro, screen changes from Total level into most detail level. I want that it is still in Total level but run Summation Macro.

Please help me to edit above Macro to satisfy requirement.

Thanks very much for your support!

Duyennx

[Former Member · Accepted solution]

Please try using 'internal' option during drill down and drill up.

Ex 😆 DRILL_UP( '9ALOCNO' ; 'internal' )

[Former Member]

Dear Mr. Rajesh,

Bellow are screenshot of my Macros. Please analyze to help me find out something wrong:

I also remove all Macros from Default event:

Thanks very much for your supports!

Duyennx

[rajkj]

Hi Duyennx,

In Execute drill down and drill up macros, the order needs to be changed as shown in the following screenshot.

Thanks,

Rajesh

[Former Member]

Dear Mr. Rajesh,

Please check your screenshot and post it again. Currently, I could not see it.

Thanks very much for your help!

Duyennx

[rajkj]

[Former Member]

Dear Mr. Rajesh,

I changed Macro according to your guid but the message "The drilldown or drillup operation chosen is not valid" still display.

I also tried spliting Macro Drill up in to two macro: Drill up on Mat, Drill up on Loc, splitting Drill down in to 2 a Macros: Drill down on Loc, Drill down on Mat. However, it still be the same.

I wonder What maybe differrences between our system and yours?

Thanks very much for your supports!

Duyennx

[rajkj]

Hi Duyennx,

Probably, there might be some mistake in your implementation. Since this post has grown too big, you can use my email id to send the screenshots. I will analyze and let you know my comments.

Thanks,

Rajesh

[Former Member]

Dear Mr. Rajesh,

Thanks very much for your help!

I also get result by following your guide.

I continue testing and find that:

Case 1. If I set event "Default" for "Sum rows" Macro (user do not need to press button), then when I load data, system gives Message:

    "Drill up sequence not allowed"

and the mouse spin arround if I press Continue.

Case 2. I do not set default for "Sum rows" Macro:

I could not drill down by each Location, Each Product. When I do this, system give Message as following:

I could drill up by "Total", drill down by, "Detail all",  "Detail all (Filter)" only.

Please kindly continue help me to edit Macro so that:

1. It can run default

2. User still can drill down by Each Location, Each Product.

Thanks very much for your enthusiatic help!

Duyennx

[rajkj]

Duyennx,

This macro can't be added to 'Default' event. Since we use macro operations to drill up and down, the default event will be triggered for each level change and subsequently, this macro will be executed uncontrollably. These complex macros are better handled manually rather than event based execution.

If you remove the macro from default event, I hope you won't get any other warning messages.

Thanks,

Rajesh

[Former Member]

Dear Mr. Rajesh,

Thanks very much for your reply!

About case 1: It's ok. I can run Macro manually.
About case 2: As I described above, I removed the macro from default event, but system still gives message when I click drill down by one Location (e.g. Z_0112) or one Product (e.g. APO_255):

"The drilldown or drillup function chosen is not valid"

Meanwhile, Other types of drill down and drill up like: "Total", "Detail all",  "Detail all (Filter)" still works.

Please check this for me.

Thanks very much for your help!

Duyennx

[rajkj]

Hi Duyennx,

It's working fine my system. I hope you had removed the collective macro from 'Default' event and then, reexecuted the t.code /SAPAPO/SDP94 to check the data view. If you still have issue, post the macro screenshot for further analysis.

Thanks,

Rajesh

[Former Member]

Dear Mr Rajesh and Nagarjuna Reddy,

Thanks for your answer!

However,the macro still does not run as I expect:

- I use 'Internal' but it still display the drill down action on the screen.

- And Macro drills down and gives result incorrectly as bellow (row Total and other rows are not in order).

The result I expect is:

This is My macro. Please help me to correct it:

Thanks very much for your support!

Duyennx

[rajkj]

Hi Duyennx,

Could you try to drill down by product as well (after location based drill down and before vector sum)?

Thanks,

Rajesh

[Former Member]

Dear Mr. Rajesh,

Sorry for my mistake. When I wrote Macro, I included drill down by product as well. However, when capturing, I did not capture it by mistake.

This is the complete Macro.

Please help me to correct it.

Thanks very much!

Duyennx

[rajkj]

Hi Duyennx,

Could you please inactivate the PLOBS_AT_LEVEL statement?

That is occurrence of the following 2 lines

• AND
• PLOBS_FOR_LEVEL

Please post the results screenshots to analyze the macro functionality.

Thanks,

Rajesh

[Former Member]

Dear Mr. Rajesh,

I inactive all following statements as you said:

• AND
• PLOBS_FOR_LEVEL

but the result still be the same as previous. There 's nothing changed:

Thanks very much!

Duyennx

[rajkj]

Hi Duyennx,

I worked with the macro implementation in our internal system and found out the reason why it was not working as expected. If we put our drill down stuff and summation in one macro, the sum operation is not being calculated at detailed level rather it was at aggregated level. I corrected the macros and check the attached screenshots for details.

Selection Profile:

Show: APO Product

APO Planning Version: 000

APO Location: 0001000702 and 0001000704

APO Product: 23682103444 and 77262343192

Header information:

APO Location

APO Product

Thanks,
Rajesh

[rajkj]

I have further enhanced the drill down and up macros considering the aggregated level checks.

[Former Member]

Dear Mr. Rajesh,

Thanks very very very much for your help.

Following your guide, I got result as expected as following:

  1. I assign event for Macro "Sum rows" to a button

  2. I load data into interactive planning talbe

  3. I press button "Sum rows" to run Macro

  4. I get right result

However, I tested one another case:

1. I load data into interactive planning talbe

2. I drill down by location, then drill up by location

3. I press button "Sum rows" to run Macro

4. All detailed data disappeared.

Please help me to change Drill up and Drill down macros in order to have a Macro that run in right way in all cases.

Thanks very much!

Duyennx

[rajkj]

Hi Duyennx,

I was able to reproduce the error in my system and fixed it by removing the 'internal' argument from the drill_up and drill_down macro functions for both 9ALOCNO and 9AMATNR. 

e.g.

DRILL_DOWN( '9ALOCNO' )

DRILL_UP( '9ALOCNO' )

Thanks,

Rajesh

[Former Member]

Hi Duyennx,

Very interesting scenario.. not sure why you require such as this will work only when locations are fixed and it's not going to change (no new addition of plants in the business).

Let me try drafting logical steps for the above requirement,

1. You need to find out which location actually user has loaded - you can use ACT_LOCATION object to capture the information and store it in a variable called X. Lets say user has loaded L1, so X=L1

2. You need to write a DO-CASE , where Case X=L1, you need to mask the visibility of KF2 & KF3. This will prevent them from entering any value on those 2 Key figures - You can use ROW_VISIBLE() function for the same

DO

CASE L1

KF2 (attributes)=

     ROW_VISIBLE (0)

KF3 (attributes) =

     ROW_VISIBLE (0)

CASE L2

.

.

.

ENDDO

Above 2 steps must be created as single macro (default)

3. Create separate macro to calculate KFTOTAL which will be addition of KF1+KF2+KF3. Since data for L1 will be only in KF1 (as per row_visible function in earlier macro), there shouldn't be any overlap of data. This should be created as default macro

Lets say L1-P1 has KF1 = 100; L2-P1 has KF2 = 120; L3-P1 has KF3 = 50; L3-P2 has KF3 = 25

When you load data at L1 level, then

KF1 = 100

KF2 = 0

KF3 = 0

KFTOTAL = 100

When you load data at P1 then

KF1 = 100

KF2 = 120

KF3 = 50

KFTOTAL = 270

Now when you load all the data then,

KF1 = 100

KF2 = 120

KF3 = 75

KFTOTAL = 295

It's just a crude way.. you can improvise a lot on this..

Thanks,

Rajesh