Skip to Content
0

Delivery Report or A/r Report in Crystal Report

Mar 14, 2017 at 12:00 AM

76

avatar image
Former Member

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Ian Waterman Mar 14, 2017 at 09:34 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Ian,

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

Regards,

thanks.

0

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

0
avatar image
Former Member Mar 14, 2017 at 02:48 AM
0

Hi Romel C,

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

Thx.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Harry P.,

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

Thanks.

0