Skip to Content
0
Aug 03, 2023 at 05:30 AM

Suggest to me this Quest on SAP B1 HANA

34 Views

Dear Expert,

I have problem this query below. I have grouping function to split group and show subtotal and grandtotal but it's grandtotal wrong position to top row (Red) what i want to below row (green).. Please you resolve to me...
Code :

SELECT

Case When Grouping(A."Area") = 1 and Grouping(A."Code") = 1 and Grouping(A."Name") = 1 and Grouping(A."Sales") = 0 Then 'SubTotal'

When Grouping(A."Area") = 1 and Grouping(A."Code") = 1 and Grouping(A."Name") = 1 and Grouping(A."Sales") = 1 Then 'GrandTotal'

Else Cast(A."Code" as varchar(10)) end as "Code",

A."Name",

A."Area",

Case when A."Name" is null then Null Else A."Sales" end as "Sales",

Sum("TO") as "Total",

Sum("PF") as "Profit"

FROM

(

SELECT

T0."CardCode" as "Code",

T0."CardName" as "Name",

T2."U_CUSTOMER_AREA" as "Area",

T3."SlpName" as "Sales",

SUM(T1."LineTotal")-ROUND(((SUM(T1."LineTotal"))*(T0."DiscPrcnt"/100)),2,ROUND_HALF_UP) as "TO",

SUM(T1."GrssProfSC") as "PF"

FROM

ORIN T0

INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"

LEFT JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"

LEFT JOIN "BEC2"."@SALESTEAM" T4 ON T3."U_Sale_Team" = T4."Code"

WHERE

T0."CANCELED" = 'N' and

T4."Code" = '11160' and

T0."DocDate" between '01.07.2023' and '31.07.2023' and

T1."AcctCode" = '4011010' and

T1."LineTotal" > 0

GROUP BY

T0."CardCode", T0."CardName", T2."U_CUSTOMER_AREA",T4."Code", T3."SlpName",T0."DiscPrcnt"

ORDER BY

T0."CardCode"

) A

Group by

GROUPING SETS

(

(A."Sales"),

(A."Code",A."Name",A."Area",A."Sales"),()

)

ORDER By

A."Sales",

Sum("TO")

image.png

Attachments

image.png (123.9 kB)