Group by queries

In this chapter we will describe how to create a summary overview with a possibility to display records that build up summary data (drill down).

If we need summary data from a class, we will use a query with GROUP BY clause. In the example hereinafter we want to show number of incidents that are not closed grouped by particular users. Data are stored in the class incident, status of incident is a reference to the class incident-status and solver is a reference to the internal class person.

The objective is to show solvers in rows and number of new incidents, open incidents and total incidents in columns. We will also show how to ensure that click on the summary data will lead to display of particular records which are represented by the summary.

We will create a query, that returns also id of the given user (id of the ObjectGears user) beside the user full name. This information will be used in the link to the summary data breakdown.

This is SQL definition of the query:

SELECT ai.id pid, ai.fullname,
SUM( status_New) status_New,
SUM( status_Open) status_Open, sum( status_New) +
SUM( status_Open) Total from
(
SELECT p.id, p.fullname,
CASE WHEN i.status = 1 THEN 1 ELSE 0 END status_New,
CASE WHEN i.status = 2 THEN 1 ELSE 0 END status_Open FROM {{: class.incident :}} i
INNER JOIN {{: class.incident-status :}} ins ON ins.id = i.status
INNER JOIN person p ON p.id = i.solver
WHERE i.status in (1, 2) AND
i.deleted IS NULL
) ai
GROUP BY ai.id, ai.fullname

Data returned by this query will look like this:

We will display query columns and perform these changes:

1. Column with user id should not be displayed. We will open the detail of the column pid and set its property Display in list to Unchecked.
2. We want to show query names according to the user localization. We will open detail of particular columns and set new names in particular language options.
3. Name of the user shall work as a link to the list of incidents assigned to this user. We will open the detail of the column solver and set these properties:
   • Use as a reference: Checked
   • Link format: './Datas.aspx?CId=430&filter=solverEQ' + Cstring(Column('pid')) + '~AND(statusEQ1~ORstatusEQ2~)'
   • Enable opening the link in a list: Checked

Link format shown above is used for class with id 504, that contains column id 6223 (solver) and 6224 (status). Documentation to the filter syntax is described in the chapter URL filter.

The query looks after these modifications like that:

After clicking on the user name list of incidents is open and filter to the given user and incident status is applied.