Link to home
Start Free TrialLog in
Avatar of kevin1983
kevin1983Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Crystal report to show lines that were on sales order but not on corresponding delivery.

Hi,

Currently trying to build a crystal report against a SAP Business one database that will show the deliveries on a given date. We need the report to show lines that were on the sales order but were not on the delivery. The report is grouped by delivery number.

The only fields we need from the delivery table are the delivery number (ODLN.DocNum) and the delivery Date (ODLN.DocDate).


The problem we have run into is that the only way we can find to link from the sales order to the delivery table is on the line level.
Here is the SQL command used by the report

--T1 is Sales Order Table
--T2 is sales order line level table
--t4 is the delivery table

SELECT t1.docnum, 
       t2.itemcode,  
       t2.linenum, 
       t2.[delivrdqty], 
       t2.[quantity] AS 'RDRQty', 
       t2.[price], 
       t2.[orderedqty], 
       t4.docnum AS 'DeliveryNum'
FROM   dbo.ordr t1 
       INNER JOIN rdr1 t2 
               ON t1.docentry = t2.docentry
       LEFT JOIN odln t4 
              ON t2.trgetentry = t4.docnum 
       

Open in new window


If the Line was not on the sales order then the TrgetEntry field will be null preventing us from linking to the correct delivery. It is possible for there to be multiple deliveries for a single sales order so we can't really use the first non null TrgetEntry as this could potentially link to the wrong delivery.

The attached spreadsheet is the output of the query and shows the problem we are having. Line number 14 was on the sales order but not on the delivery and as a result it has a null delivery number meaning that it will be grouped incorrectly on the report.

Would really appreciate some help as I am a bit stuck here.
Delivery.xlsx
Avatar of Mike McCracken
Mike McCracken

How are you grouping?

Seems you would group by docnum then by deliverynum

mlmcc
Avatar of kevin1983

ASKER

I've tried grouping as you suggested below, but doesn't make any difference to the results
There's a field on the sales order at line level that shows if an item has been delivered  or not this field is: t2.[delivrdqty] (Shows Null when not delivered as per line 14) but this only seems to help so much.


SELECT t1.docnum,
       t2.itemcode,  
       t2.linenum,
       t2.[delivrdqty],
       t2.[quantity] AS 'RDRQty',
       t2.[price],
       t2.[orderedqty],
       t4.docnum AS 'DeliveryNum'
FROM   dbo.ordr t1
       INNER JOIN rdr1 t2
               ON t1.docentry = t2.docentry
       LEFT JOIN odln t4
              ON t2.trgetentry = t4.docnum
             
             
Group by t1.docnum, t4.docnum, t2.itemcode,  
       t2.linenum,
       t2.[delivrdqty],
       t2.[quantity],
       t2.[price],
       t2.[orderedqty]
Not a group by in the SQL but grouping in the report itself.

mlmcc
ah ok, in the crystal report at the moment it's grouped on delivery number and most details  are within the delivery number footer group section
That is what I suspected.  Grouping that way there is no way to get the "missing" lines to appear with the group.  That is why I suggested using the DOCNUM as a first group.

mlmcc
No this is not the exact issue, we can get the original sales lines from the sales order to appear in the report but we don't know how to relate the correct delivery data to the corresponding sales lines.

The report needs to run each day, and only show delivery for that day the report is run with the corresponding lines from the related sales order that have not been delivered.

Sometimes one sales order may have multiple deliveries on different days, but the report only needs to relate to deliveries done on that day.
So you don't want the items with a null delivery to show?
Change the JOIN to INNER

If that isn't what you want, what do you want done with those lines?

mlmcc
What we want is a single report where the non delivered lines still appear in the delivery that has been created from the sales order but we can highlight them so that we know they haven't been delivered.

The problem is that we can't associate the non delivered lines with the correct delivery as the field that we join to the delivery table on (trgetEntry) will always be null.

The result is that we end up with a report with the non delivered lines in a group with a null delivery number rather than the delivery that they would have been included in.

In the spreadsheet that I attached line 14 was never delivered and has a null TrgetEntry, what we need is for it to somehow be grouped into delivery 4233 and flagged so that we know that it was not actually delivered.

I have attached another spreadsheet with some sample data from the order and delivery tables.
sample-data.xlsx
It can't be grouped with the delivery since it is null.  That is why I suggested grouping by the document.

Can a document have multiple deliveries?
If so which would you include the missed items in?

I don't really understand what the columns are in the 2nd example

mlmcc
Yes a document can have multiple deliveries, from looking at the table structure I am not sure if there is any way we can get this working consistently.

I guess that it is simply not possible to know what delivery a missed item would be in.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is what I ended up doing in the end, thanks for the help.