linking a subreport 1

[DJWheezyWeez]

I'm using CR XI.

I'm trying to get a working Sales Journal and I'm very close but having trouble with the linking. In one report I have sales. In another report I have cost of sales. I need them both in one report so I suppressed the cost of sales report down to one field for a subreport. When I import it into the sales report, I link them by customer but I don't end up with the same cost of sales totals.

In the sales report, I have 6 tables: Item, AnalysisCode, Bill, BillLine, CustAnalysisCode, and Customer. In the cost of sales report, I have 3 tables: Item, Customer, and InvHistory. I linked the reports by {Customer.CustCode} since that's what I'm grouping by first in both reports and it made sense but it didn't work. I've been just guessing with different links now and nothing seems to be working.

Should I be linking between the same tables since both report share two of the same tables or should I be linking different tables? Or is there some special knowledge of an easy rule for linking subreports? I'm very lost at the moment. Any help is appeciated.

-DJWW

 

[lbass]

Please explain the group structure in the main report and also tell us in what section you have placed the subreport. Is the total you are showing in the subreport the total for the customer regardless of item? Or?

-LB

 

[DJWheezyWeez]

The main report is grouped by CustName, ItemCode, and BillNum in that order. I placed the subreport in GF1. The subreport is all suppressed down to only show the cost of sales sum which is also in GF1 in the subreport. And I don't quite understand your last question but I think it would matter if the item wasn't there. It can be out of the report but the table still needs to be there to link within the subreport. I hope that answers your questions.

-DJWW

 

[lbass]

I was asking whether the subreport is showing the cost across all items for the customer or whether you want it to show the cost per item per customer.

If you are looking for totals per customer then your linking is correct, so now you need to explain in what way the results you are seeing are incorrect. Are they inflated? Are they repeating?

-LB

 

[lbass]

Also, are you using the same selection criteria in both main and subreport?

-LB

 

[DJWheezyWeez]

Well it should be one cost per item then I sum them to get the total I want. I checked on the selection criteria and yes they're the roughly the same. The main report has a few more just to restrict down to one customer so I don't have to run the whole report for all customers every time I make a change. But I also have another subreport in the man report with 0 restrictions on it and I'm still getting the same total as the other subreport. I did some investigating and it seems the main report total is $815 less than the subreports' totals which happens to be one of the prices on one of the items. It does sum correctly in the cost of sales report but not as a subreport though. I briefly looked at the two reports by themselves trying to see if anything odd sticks out to me but nothing seems wierd about that item compared to the others. I'll keep looking around though.

-DJWW

 

[DJWheezyWeez]

I couldn't find any reason why the totals weren't equaling but instead of taking the cost of sales report and putting that into the sales report, I tried it the other way around and put the sales report into the cost of sales report. I've only checked it on a few customers since it takes a while to run but it seems to be working.

Not that it matters too much now that it's working but does anyone have any insight into why this might be?

-DJWW

 

[lbass]

A subreport behaves as if it is a table that is left joined to the main report--if it is linked, that is. The report that has the more comprehensive set of records should be used as the container record.

-LB