Placeholder

Customer Forum

Count records

External Posted: 2011-02-14 02:43

 I'm trying to build a report in WB to count the number of activities created each month. I've created a similar report before on opportunities which extracts the amount forecasted each month by using month(). I can't quite seem to add a Count() function in. In my mind I would need an IF statement too but I can't get that to work either.


Any ideas?

Workbooks Support Posted: Mon, 14.02.2011 - 03:43

Hello,

Yes, you're right.  A count / tally of the records can be achieved by adding a calculated column with this formula COUNT(id).  You're just missing the "id" bit in your formula.  Here's a link to other useful formulas.

Image removed.

NOTE: It is important to remember that if you want to "breakdown" the count into sub-sets / grouped / summarised data you need to use the Summarise by tab.  A good example of this is Open Support Cases by Type or open Opportunities by Stage.  Simply select a relevant summary column on the Summarise by tab to produce the desired report:

Image removed.

Failing to summarise will result in a misleading report.  Note in the previous screenshot there were 8 open cases, split over 5 case types.  However, if you use the count without a summarise by column, and display any other columns you'll get a misleading report.  The first Case Type is displayed alongside the count, which is taking account of all open cases, or any type:

Image removed.

Workbooks Support Posted: Wed, 14.08.2013 - 11:48

In the next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

  • Create a new blank report based on Cases. Within the Details view add a column for Object Reference and Type.

The details view now displays all Cases and their Case Type. A Summary View can be added so that we can group by Type and then Count the number of records of each type. To do this:

  • Select 'Add Summary View' and 'Add grouping column'. Group by 'Type'.
  • Add a Calculated column called 'Count', which uses the following formula:

'COUNT( PARENT('Object Reference'))'

This counts the number of Cases for each Case type. The report should look something like the one below (click to enlarge).

Image removed.