Reporte Ranking de Clientes

SAP Business One
1

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:

Captura de pantalla (95)
Captura de pantalla (95)1366×768 80.9 KB

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

3

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
4

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