As corporate philosophy continues to integrate itself into museum administration, greater emphasis is placed on the quantification of staff activities. Thus, I often find myself in the role not only of Database Administrator, but also of bean counter. This tutorial outlines how to create a Crystal Report that provides statistics drawn from TMS.

This is the “Statistics at a Glance” report originally developed for the Walters Art Museum. The museum had undertaken a major digitization project, and the report was designed to demonstrate progress through a standard set of metrics. This report was included in the presentation Advanced Crystal Reports at Collective Imagination. 3-4 June 2009, Amsterdam, The Netherlands.

Example

SQL View

The report draws all of its data from a single SQL view called plugin_SAG. Each SELECT statement represents a single metric, and returns four fields:

• grouper: The group header under which the line item will be reported. This needs to be identical for each item within the group.
• label: The individual label used to describe each line item.
• value: The statistic aggregated using the SQL COUNT(*) function.
• rank: A number used to organize the line items within a group.

The line items also contain the table name and WHERE criteria that aggregates the number to be reported, and all of the line items are joined together in the view using a UNION statement.

Below is an example of some basic statistics that could be included in the report:

/* Stats at a Glance View */
CREATE VIEW [plugin_SAG] AS
SELECT '1. Object Records' AS grouper, 'Object Count - Total' AS label, COUNT(*) AS value, '1' AS rank FROM Objects
UNION ALL
SELECT '1. Object Records' AS grouper, 'Object Count - Physical' AS label, COUNT(*) AS value, '2' AS rank FROM Objects WHERE IsVirtual = 0
UNION ALL
SELECT '1. Object Records' AS grouper, 'Object Count - Virtual' AS label, COUNT(*) AS value, '3' AS rank FROM Objects WHERE IsVirtual = 1
UNION ALL
SELECT '1. Object Module' AS grouper, 'Object Count - Total Records' AS label, COUNT(*) AS value, '1' AS rank FROM Objects
UNION ALL
SELECT '1. Object Module' AS grouper, 'Object Count - Physical Records' AS label, COUNT(*) AS value, '2' AS rank FROM Objects WHERE IsVirtual = 0
UNION ALL
SELECT '1. Object Module' AS grouper, 'Object Count - Virtual Records' AS label, COUNT(*) AS value, '3' AS rank FROM Objects WHERE IsVirtual = 1
UNION ALL
SELECT '2. Media Module' AS grouper, 'Media Count - Total Records' AS label, COUNT(*) AS value, '1' AS rank FROM MediaMaster
UNION ALL
SELECT '2. Media Module' AS grouper, 'Media Count - Approved Records' AS label, COUNT(*) AS value, '2' AS rank FROM MediaMaster WHERE ApprovedForWeb = 1
UNION ALL
SELECT '2. Media Module' AS grouper, 'Media Count - Unapproved Records' AS label, COUNT(*) AS value, '3' AS rank FROM MediaMaster WHERE ApprovedForWeb = 0

After producing the view, remember to change the permissions on the SQL Server, so that the TMSUsers group is permitted to SELECT.

Crystal Report

The report itself is fairly simple: records are grouped using the {plugin_SAG.grouper}. The details includes {plugin_SAG.label} and {plugin_SAG.value}, and the Sort Expert uses the value in {plugin_SAG.rank}.

[Download not found]