Getting the correct data from two similar tables 1

[TGHaddon]

My company has recently moved to a new set of computer systems and is using Crystal Reports 8.5 to generate reports.

I am trying to build reports that show sales by Rep, Customer, Product, Supplier etc. I have managed to build basic reports to do this but have another problem.

The computer system keeps records of sales and credits in two separate tables. AVDT3 (Credits) and AVDT5 (Sales), I want to create a report that gives me a net value of sale, at the moment I have to use two reports, one showing the sales and one showing credits which have to be manually reconciled.

Hopefully someone can help.

I have tried linking both tables in the same report e.g.

Customer - AVDT5 - AVDT3

Customer - AVDT5
- AVDT3

And with various types of link, however I either get error messages or the report generates accurate sales data but duplicates credit information

i.e.

Customer AVDT5 AVDT3
Record1 Record1
Record2 Record1
Record3 Record1
Record4 Record1

I also thought that using sub-reports might be the solution but can't figure them out at all.

I don't know how easy it is to understand any of this, but any possible help would be appreciated.

 

[lbass]

If we assume that you want to report only on customers who have sales, and that only customers who have sales will have credits, then you could create an equal join from customer to sales and a left join from sales to credit.

You will get repeating values for the credit column and if there were more than one credit per customer, you would get repeating values for sales as well. The solution for your example data above would be to use a running total for sales (sum), evaluate for every record, reset on change of {AVDT3.ID}, and a running total for credit (sum) which is evaluated on change of {AVDT3.ID} and resets on change of group (customerID).

Another approach would be to use only the customer and sales tables in the main report, and in the subreport use the customer table with a left join to the credit table. In the main report, you would group on customer ID, and the subreport would be linked to the main report on customer ID and placed in the group footer. Group on customer ID in the subreport, and then create a formula within the subreport:

{@creditamt} to be placed in the group footer:
whileprintingrecords;
shared numbervar creditamt := sum({AVDT3.amount},{Customer.CustomerID}

Then suppress all subreport sections but the group footer, removing all other fields but the formula from the footer.

In the main report, insert a second group footer section, and create a formula {@netamt}:

whileprintingrecords;
shared numbervar creditamt;
sum({AVDT5.sales},{AVDT5.customerID}) + creditamt;

Place this formula in the second group footer section, and if you want it to appear as though it is in the first group footer section, go to format section->group footer_a and check "Underlay following sections."

-LB