Writing Your Own Custom Reports in Serio

I intend to use the subject of reporting for a series of blog articles, covering how to create some simple reports for your own Helpdesk or Service Desk.

Rather than using a software tool such as Crystal, which I know a lot of you do not have, I will to use tools that come with both Microsoft SQL Server and Oracle to create simple SQL queries, and will illustrate how the Serio reporting Views can help you. If you understand how to use the Views, and a little bit of SQL, you will have all the skills you need to create your own reports in a reporting tool.

I will assume you have never used SQL before.

Jargon Buster

SQL: Short for Structured Query Language. This is a language used to interact with a database system. For the purposes of this tutorial it is the language we will use to specify the data we are interested in.

View: All this means is ‘virtual table’. Your database stores data (such as your Incidents or Configuration Items) in tables, a bit like a spreadsheet. Each table is made up of columns, each of which has a name, and rows which contain data. Views are created for the convenience of those writing reports. If you are still baffled by that do not worry – think of each View as a convenient way for you to generate reports.

Getting Started

You’ll need to get hold of the Serio Developer Toolkit for Crystal. You can get this from Serio if you ask – it is free. Follow the instructions in the Kit to create the Views I will use in this tutorial series. Don’t be put off, as it takes no more than 20 minutes to create the Views (even if you are a novice).

Tools for creating and running queries

Microsoft SQL Server: Use the SQL Query Analyser. You can access this from the SQL Server client tools, installable from the SQL Server CD (make sure you install the service packs available from Microsoft). Ask your DBA for assistance in set-up and installation.

Oracle: SQL*Plus. Yes I know it’s a fairly primitive tool, it is it shipped with all Oracle systems. It will be on the Oracle CD. Ask your DBA for assistance in set-up and installation.

Documentation on the Views is available with the Developer Kit. Look in the ‘Schema.xls’ document, and click on the Views worksheet.

Creating our first query

For the first query, I’m going to use the very convenient View sv_issue_basics. This View contains information about every Incident, Problem and Change ever logged in your Serio system.

The Task

Create a query that lists the reference number, date of logging and priority for each Incident logged between 1st October 2006 and 31st October 2006.

To read data from the database, we need to issue a SELECT statement. Rather than explain SELECT I’m simply going to write a statement, and then refine it.

select issue_no, issue_logging_date_time, issue_priority

from sv_issue_basics

It’s probably not a good idea to run this query just yet. We have not specified that we are only interested in data from October – so this query will return information about each Incident, Problem and Change we’ve ever logged! What we need is to specify a condition, and you do that as follows:

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'

SQL query

Figure 1 - Query being executed on SQL Server 

I said that sv_issue_basics contains information about all Incidents, Problems and Changes, but we are not telling the database that we only want Incident data. Looking at the documentation for sv_issue_basics, we can see a column called ‘type_code’ that ‘equals i for an Incident, p for a Problem and C for a Change’. This looks like a perfect way to just deal with Incidents, allowing me to add a clause as follows.

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'

If you run this query, you’ll see that it now returns just Incident data, but you’ll probably find that the data returned is unsorted – because we have not told the database to sort it before returning to us. The following statement applies a sort:

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' a

nd type_code = 'i'

order by issue_no

This shows the 4 most important parts (but not the only parts) of a SQL statement: select, from, where and order.

We will continue this later, and welcome feedback on this.

Tags: 

Categories: