Distinct Summary

[samilam]

I have created a field from two others, {name}&{location}. I want to get a summary on a seperate field in the same table, but a distinct sum based on the new field, {name}&{location}. I created a group based on my {name}&{location} field, and moved my {amount} field into the group header, but when I insert a summary, I get a total based on each record, not the distinct sum I am looking for. Any ideas how I can do this?

I am on CRXi R2, on a SQL db on an ODBC connection.

Any help would be great!

Sam

 

[hilfy]

The easiest way I can think of involves a formula and a summary.

The formula gets the amount you're going to sum:

if ({table.name} <> previous({table.name})) or
({table.location} <> previous({table.location)) then
{table.amount}
else 0

This only includes the amount field on the first record for the name + location combination.

You then use a summary to sum this formula to get the figure you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

Try inserting a running total. First you should either group or sort by your new formula ({@yourformula}), and then insert a running total that summarizes the desired field, evaluates on change of group (or field, if your sorted): {@yourformula}, reset never. Place the result in the report footer.

-LB