How do I do a count using a sum?

From SmartWiki
Revision as of 10:40, 26 April 2013 by Arthur Lathrop (talk | contribs) (Examples)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Q: How do I do a count using a sum?

A: if([this]= 'Counting Criteria' ,1,0)

Why do we do this?

When using the Criteria line in the Report Builder, one line of criteria is treated as a single AND statement. By using this method we can move the criteria up to the Calculate Field area and allow for multiple OR criteria using a singular AND statement in the Criteria line.

Examples

Counting Records where Current User is the Owner

ReportBuilderExample1.PNG
This calculation will assign a value of "1" to each record where the @userid@ matches the Owner ID (in other words, all records where the user currently logged in is the Owner of the record), and a value of "0" to all other records, and then sum up the list, returning a total of records for which the current user is the Owner.

Counting Records where End Date is After a Given Date

ReportBuilderExample2.PNG
This calculation will assign a value of "1" to each record where the End Date is later than April 1, 2013, and a value of "0" to all other records, and then sum up the list, returning a total of all records for which the End Date is later than April 1, 2013.

Counting Records where Text Field contains a Given Text String

ReportBuilderExample3.PNG
This calculation will assign a value of "1" to each record where the Description field contains the word "laptop," and a value of "0" to all other records, and then sum up the list, returning a total of all records where the Description field contains the word "laptop."

See Also