Skip to Content
avatar image
Former Member

Ayuda con Query

Hola buen día

Por favor si me pudieran ayudar, necesito hacer un reporte en Crystal Reports,  tipo estado de resultados donde obtenga el total de las ventas (INV1) en pesos $ y en unidades de medida, es decir toneladas y litros, ya que se venden productos sólidos y líquidos. También necesito obtener de OACT el costo de ventas, nomina, energia electrica, gastos de fabricacion, gastos de administracion y de mantenimiento.

Ya hice las consultas, sin embargo no se si esten bien porque no soy muy buena en SQL. son varias consultas y al pasarlas a Crystal Reports no me deja crearlas en un solo informe 😔, puedo hacer una sola consulta donde me de toda esta información para hacer un solo informe en Crystal Reports ?

SAP B1 9.0 PL17

SAP Crystal Reports 2013

---------------------------------------------------VENTAS EN PESOS $$TONELADAS---------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'DESHIDRATADO' AS TIPO FROM OACT WHERE  ACCTCODE='41020000' OR ACCTCODE='42020000' OR ACCTCODE='43020000' OR ACCTCODE='44020000' OR ACCTCODE='45020000' OR ACCTCODE='46020000' OR ACCTCODE='44010000' OR ACCTCODE='44020000' GROUP BY FATHERNUM

---------------------------------------VENTAS EN PESOS $$ LITROS----------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'AGUA' AS TIPO FROM OACT WHERE  ACCTCODE='41020000' OR ACCTCODE='41020000' OR ACCTCODE='420120000' OR ACCTCODE='42020000' OR ACCTCODE='43010000'

OR ACCTCODE='43020000' OR ACCTCODE='44010000' OR ACCTCODE='44020000' GROUP BY FATHERNUM

-------------------------------------- TOTAL EN COSTO DE PRODUCTOS  QUE NO SEAN DE ALMACEN1-------------------------------------------------

SELECT INV1.ITEMCODE, SUM(LINETOTAL) AS COSTOTOTAL  FROM OINV INNER JOIN INV1 ON OINV.DOCENTRY=INV1.DOCENTRY WHERE OINV.CARDCODE<>'C00009' AND CANCELED='N' AND DOCSTATUS='C' AND INV1.ITEMCODE LIKE '%%CW%%' OR INV1.ITEMCODE LIKE '%%DC%%'

GROUP BY INV1.ITEMCODE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TOTAL DE TONELADAS Y LITROS POR PRODUCTO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT INV1.ITEMCODE,INV1.DSCRIPTION, SUM(QUANTITY) AS UNIDADSXCAJA, CASE

WHEN INV1.ITEMCODE='CW0001' THEN (0)

WHEN INV1.ITEMCODE='CW0002' THEN (0)

WHEN INV1.ITEMCODE='CW0003' THEN (0)

WHEN INV1.ITEMCODE='CW0004' THEN (0)

WHEN INV1.ITEMCODE='CW0005' THEN ((SUM(QUANTITY))*3.96)

WHEN INV1.ITEMCODE='CW0006' THEN ((SUM(QUANTITY))*3.96)

WHEN INV1.ITEMCODE='CW0007' THEN ((SUM(QUANTITY))*1.08)

WHEN INV1.ITEMCODE='CW0008' THEN ((SUM(QUANTITY))*1.08)

WHEN INV1.ITEMCODE='CW0009' THEN ((SUM(QUANTITY))*1.08)

WHEN INV1.ITEMCODE='CW0010' THEN ((SUM(QUANTITY))*1.08)

WHEN INV1.ITEMCODE='CW0011' THEN ((SUM(QUANTITY))*6.48)

WHEN INV1.ITEMCODE='CW0012' THEN ((SUM(QUANTITY))*6.48)

WHEN INV1.ITEMCODE='CW0013' THEN ((SUM(QUANTITY))*6.48)

WHEN INV1.ITEMCODE='CW0014' THEN ((SUM(QUANTITY))*6.48)

WHEN INV1.ITEMCODE='CW0016' THEN ((SUM(QUANTITY))*2.16)

WHEN INV1.ITEMCODE='CW0017' THEN ((SUM(QUANTITY))*12)

WHEN INV1.ITEMCODE='CW0018' THEN ((SUM(QUANTITY))*12)

WHEN INV1.ITEMCODE='CW0019' THEN ((SUM(QUANTITY))*3.96)

WHEN INV1.ITEMCODE='DC0001' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0002' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0003' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0004' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0005' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0006' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0007' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0008' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0009' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0010' THEN ((SUM(QUANTITY))*25)

WHEN INV1.ITEMCODE='DC0011' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0012' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0013' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0014' THEN ((SUM(QUANTITY))*5.443)

WHEN INV1.ITEMCODE='DC0015' THEN ((SUM(QUANTITY))*13.61)

WHEN INV1.ITEMCODE='DC0016' THEN ((SUM(QUANTITY))*.227)

WHEN INV1.ITEMCODE='DC0017' THEN ((SUM(QUANTITY))*.454)

WHEN INV1.ITEMCODE='DC0018' THEN ((SUM(QUANTITY))*.227)

-- WHEN INV1.ITEMCODE='DC0019' THEN ((SUM(QUANTITY))*.433)

WHEN INV1.ITEMCODE='DC0020' THEN ((SUM(QUANTITY))*13.61)

WHEN INV1.ITEMCODE='DC0021' THEN ((SUM(QUANTITY))*13.61)

WHEN INV1.ITEMCODE='DC0022' THEN ((SUM(QUANTITY))*13.61)

