Trouble getting a count of a field.

[bobpv]

I am running in circles with this one, perhaps someone can point me in a new direction.

It is a MSSQL data base which stores estimate data in a single table. Each estimate has a unique number as it's ID.

An estimate can be related to additional estimates by a field called MASTER, which is the number of the Master estimate used to create different "versions" of the estimate. So for example, estimate 20 can be the master to estimates 23,25,55 and 87. 5 estimates, 4 versions and 1 master.

What I am trying to do is summarize the estimates in a report, grouped by customer. The fields are summarized as so:

Customer (a group) - count of all estimates - # of different versions (where the Master Est field is not 0) - # won and % won. These are all cool. However, I cannot seem to pull out the last field, which would be the number of masters. In the above example, the customer shows 5 estimates, 4 versions, but also showing 4 masters, which should only be 1 master.

The field is numeric, and is 0 if no master estimate is found. Distinct count is not helping, nor can I get a running total to calculate correctly.

Anybody have an working solution here?

 

[lbass]

Try creating a formula like this:

if {table.master} = 0 then 1

Then insert a SUM (not count) on this formula at the group and/or grand total level. If this suggestion doesn't work, please show how the fields display if placed in the detail section, and label each field.

-LB