Serio IT Service Desk & Helpdesk Blog

This post is next in the series on Writing your own Custom Reports.

The last post was all about introducing the SELECT statement, and showing how you can use it to access data.

There are a few points to draw from the previous post, and they are:

Understand your data. You can’t write any report yourself unless you have a clear idea of what data is available. The devil is all in the detail here, and if you want to create a report yourself then you have to grasp the detail.

For example, understanding that the sv_issue_basics View contains both Incident, Problem and Change data is an important detail. You find that out by reading the comments about the View, but you can also infer it by looking at the columns. Since one of the columns has is type_code, and this is I for Incident, p for Problem and c for Change it is easy to deduce this View has all three types of data, and that this column allows you to filter.

Build your query in stages. Just like I did in the earlier post, you can gradually develop your query in stages.

In this post, I’m going to refine the query further, and show you some of the other things we can do with a SELECT.

We finished with this SQL statement

select issue_no, issue_logging_date_time, issue_priority
from sv_issue_basics
where issue_logging_date_time >= ‘2006-10-01′
and issue_logging_date_time <= '2006-10-31'
and type_code = 'i'
order by issue_no

Note that the first part of the statement says what columns we want to see. There is a shorthand way of saying we want to see every column, using *, as in this example below

select *
from sv_issue_basics
where issue_logging_date_time >= ‘2006-10-01′
and issue_logging_date_time <= '2006-10-31'
and type_code = 'i'
order by issue_no

Grouping and Counting

If you try to write a report yourself, you will almost certainly want to both group by different data, and to count instances of particular groups. A lot of the reports in SerioReports do this. This post will show you how.

The Task

Produce a report for October that groups Incidents by Priority, and counts the number logged for each Priority.

What we have to do is introduce a new clause – the GROUP BY clause. This clause tells the database to ‘fold’ rows that are similar together – in this case, by Priority. Since we wish to have a count of Incidents, we can use the function specially provided in SQL for this, the COUNT function (these are called aggregate functions).

select issue_priority, count(*)
from sv_issue_basics
where issue_logging_date_time >= ‘2006-10-01′
and issue_logging_date_time <= '2006-10-31'
and type_code = 'i'
group by issue_priority
order by issue_priority

If you wish to GROUP results, here are a few simple rules to help you get it right.

- If you include a column in the SELECT clause that is not an Aggregate function, you must include it in the GROUP clause.
- Generally it is a good idea to include GROUPed columns in the ORDER clause.

Using these rules, I can extend the query to group by Priority and Problem Area, as follows.

select issue_priority, problem_area, count(*)
from sv_issue_basics
where issue_logging_date_time >= ‘2006-10-01′
and issue_logging_date_time <= '2006-10-31'
and type_code = 'i'
group by issue_priority, problem_area
order by issue_priority, problem_area

You can see a picture of the query being run by clicking here.

• Writing Custom Reports Part 3
• Writing Your Own Custom Reports in Serio
• Writing Custom Serio Reports using Open Source Tools