Data Retrieval

[keithm2008]

Background - rookie user; CR2008; btrieve data(Peachtree)

I have 3 tables: customer, jrnlhdr, & ticket; customer # is link to the tables.

I want to report cash receipts from jrnlhdr and billing total from ticket grouped by customer for a given period of time.

When I do this separately(customer & jrnlhdr and customer & ticket) I get the expected results. It's when I try to combine them the results are WAY off.

I'm sure this should be easier than I am making it but I am struggling mightily. Any help would be most appreciated.

keith

 

[Turkbear]

Hi,
Please explain further what you mean by:

It's when I try to combine them the results are WAY off.

Show some examples of good and 'way off' results.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

Crystal works by 'detail lines'. When you link two records, each possible combination becomes a detail line. So if a record on Table A is linked to two records on Table B, the Table A record will appear on two separate detail lines, and any count or sum may get double the result you expect. (Even worse with three tables, of course.)

The solution is to group, and base your count or sum on groups. There is also 'distinct count', which ignores duplicated values.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[keithm2008]

examples of good results...
summed by customer
from ticket table BillAmount = $6,100
from jrnlhdr table MainAmount = $3,150
these values are for cust_a and are correct

When I combine the two in a single report I get results well into the millions or I run out of memory depending on different approaches I might try.

I understand Madawc's point about possible combinations. As an old programmer I feel like my hands are tied because I know what I need to do but I don't understand how CR works so that I can take advantage of it's power. I need these calculations to take place separately and then get brought back together for reporting. What am I missing?

keith

 

[lbass]

You are getting inflated values because of the table joins. You might want to remove one of tables and instead add it in a subreport that is linked on the customer ID field. Place the sub in a main report group based on the customer ID. Then use shared variables if you need to use your sub calculation in a further calculation in the main report.

Another approach would be to use running totals that evaluate only periodically based on the repetition of data, e.g., evaluate on change of some repeated field.

-LB

 

[keithm2008]

I've tried subreports in the past and was hoping it would not be necessary this time. I was reluctant to go that route because I have date parms that I need to limit the data being retrieved. Can I pass those parms (date range, actually) to the subreport?

Thanks so much the assistance. I really appreciate it.

keith

 

[lbass]

I would change the date range parm to two parms {?start} and {?end}. Create the same parms in the sub and use them in the sub selection formula and then link the parms to each other by using the dropdown in the lower left of the subreport linking screen to select {?start} instead of the default (?pm-?start}.

-LB

 

[keithm2008]

LB - thanks for the advice. as it turns out there is an FAQ (faq149-1329) on this site that walks you thru the process and I just got it working correctly.

keith

 

[lbass]

Keith,

FYI--That is an old faq, and you can now link directly on parameters, as I suggested.

-LB