Skip to Content

How to arrive in this kind of report?

Hi guys,

I have a question on how can I get this kind of report. Is this possible in query designer or in the back-end?

Here is my final report format per MONTH:

But in order for me to tag a material as class A, B, or C, I need to do some computations as shown below.

First problem is, how can I sort the MATERIAL per GROUP based on their AMOUNT in DESCENDING ORDER? I'm thinking of creating a CONDITION of TOP N (N = 100,000 to be safe since we have thousands of materials) with reference to MATERIAL and GROUP. I did not try this yet but do you guys think this will work as shown above?

EDIT:

Tried using the CONDITION but it is not sorting the AMOUNT based on the MATERIAL NUMBER per GROUP. Maybe there is other way to achieve this?

EDIT:

The other problem is, how can I compute the NUMERATOR part? There is a CUMULATED option in the CALCULATIONS tab of a key figure in query designer but as far as I know it is only for DISPLAY PURPOSE. So even if I can see the CUMULATED value along the column, I still cannot compute the % part since the values shown is not the real value. In short, using it will not work. How do you guys think I can achieve the NUMERATOR part? I already have an idea about this but I need to do some back-end work. I'm trying my luck and hoping if this can be achieved in the query designer or if I can get simpler solution. :-)

Amount = this is my key figure (CAN BE DONE)

Numerator = this is the cumulative value of all materials per group starting from the highest to the lowest value of amount

Denominator = this is the total amount of all materials per group (CAN BE DONE).

I think I can do the denominator part using CONSTANT SELECTION.

% = Numerator / Denominator (CAN BE DONE)

Class = material will be tagged as class A if it is part of the TOP 80%; class B if it is part of 80% - 95%; and class C for other 5%. (CAN BE DONE)

I think I can do the CLASS tagging using CELL. But if you have other suggestions, please say so. I did not try it yet since my problem first is how to compute the NUMERATOR part. :-D

Thank you so much.

Loed

delete.jpg (466.5 kB)
delete.jpg (268.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jan 26 at 12:18 PM

    Hi,

    Search for ABC Classification . Use T-Code RSDMWB and cread model and use in Analysis Process Designer(RSANWB)

    https://blogs.sap.com/2005/08/10/customer-prioritization-using-abc-classification-model/

    Regards,

    Anand

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Anand,

      I tried to use the ABC classification. However, it's not working for 2 or more reference characteristics? Is that correct? Or did I miss something?

      In the blog that you have posted, it is only using CUSTOMER ID. In my case, if I only use MATERIAL, the ABC classification is working. However, I also need to consider the GROUP infoobject. How will I make it work? I already tried to have 2 CLASSIFIED OBJECT (material and group) but it is not working properly.

      Any help?

      Thank you.

      Loed

  • Jan 29 at 08:05 AM

    Hi Anand,

    Here are the steps I did.

    First.

    RSDMWB to create the model.

    I saw these in the blog you have posted.

    1. Classified Object – In our example its Customer ID
    2. Classification Criterion – Revenues generated by customers.
    3. Grouping Attribute – where to store result.

    So I did assign the ff objects:

    1. Classified Object – GROUP and MATERIAL NUMBER
    2. Classification Criterion – AMOUNT
    3. Grouping Attribute – I DID NOT USE THIS. I can't see the purpose of using the grouping attribute since I already can use the ABC CLASS object in the APD. I tried including and excluding this type but I did not notice any difference in the result. Can you explain me the real purpose of this type? Do I really need to add it in my ABC MODEL?

    ***I used Cumulated Percentage of Classified Object since I wanted to consider both of my classified objects..

    A from 0 to 80 CLOSED

    B from 80 to 95 OPEN ON LEFT

    C from 95 to 100 OPEN ON LEFT

    Second.

    Created the APD.

    QUERY -> ABC CLASS -> MASTERDATA

    ***I will be using the result of ABC class as the value of the CLASS infoobject, which is an attribute of MATERIAL NUMBER..

    ***My query contains GROUP and MATERIAL NUMBER in ROW and AMOUNT object in COLUMN..

    Did I miss something?

    Thank you.

    Loed

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 29 at 08:20 AM

    Hi,

    if you can convince the business to use 1 - 2 - 3 in stead of A - B - C ... then you just need a calculated KF to have your result

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Koen,

      Which KF percentage are your referring? Is it the % in my sample above? But I can't get % if I don't have the values of my NUMERATOR. Do you also have an idea how to get the NUMERATOR part?

      Regards,

      Loed

  • Jan 29 at 08:57 AM

    create formula Numerator= [amount] but set cumul flag on in calcutation tab

    Add comment
    10|10000 characters needed characters exceeded

    • I already made this work by doing the config in the back-end. I'm just trying my luck if I could also find a way in doing this in the report level. The ABC classification is also one of the ways to do this but I can't make it work when using 2 fields. But using it in 1 field, ABC classification is working great. Thanks to Anand for the idea, didn't know that it exists in BW. LOL! There are also some other tools in RSDMWB, I will also try to study them. Have a look at it Koen and tell me you made it work with 2 fields. :-D

      Regards,

      Loed