Distinct Average in Crystal Report?

[kunzi]

I have some duplicate records in my record due to the complicated table structures, and checking "Selecting Distinct Records" would only filter part of them. So now my report for one user is like this:

Record Is Valid?
a Yes
a No
b Yes
c Yes
........

If one record is marked as "Valid" in any row, it is valid, despite that it may be marked as Not Valid in other rows. So in the example above, all records a, b and c are valid.

My questions is:

There're many users and I need to sort upon the percentage of their valid records. How to use summary to calculate the percenatge of valid records in the report? Simple "Average" would return 75% in this above example but the actual percent is 100%.

I created a formula to get the correct percent but Crystal refused to sort on it:

Sum ({@ValidRecord}, {%User})/DistinctCount ({RecordID}, {%User})

({@ValidRecord} returns 1 if the record is valid in this row.)

Any suggestions to make either way work?

Thanks!

 

[lbass]

You would have to recreate your percentage formula as a SQL expression or as a formula that references SQL expressions so that you could place it in the detail section and insert a summary, e.g., maximum, on it at the User group level, even when the expression is already returning the correct percentage. Inserting a summary is what activates the group sort feature.

-LB