Running total problems....

[GIScrew]

Report Description:
My report consists of three tables. The tables are linked by a Left Outer join with an = Link Type. The report has 3 groups; State, County, Tract. There are 3 running totals; RT1 = DistinctCount(Eval on formula, reset based on group), RT2 = Sum(Eval on formula, reset based on group), RT3 = Distinct.Count(Eval on formula, reset based on group).

Report PROBELM:
Everytime RT3 is > 1, RT2 is multiplied by RT3. RT1 creates a perfect count but RT2 is always distorted. The pattern is easily identifiable when RT3 is > 1. I have double checked my database and run out of ideas. I have tried sorting by different fields but nothing seems to work. Anyone have any ideas? This problem is so simple, I know its stairing me in the face, I just need a fresh look.

Thanks,
Grant

 

[synapsevampire]

It sounds like a record inflation issue in that you have the joins set up to return more than one row where you are expecting only one.

Are you getting unexpected rows?

The obvious difference is that you have a DISTINCT on the counts.

You can use the 3 formula method:

Group header:
whileprintingrecords;
numbervar MySum:=0

Details:
Group header:
whileprintingrecords;
numbervar MySum;
If onfirstrecord
or
previous(table.field) <> table.field then
MySum:=MySum+{table.value}

Group Footer:
whileprintingrecords;
numbervar MySum

Note that you check for duplication using {table.field}, and then you can sum if it's unique.

-k

 

[GIScrew]

No, I am not getting any unexpected rows. I'm not sure I can integrate your solution into my report. I agree with the record inflation issue. Could changing the table joining possibly solve the problem?

Thanks for the Insight,
Grant

 

[synapsevampire]

Why can't you integrate the 3 formula solution?

If you have to use a distinct and the sum is wrong, it sounds like you're getting unwanted rows.

-k

 

[GIScrew]

Ok, you were right, I am getting unexpected rows. I am hammering away at it right now. I'll try to post again in a minute.

Thanks a Ton,
Grant