Grouping grand total

[Former Member]

Dear SAP Experts,

I kind of got a case with bex query.

Because of join table, I have query data in bex like this :

Sales order#      Material#      Billing#      Bill Amount      COGS      Net SO Amount

S1                        M1           B1                     1000         2000           6000

S1                        M1           B2                     500           2000            6000

S1                        M2           B3                     800           3000            6000

S1                        M2           B4                     400            3000           6000

Grand Total                                                  2700          5000               6000

I want to have total for bill amount column = 2700 ,

For COGS total = 5000 (group by Sales order# and Material#),

For Net SO amount total = 6000 ( net so amount group by sales order#).

Could help me give some way to achieve the results?

Kindly help and may thanks

Regards

[anshu_lilhori · Accepted solution]

This can be solved with the help of Exception aggregation.

Make a new formula and put COGS inside it--Hit the aggregation tab--Exception aggregation as Summation and reference characteristic as Material.

Make a new formula and put Net SO Amount inside it--Hit the aggregation tab--Exception aggregation as Summation and reference characteristic as Sales order.

Give it a try and let me know if this works or not.

Regards,

AL

[Former Member · Accepted solution]

Thank you very much Anshu.

I try it right away.

[Former Member · Accepted solution]

HI Anshu,

I am testing in net so amount column :

Sales order#      Material#      Billing#      Bill Amount      COGS      Net SO Amount

S1                        M1           B1                     1000         2000           6000

S1                        M1           B2                     500           2000            6000

S1                        M2           B3                     800           3000            6000

S1                        M2           B4                     400            3000           6000

Grand Total                                                  2700                             24,000

The result ini cogs total is 24,000 not 6,000.

Kindly help me.

[anshu_lilhori · Accepted solution]

Yen,did you get the correct total for COGS which should be 5000 i guess ?

Moreover for Net So amt.Change the aggregation to First value and reference characteristic as Billing #

Regards,

AL

[Former Member · Accepted solution]

Hi Anshu,

I am doing for net so amount just now.

I have not done in COGS yet.

Ok, I will try again.

Thank you, Anshu.

[Former Member · Accepted solution]

HI Anshu,

It's workss..thank you very much..

But I have another issue with 2 grouping by, but I better start new topics..

Hoping that you want to help me again...