Case Count

This is a little bit obsolete because the way the framework works is a little bit different than custom search, and it's too simplistic as is to be useful to us. However, the SQL down below might still be useful to someone.

Provides a listing of cases with a start date in the given date range.

Report Columns

• Case ID
• Case Type
• Start Date
• End Date
• Case Status

Optional Columns

Filters

• Start Date begin (inclusive)
• Start Date end (exclusive - avoids potential problems with time components)

Default Filter Values

• Start date begin defaults to blank, meaning no minimum date.
• Start date end defaults to blank, meaning no maximum date.

URL options

Group by columns

• Case Type

Sub-total columns

• Date (day/week/month/quarter/year)

Other Options

Category

• CiviCase

Permissions

• access CiviCase

SQL

SELECT c.id, ov1.label as 'case type', c.start_date, c.end_date, ov2.label as 'case status' FROM civicrm_case c
INNER JOIN civicrm_option_group og1 ON og1.name='case_type'
INNER JOIN civicrm_option_value ov1 ON (ov1.option_group_id = og1.id AND ov1.value = c.case_type_id)
INNER JOIN civicrm_option_group og2 ON og2.name='case_status'
INNER JOIN civicrm_option_value ov2 ON (ov2.option_group_id = og2.id AND ov2.value = c.status_id)
WHERE c.is_deleted <> 1
AND c.start_date >= ?
AND c.start_date < ?