Summarize Distinct Values 1

[calvinsmith]

Is there a way to summarize all distinct values for a given column in my data? What I need to do is create a field that will give the total number of 'A's in a column. The column will be made up of 'A's, 'B's, and 'C's. Eventually I will need to add more extensive criteria for the selection of the 'A's but for now I am stuck on the summarization. Any help with this would be appreciated. Thanks!

 

[vidru]

You can create a formula that evaluates the field, and returns either a 1 or 0. Then create a summary on it like you would with any other numeric field:
[tt]
//@A_Count
//Drop this into your Details section, and suppress it.
//Then create a summary of this formula to get the Count of A's
If {Table.Field} = "A" Then
1
Else
0;
[/tt]
-dave

 

[synapsevampire]

Another option is to use a Running Total and in the evaluate->use a formula place:

{Table.Field} = "A"

This is a bit simpler than the manual means, but the values are only available at the end of the RTs respective group or if you have no groups, at the end of the report.

It always makes me nervous when someone says they need more complex criteria as it might completely change everything, so it's probably best to state the real requirements up front.

-k

 

[stormtrooper]

Chiming in with another related question here. How would you go about it if your values in the field were undetermined?

eg.

{table.field} =
12
12
12
16
16
88
88
4

And from this sum the distinct values.(this field would be in the details by the way)

eg. 12 + 16 + 88 + 4 = 120

 

[stormtrooper]

Any ideas?

 

[lbass]

stormtrooper--are you asking this of calvinsmith for clarification, or are you asking this to get an answer for your own problem?

If the latter, sort by the field and then use a running total and choose "evaluate on change of field" and choose the field. There is a danger here, in that ordinarily you would want some other field to identify what might be a duplicate value versus a legitimate value that is the same as the previous one, but which should be added into the sum.

-LB