Skip to Content
0
Jul 30, 2020 at 02:41 AM

Text Type Rows in Crystal Reports

196 Views

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.

Attachments

crystalreport1.jpg (37.5 kB)
sapb1.jpg (25.9 kB)