cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Report for Sales, Delivery and Payment.

Former Member
0 Kudos

Hi Guys,

Need some expert advice over here. Currently, our sales team are using 3 separate report, one report on sales per month for each specific customers, one report on delivered amount per month for specific customers again and the last one report on payment received per month for specific customers as well.

We have the idea of joining this 3 report so that I can report on sales, delivery and payment all at once so that they can see the trend better. However, after trying for quite a few times, we fail to create a common joining between ORDR, ODLN and ORCT. Is there any possible way to work something out ?

I have thought of using subreport, but I just cant find the way to plug in the figure correctly.

Need some advice over there.

Thank you.

Best Regards,

Alex

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Alex,

The way I understand it you are using 3 separate reports. Each report takes Customer CardCode I assume as the parameter and you want to combine them together in one report.

The best way to do them is to create 3 subreports and insert them all in one report. You already have the 3 reports you need for subreports. Here are the steps

1) Create a new report with Customer Code as the parameter

2) Then in Crystal Reports go to the menu INSERT >> SubReport

3) On the tab "Subreport", select "Choose existing Report" and browse to your Sales Order report file.

4) Then goto the "Links".

5) On the left side, Select Customer Code parameter created in step 1 and move that field to the right.

6) On the bottom left, select the Customer Code Parameter from the subreport

7) On the bottom right, select either OCRD.CardCode or ORDR.CardCode

Repeat these steps for Delivery and Payments report.

This way you can add all these 3 reports as subreports in a single report.

Hope this helps

Krishnan

Former Member
0 Kudos

Hi Krishnan,

Thank you for your advice. I have already tried this method a few time previously, but the joining wouldnt be as accurate as what I want. The problem is as below description.

All 3 reports - Sales, Delivery and Incoming Payment use CustomerName parameter and yes, I can link all 3 report using CustomerName parameter linking. However, all the figures within this 3 report were also grouped into specific month and I would supress the details and sum the figure up as a monthly total figure only. Hence, by using subreport, I have to link 2 parameters - CustomerName and Month.

For some customers, there could be sales for June 2010 but we would only deliver during July 2010 and payment will only come in during August. However, because of linking Month together, the report only return figures for month where all 3 subreports have a specific figure. Lets say for June I have sales but I dont have delivery, that newly joined report wouldnt return any figure for June at all. I need a way to solve this, else data obtained wouldnt be accurate.

My apology if I create more confusion as I don't know how to describe the situation better in words.

Please advice.

Thank you.

Best Regards,

Alex

Former Member
0 Kudos

Based on what you have told me, then you should then join Sales Order, Delivery, Invoice and Incoming Payments into one.

The reason I say Invoice table also is because, you cannot directly link Delivery and Incoming Payments window.

Also you it is not enough that you link the ORDR, ODLN and ORCT, you will need to get the details from RDR1 and DLN1.

In SAP Business One, each line in SAP Business One can have a different Delivery documnet.

The TrgetEntry, TargetType will give you this information. For example, if a line for Sales Order has TargetType 15, then TrgetEntry will be the DocEntry in the ODLN. But if it TargetType is 13 it means the Sales order was converted directly to and Invoice and bypassed the creation of Delivery step, which is possible in SAP.

So here is how I think you will need to link Sales order and Delivery

RDR1.TrgetEntry INNER JOIN DLN1.DocEntry AND

RDR1.TargetType INNER JOIN DLN1.ObjType AND

RDR1.LineNum INNER JOIN DLN1.BaseLine

Now this could get a little more complicated if you do partial deliveries. For example if a line in your Sales Order has say 10 items and 6 of them are delivered using Delivery Note 123 and the remaining are delivered using Delivery Note 124, then the join will give you 2 different lines.

So basically you will need to link the tables in this fashion

ORDR <-> RDR1 <-> DLN1 <-> INV1 <-> RCT2 <-> ORCT

Unfortunately, this linking process can get complicated if you do not follow certain steps, like for example if you do not create a Delivery Note and converted your Sales order to Invoice. Then the link would be

ORDR <-> RDR1 <-> INV1 <-> RCT2 <-> ORCT

I hope I didn't confuse you.

Krishnan

Former Member
0 Kudos

Hi Krishnan,

Yes, I know there is a need to link. But as I have mentioned earlier, I have 3 separate reports. One report on sales (ORDR), the second one report on delivery (ODLN) and third report on ORCT. I am interested in the document total only, not all the details. Hence, I opt not to join each document because of the nature of our business that could go from Sales to Invoice or at time, Sales - Delivery -Invoice. Your linking over there refer to usual linking within a report or for subreport ?

I only have one problem here now, is that whenever I join these 3 report together via subreport function, I have problem getting all the period. This is due to what I have mentioned earlier, for certain customers, there might be no sales for certain month but we still receive backdated payment during that period. But when we join them up using subreport, if data is not present for all module but only sales/payment/delivery, then the data for that period will not surface.

Need some advice over here.

Thank you.

Edited by: AlexAkane on Aug 7, 2010 4:36 AM

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

In order to join payment in your report, invoices have to be jointed too.

Thanks,

Gordon

Former Member
0 Kudos

Hi Alex,

Based on your description, i assume that you want to a report that display information about Sales Order, Delivery and Incoming Payment from Specific customer. if it's true then I think you must use UNION ALL in your query.

For Example :

Select 'Sales Order' As DocType, CardCode, CardName, DocDate, DocEntry

From ORDR

Where DocDate Between @StartDate And @EndDate

UNION ALL

Select 'Delivery' As DocType, CardCode, CardName, DocDate, DocEntry

From ODLN

Where DocDate Between @StartDate And @EndDate

UNION ALL

Select 'Inc. Payment' As DocType, CardCode, CardName, DocDate, DocEntry

From ORCT

Where DocDate Between @StartDate And @EndDate

Then in your crystal report just use group based on customer.

I hope this can solve your problem.

Regards,

WhiteWood