CODEWITHSUNDEEP
sqlcrystal-reportssapb1
Nick Jones
Nick Jones

Reputation: 93

Crystal Reports not showing SAP b1 query

I have the following query;

SELECT TOP 1
T0.custmrName, 
T0.callID,
T0.BpShipAddr, 
T1.Name, 
T0.createDate,
T0.subject,
T0.manufSN, 
T0.internalSN,
T0.itemCode,
T0.itemName, 
CAST(T0."resolution" AS varchar(MAX)),
T5.DocNum,
T7.DocNum AS 'OrderNum'
FROM OSCL T0
LEFT JOIN OSCT T1 ON T0.callType = T1.callTypeID
LEFT JOIN OHEM T2 ON T0.technician = T2.empID
LEFT JOIN SCL4 T3 ON T3.SrcvCallID = T0.callID
LEFT JOIN QUT1 T4 ON T3."Object" = T4."ObjType" AND T3."DocAbs" = T4."DocEntry"
LEFT JOIN OQUT T5 ON T5.DocEntry = T4.DocEntry
LEFT JOIN RDR1 T6 ON T3.Object = T6.ObjType AND T3.DocAbs = T6.DocEntry
LEFT JOIN ORDR T7 ON T7.DocEntry = T6.DocEntry
WHERE T0.callID = 8235
ORDER BY T5.DocNum DESC, T7.DocNum DESC, T3.Object DESC

In SAP b1 this works perfectly fine, only gives me the 1 record relating to the service call I am after. If it has a linked quotation, gives me the info with the quote number, if only a linked sales order then all the info with the S/O number. If both exist then gives me the one record with the quotation number and no sales order number (which is fine).

Now the problem lies in getting this to show in Crystal Report. None of the fields are showing up in here (only the labels) and i cant seem to work out why. All my joins are left joins and it works perfectly fine in SAP B1.

It seems to only happen like this in CR if its TOP 1, DISTINCT works but that will give me multiple records.

If anyone can shed some light on why this might be that would be super helpful.

(note the where condition for the call id is used as an example, this is controlled by a parameter in Crystal Reports)

Thank you.

Upvotes: 1

Views: 886

Answers (1)

Nick Jones
Nick Jones

Reputation: 93

I managed to fix this issue by removing the parameter from Crystal Reports and placing it directly within the SQL Query.

Upvotes: 0

Related Questions