Group Selection Formula Help 1

[kovas]

I have duplicate names in my report so to get rid of them, i grouped by Name, and then put a formula that looks like that in the details

if not isnull({people.ssn}) then
1
else
0

then in the group footer i sum up the formula and in group selection formula i put this

Count ({@SSN}, {@Group}) = 1

Now this gets rids of all the duplicates and the report looks great, but it also gets rid of all the customers that had duplicates. But i need one copy of it to show on the report. Is it somehow possible for the Group Selection to Select only those groups where the Sum of the formula was Less then 2?

Count ({@SSN}, {@Group}) < 2

doesnt seem to work?

Hope this makes sense, thanks!

 

[lbass]

If you are just trying to achieve one row per customer, you could create a formula {@uniquename} which concatenates the name and the SSN:

{name}+{SSN}

Group on this, and then, if you wish, remove the group name field from the group header and substitute {name}. This way each John Smith with a different SSN will appear once, while duplicates are eliminated from the display.

Another way of handling this without grouping (depending on your requirements) is to format section->detail->suppress->E-2: {@uniquename} = previous({@uniquename})

-LB

 

[kovas]

thanks lbass, i already had something similar as to what you suggested.

But when you use this method. How can you get an accurate count of how many records you are displaying? I tried using Summary/Count but it counts the suppressed records.

 

[lbass]

If you are trying to count non-duplicated names, then use distinctcount({@uniquename}).

If you are trying to summarize another field or fields, e.g., {sales}, that you want to evaluate for each person, then use a running total. Using the running total wizard, you would choose {sales}, sum for the summary, evaluate on change of {@uniquename}, reset never or on change of field or group, depending on your needs.

In general, if rows or fields are suppressed, running totals will allow you to summarize based on displayed fields, since you can build in the conditions that allow you to ignore suppressed fields.

-LB