Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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 (15318 B)
Tags:
Former Member
replied

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.

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question