Text Type Rows in Crystal Reports

[lsauser]

Hi all,

I have done a ton of research on this one and looked at a bunch of other threads with similar issue with no clear fix. I need my records to show in the exact same order in crystal reports as they do in the SAP b1 document and include text lines.

Some solutions involve the TmSP_DocLineTypeLayout;1 stored procedure and i had a look at a system crystal report but haven't been able to put 2 and 2 together and figure out how its done.

As you can see from the crystal report view, the text line displays after the first record however;

The order and text row comes last here.

Ideally this will work with text rows anywhere in this order of the document.

Here is my query in Crystal Reports;

SELECT DISTINCT 
T0.[CardName], 
T0.[DocNum], 
T0.[DocDate], 
T0.[Address], 
T0.[Address2], 
T2.[SlpName], 
T2.[Mobil], 
T2.[Email],
T0.DocEntry,
T1.ItemCode,
T1.Dscription,
T1.Quantity,
'\\LSA-DB01\Pictures\'+convert(varchar,T3.Picturname),
T1.Price,
T4.Tel1,
T4.Cellolar,
T4.E_MailL,
T4.FirstName,
T4.LastName,
T5.PymntGroup,
CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS 'Deposit',
CAST(T6."LineText" AS varchar(MAX)) AS LineText,
T0.[U_LSA_Period],
T7.ItmsGrpNam,
CAST(T3.UserText AS varchar(MAX)) AS ItemInfo,
T0.Series,
T1.FreeTxt
FROM OQUT T0 
INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]
INNER JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode]
INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum]
INNER JOIN QUT10 T6 ON T0.[DocEntry] = T6.[DocEntry]
INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod

I tried having QUT10 as a LEFT JOIN with AND T6.AftLineNum = T1.LineNum included and T1.DocEntry instead of T0 however that just produced no results.

I understand why this isnt a straight forward solution like it is in the PLD, but i still cant figure out how to fix it, have been at it for a while now.

As always, any help is greatly appreciated.

[lsauser · Accepted solution]

is anyone else able to help me with this one? It's the last little hurdle I need to overcome for this.

Regards,

Nick

EDIT: Solved it, did some comparisons with another code i found elsewhere and realised i needed a 0 instead of '' for some of these unioned fields, so simple.

[lsauser]

Thanks for your comment msundararaja.perumal ,

I believe I have been able to get most of the query working myself, but I am stuck at one part that I cant quite work out. The error I am getting now is "Conversion failed when converting the nvarchar value 'IPSO Parts' to data type smallint."

I tried CAST(T7.ItmsGrpNam AS smallint) AS 'Items Group' but it just changed 'IPSO Parts' to 'ADC Parts'. I cant seem to get over this little hurdle. I hope everything else i've done is right.

Code is below;

SELECT DISTINCT 
T0.[CardName], 
T0.[DocNum], 
T0.[DocDate], 
T0.[Address], 
T0.[Address2], 
T2.[SlpName], 
T2.[Mobil], 
T2.[Email],
T0.DocEntry,
T1.ItemCode,
T1.Dscription,
T1.Quantity,
T1.Price,
T4.Tel1,
'\\LSA-DB01\Pictures\'+convert(varchar,T3.Picturname),
T4.Cellolar,
T4.E_MailL,
T4.FirstName,
T4.LastName,
T5.PymntGroup,
CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS 'Deposit',
T0.[U_LSA_Period],
 T7.ItmsGrpNam,
CAST(T3.UserText AS varchar(MAX)) AS ItemInfo,
T0.Series,
T1.FreeTxt,
T1.VisOrder
FROM OQUT T0 
INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] AND T1.LineType = 'R' 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]
INNER JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode]
INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum]
INNER JOIN QUT10 T6 ON T0.[DocEntry] = T6.[DocEntry]
INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod
UNION ALL
SELECT DISTINCT 
T0.[CardName], 
T0.[DocNum], 
T0.[DocDate], 
T0.[Address], 
T0.[Address2], 
T2.[SlpName], 
T2.[Mobil], 
T2.[Email],
T0.DocEntry,
'',
'',
'',
'',
T4.Tel1,
'',
T4.Cellolar,
T4.E_MailL,
T4.FirstName,
T4.LastName,
T5.PymntGroup,
CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS 'Deposit',
CAST(T1."LineText" AS varchar(MAX)) AS LineText,
T0.[U_LSA_Period],
'',
'',
T0.Series,
T1.AftLineNum
FROM OQUT T0 
INNER JOIN QUT10 T1 ON T0.[DocEntry] = T1.[DocEntry] AND T1.LineType = 'T' 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode]
INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum]
ORDER BY T0.[DocDate], T0.[DocNum], T1.[VisOrder]

[msundararaja_perumal]

Hello,

You can either use TmSP_DocLineTypeLayout stored procedure but it may slow down after few years when you preview the layout.

Otherwise you can create a custom query or stored procedure to achieve your goal.

You cannot create the format in a single query, you have to extract the details of the Items Row(QUT1) and Text Rows(QUT10) seperately then combine according to the QUT1.VisOrder to bring in sequence.If it is too complicated then I suggest you to use TmSP_DocLineTypeLayout in the crystal report, you need to Join

OQUT.DocEntry with TmSP_DocLineTypeLayout.DocEntry, use inner join

And use outer join for the below

TmSP_DocLineTypeLayout.DocEntry with QUT1.DocEntry &

TmSP_DocLineTypeLayout.LineNum with QUT1.VisOrder

Then start designing the crystal report.

Thanks.