Ayuda SAP

Reporte Ranking de Clientes

Buen dia Compañeros, estoy realizando un reporte en SAP B1, pero no logro almacenar el campo “OTROS” en una fila que acumule los totales como en la siguiente imagen:

Mi codigo es el siguiente:

ALTER  PROCEDURE REPORTERANKINGDECLIENTES(fecha1 date, fecha2 date)                           
                        
AS
BEGIN
 
 --FACTURAS 
SELECT  T0."DocEntry", T0."DocNum", T0."CardCode", T0."CardName", T1."LicTradNum", T0."DocDate", 
T0."CANCELED", T0."DocCur", T0."BaseAmnt", T0."BaseAmntFC", T0."VatSum", T0."VatSumFC", 
T0."DocTotal", T0."DocTotalFC", T0."DpmAmnt", T0."DpmAmntSC",
T2."LineTotal", T2."GTotal", T2."LineVat" 

FROM OINV T0 
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
INNER JOIN INV1 T2 ON T0."DocEntry" = T2."DocEntry"
 
where   T0."DocDate" between fecha1  AND fecha2  and T0."DocType" in ('I', 'S')
AND T0."CANCELED" = 'N' 

UNION ALL

--Notas de debito

SELECT T0."DocEntry", T0."DocNum", T0."CardCode", T0."CardName", T1."LicTradNum", T0."DocDate", 
T0."CANCELED", T0."DocCur", T0."BaseAmnt", T0."BaseAmntFC", T0."VatSum", T0."VatSumFC", 
T0."DocTotal", T0."DocTotalFC" , T0."DpmAmnt", T0."DpmAmntSC",
T2."LineTotal", T2."GTotal", T2."LineVat" 

FROM OINV T0 
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
INNER JOIN INV1 T2 ON T0."DocEntry" = T2."DocEntry"
  
  where  T0."DocDate"  between fecha1 AND fecha2  
  and T0."DocSubType" = 'DN' 
AND T0."CANCELED" = 'N' 

UNION ALL

 --notas de credito tipo articulo

SELECT  T0."DocEntry", T0."DocNum", T0."CardCode", T0."CardName", T1."LicTradNum", T0."DocDate", 
T0."CANCELED", T0."DocCur", T0."BaseAmnt"*-1, T0."BaseAmntFC"*-1, T0."VatSum"*-1, T0."VatSumFC"*-1, 
T0."DocTotal"*-1, T0."DocTotalFC"*-1, T0."DpmAmnt"*-1, T0."DpmAmntSC"*-1,
T2."LineTotal"*-1, T2."GTotal"*-1, T2."LineVat"*-1 

FROM ORIN T0 
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
 INNER JOIN INV1 T2 ON T0."DocEntry" = T2."DocEntry"

WHERE  T0."DocDate" between fecha1  AND fecha2  
and T0."DocType"='I' 
AND T0."CANCELED" = 'N' 

UNION ALL

 --notas de credito tipo servicio

SELECT T0."DocEntry", T0."DocNum", T0."CardCode", T0."CardName", T1."LicTradNum", T0."DocDate", 
T0."CANCELED", T0."DocCur", T0."BaseAmnt"*-1, T0."BaseAmntFC"*-1, T0."VatSum"*-1, T0."VatSumFC"*-1, 
T0."DocTotal"*-1, T0."DocTotalFC" *-1, T0."DpmAmnt"*-1 , T0."DpmAmntSC"*-1,
T2."LineTotal"*-1, T2."GTotal"*-1, T2."LineVat"*-1 

FROM ORIN T0 
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
 INNER JOIN INV1 T2 ON T0."DocEntry" = T2."DocEntry"

WHERE  T0."DocDate" between fecha1  AND fecha2   
and T0."DocType"<>'I'      
AND T0."CANCELED" = 'N' 

;
END;

Espero su apoyo,
Gracias

Hola @francisromero
Te invitamos a pasar por la seccion de #presentaciones
Lo que tu requieres la forma mas facil es mediante un grupo en crystal reports,
Si lo quieres hacer desde sql crea una consulta totalizando con SUM y realizas el UNION.

Andres Ramirez Jaramillo :colombia:

2 Me gusta

a todo el SELECT aplicale un WITH y luego le realizas un select aplicando group by