WHEN INV1.ITEMCODE='DC0023' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0024' THEN ((SUM(QUANTITY))*.250)

-- WHEN INV1.ITEMCODE='DC025' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0026' THEN ((SUM(QUANTITY))*18.3)

--WHEN INV1.ITEMCODE='DC0027' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0028' THEN ((SUM(QUANTITY))*1)

-- WHEN INV1.ITEMCODE='DC0029' THEN ((SUM(QUANTITY))*1)

-- WHEN INV1.ITEMCODE='DC0030' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0031' THEN ((SUM(QUANTITY))*25)

WHEN INV1.ITEMCODE='DC0032' THEN ((SUM(QUANTITY))*5.443)

-- WHEN INV1.ITEMCODE='DC0033' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0034' THEN ((SUM(QUANTITY))*.227)

WHEN INV1.ITEMCODE='DC0035' THEN ((SUM(QUANTITY))*.433)

-- WHEN INV1.ITEMCODE='DC0036' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0037' THEN ((SUM(QUANTITY))*25)

WHEN INV1.ITEMCODE='DC0038' THEN ((SUM(QUANTITY))*.374)

WHEN INV1.ITEMCODE='DC0039' THEN ((SUM(QUANTITY))*25)

WHEN INV1.ITEMCODE='DC0040' THEN ((SUM(QUANTITY))*3.834)

WHEN INV1.ITEMCODE='DC0041' THEN ((SUM(QUANTITY))*.227)

WHEN INV1.ITEMCODE='DC0042' THEN ((SUM(QUANTITY))*.433)

WHEN INV1.ITEMCODE='DC0043' THEN ((SUM(QUANTITY))*12)

WHEN INV1.ITEMCODE='DC0044' THEN ((SUM(QUANTITY))*3)

WHEN INV1.ITEMCODE='DC0045' THEN ((SUM(QUANTITY))*1.588)

WHEN INV1.ITEMCODE='DC0046' THEN ((SUM(QUANTITY))*1)

WHEN INV1.ITEMCODE='DC0047' THEN ((SUM(QUANTITY))*.5)

WHEN INV1.ITEMCODE='DC0048' THEN ((SUM(QUANTITY))*6)

ELSE SUM(QUANTITY)

END 'UNIDADSTOTALES', CASE

WHEN INV1.ITEMCODE LIKE '%%CW%%' THEN 'LITROS'

WHEN INV1.ITEMCODE LIKE '%%DC%%' THEN 'KG'

ELSE 'OTROS'

END 'UNIDADES'

  FROM OINV INNER JOIN INV1 ON OINV.DOCENTRY=INV1.DOCENTRY WHERE OINV.CARDCODE<>'C00009' AND CANCELED='N' AND DOCSTATUS='C' AND INV1.ITEMCODE LIKE '%%CW%%' OR INV1.ITEMCODE LIKE '%%DC%%'

GROUP BY INV1.ITEMCODE,INV1.DSCRIPTION

---------------------NOMINA-----------------------------------------------------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'NOMINA' AS TIPO FROM OACT WHERE (ACCTCODE BETWEEN '52010100' AND '52011900') OR (ACCTCODE BETWEEN '52020100' AND '52021000')

OR ACCTCODE='52021600' OR ACCTCODE='52024000' OR ACCTCODE='52025400'  OR  (ACCTCODE BETWEEN '52030010' AND '52030160') OR ACCTCODE='52030180' OR ACCTCODE='52030190' OR ACCTCODE='52030200'

GROUP BY FATHERNUM

--------------------------GASTOS ADMINISTRATIVOS-------------------------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'GASTOS ADMINISTRATIVOS' AS TIPO FROM OACT WHERE ACCTCODE='52011700' OR ACCTCODE='52021900' OR ACCTCODE='52022000'

OR ACCTCODE='52022100' OR ACCTCODE='52022200' OR ACCTCODE='52022900' OR ACCTCODE='52023100' OR ACCTCODE='52024400' OR ACCTCODE='52024500'  OR ACCTCODE='52030170'

GROUP BY FATHERNUM

------------------------------------GASTOS FINANCIEROS---------------------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'GASTOS FINANCIEROS' AS TIPO FROM OACT WHERE ACCTCODE='52021700' OR ACCTCODE='52023000' OR ACCTCODE='52023200'

OR ACCTCODE='52023800' OR ACCTCODE='52023900' OR ACCTCODE='52024100' OR ACCTCODE='52024200' OR ACCTCODE='52024300' OR ACCTCODE='52024600'  OR ACCTCODE='52025300'  OR ACCTCODE='52025600' 

GROUP BY FATHERNUM

---------------------------------------GASTOS DE MANTENIMIENTO--------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'GASTOS DE MANTENIMIENTO' AS TIPO FROM OACT WHERE (ACCTCODE BETWEEN '52022300' AND '52022800')

GROUP BY FATHERNUM

-----------------------------------ENERGIA---------------------------------------------------------------------------------------------------------------------------------------

SELECT FATHERNUM, SUM (CURRTOTAL) AS TOTAL, 'ENERGIA' AS TIPO FROM OACT WHERE ACCTCODE='52021800'

ENERGIA

52021800

er2.PNG (15.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 17, 2015 at 05:37 PM

    Hola Coco.

    Te dejo un manual de Crystal para que le des una estudiada.

    https://www.dropbox.com/s/9nxpumf7q1i5woo/Working_with_CR_Integration_881_esCO.pdf?dl=0

    Prueba con los subinformes.

    Suerte.

    Add comment
    10|10000 characters needed characters exceeded