Delivery Report or A/r Report in Crystal Report

[former_member264311]

Good Day Experts,

How can I apply codes below to Crystal report?

Thanks

DECLARE @listCol VARCHAR(8000)
DECLARE @Query VARCHAR(8000)
DECLARE @ODLN VARCHAR(8000)
DECLARE @ORDN VARCHAR(8000)
DECLARE @From varchar(15)
DECLARE @To varchar(15)
Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

SET @ODLN = isnull( STUFF((Select ',' + convert(varchar(10),Docentry) from ODLN
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SET @ORDN = isnull(STUFF((Select ',' + convert(varchar(10),Docentry) from ORDN
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((t0.CardName)) from ODLN T0 Inner join DLN1 T1 on T0.DocEntry = T1.DocEntry
where t0.DocType='I' ORDER BY '],[' + ltrim((t0.CardName)) FOR XML PATH('') ), 1, 2, '') + ']'

SET @Query = 'Select * from (Select T1.Itemcode,T0.CardName, T4.ItmsGrpNam, sum(T1.Quantity) as Sal from
ODLN T0 Inner join DLN1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@ODLN+')
group by T1.Itemcode, T0.CardName,T4.ItmsGrpNam

union all

Select T1.Itemcode,T0.CardName,T4.ItmsGrpNam,sum(-T1.Quantity) as Sal from
ORDN T0 Inner join RDN1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@ORDN+')
group by T1.Itemcode, T0.CardName,T4.ItmsGrpNam

) src
PIVOT (sum(Sal) for CardName IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)

[Former Member · Accepted solution]

You can copy query directly into a command on Crystal reports.

In the Command dialog box you can create crystal report parameters as required to provide data for your existing parameters enabling users to customise report if required.

eg Set @From = {?CrystalParam}

Ian

[former_member264311 · Accepted solution]

Hello Ian,

Can you help me to my code? Sorry Im newbie in SQL.

Regards,

thanks.

[Former Member · Accepted solution]

Not really as I do not know your database.

You are someone familiar with your DB needs to correct this

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

When that is done you should be able to paste SQL in to a Crystal command

Ian

[Former Member]

Hi Romel C,

you have to generate those query in SP first, after that you able make report in CR.

Thx.

[former_member264311]

Hi Harry P.,

Thank you for the reply. How can I generate this code in SP? Can you teach me the steps?

Thanks.