Skip to Content

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

Insert a query in the crystal report

Good afternoon,

I have this query, I want driving in crystal report, but i do not know how to handle the variables in the data source command.

DECLARE @FechaInicial DATETIME

DECLARE @FechaFinal DATETIME

DECLARE @RangoFech INT

SET @RangoFech=(SELECT TOP 1 T0.Transnum FROM OINM T0 WHERE T0.DocDate >='{?FechaInicial}' AND T0.DocDate <='{?FechaFinal}')

SET @FechaInicial=(SELECT '{?FechaInicial}')

SET @FechaFinal=(SELECT '{?FechaFinal}')

SELECT

Articulo=T1.ItemCode,

GrupArt=T1.ItmsGrpCod,

Producto = T1.Dscription,

StockInicial=ISNULL(COALESCE(T0.SaldoInic,0),0),

CostoInicial=(SELECT TOP 1 CostoInicial=ISNULL(COALESCE(S2.CalcPrice,0),0) FROM OINM S2 JOIN OITM A2 ON A2.ItemCode=S2.ItemCode

WHERE S2.DocDate<@FechaFinal AND A2.ItemCode=T1.ItemCode AND S2.CalcPrice <> 0 AND S2.Transnum = (SELECT MAX(S3.Transnum) FROM OINM S3 WHERE S3.DocDate<@FechaFinal AND S2.ItemCode=S3.ItemCode AND S3.CalcPrice <> 0)

GROUP BY A2.ItemCode, S2.CalcPrice) * ISNULL(COALESCE(T0.SaldoInic,0),0),

Entradas=ISNULL(COALESCE(T1.Entradas,0),0),

Salidas=ISNULL(COALESCE(T1.Consumo,0),0),

Stock =ISNULL(COALESCE(T0.SaldoInic,0)+COALESCE(T1.SaldoFin,0),0),

Costo = (SELECT TOP 1 Costo=ISNULL(COALESCE(S2.CalcPrice,0),0) FROM OINM S2 JOIN OITM A2 ON A2.ItemCode=S2.ItemCode

WHERE S2.DocDate<@FechaFinal AND A2.ItemCode=T1.ItemCode AND S2.CalcPrice <> 0 AND S2.Transnum = (SELECT MAX(S3.Transnum) FROM OINM S3 WHERE S3.DocDate<@FechaFinal AND S2.ItemCode=S3.ItemCode AND S3.CalcPrice <> 0)

GROUP BY A2.ItemCode, S2.CalcPrice) * ISNULL(COALESCE(T0.SaldoInic,0)+COALESCE(T1.SaldoFin,0),0)

FROM (

SELECT A1.ItemCode,A1.ItmsGrpCod,S1.Dscription,

SaldoInic=ISNULL(SUM(COALESCE(S1.Inqty,0))-SUM(COALESCE(S1.outqty,0)),0)

FROM OITM A1 JOIN OINM S1 ON A1.ItemCode=S1.ItemCode

WHERE S1.DocDate<@FechaInicial AND S1.Warehouse='{?Bodega}'

GROUP BY A1.ItemCode,A1.ItmsGrpCod,S1.Dscription

) AS T0 RIGHT JOIN (

SELECT A.ItemCode,A.ItmsGrpCod,S.Dscription,

Entradas=ISNULL(SUM(S.Inqty),0),

Consumo=ISNULL(SUM(S.Outqty),0),

SaldoFin=ISNULL(SUM(S.Inqty)-SUM(S.Outqty),0)

FROM OINM S JOIN OITM A ON A.ItemCode=S.ItemCode

WHERE S.DocDate BETWEEN @FechaInicial AND @FechaFinal AND S.Warehouse='{?Bodega}'

GROUP BY A.ItemCode,A.ItmsGrpCod,S.Dscription

) T1 ON T1.ItemCode=T0.ItemCode

Please, help me

Juan Camilo

Former Member
Not what you were looking for? View more on this topic or Ask a question