Skip to Content
author's profile photo Nick Jones
0
Nick Jones

Text Type Rows in Crystal Reports

Posted on Jul 30, 2020 at 02:41 AM 76 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.

crystalreport1.jpg (37.5 kB)
sapb1.jpg (25.9 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Nick Jones
    Nick Jones
    Posted on Aug 02, 2020 at 11:21 PM
    0

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo M. Sundararaja Perumal
    M. Sundararaja Perumal
    Posted on Jul 30, 2020 at 04:02 AM
    0

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Nick Jones
    Nick Jones
    Posted on Jul 31, 2020 at 12:56 AM
    0

    Thanks for your comment M. Sundararaja 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]
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.