logo

  » The Official Serio blog, visit us at http://www.seriosoft.com

Serio IT Service Desk & Helpdesk Blog

June 6, 2007

Writing a Simple Report using Datavision

Filed under: Serio Help Desk and Serio Service Desk — DuncanD @ 3:57 pm

This is the second post showing you how to create custom reports using Datavision, a simple open source database report writer. The earlier post is here.

Logging in to Datavision

If you’ve got this far, you’ve correctly installed Datavision. If it’s not correctly installed go back to my earlier post.

Launch Datavision by running the file datavision.bat, and then at the first dialog click ‘Start New Report’. You’ll then be asked for login details. Here’s what you need, and a screenshot is here.

1. Driver class name. Use this:
net.sourceforge.jtds.jdbc.Driver

2. Connection info. This is a little bit complex. Take a look at this string:

jdbc:jtds:sqlserver://server_name/db_name

Where it says server_name, replace this with the name or IP address of the machine that runs your SQL Server database.

Where it shows db_name, replace this with the name of your Serio database.

Your database administrator will know both of these items of information.

3. Database name. Re-enter the database name from 2 above.

4. Username. This is your database user that will be used to connect to the server. Remember you don’t need to use ’sa’ – just create a user with read-only access. It may be you need ‘mixed’ access on your SQL Server system.

5. Password. Your database password.

Now try to connect. If you fail, go back over the previous post and then this post and double-check everything. There is also a mailing list for support with Datavision. Posting to the list might be worth a shot if you get stuck, or post a comment on this blog.

Creating a report

If you’ve got this far you are ready to create a report.

I’m going to start with a very simple report definition. I want:

A report that lists Incidents logged broken down by logging team. The report will take a date range, which we will use with the date of logging to select our Incidents. At the end of the report should be a total.

I’m going to do this 2 ways – one using the views you get with the Serio Developer Kit (SDK) for Crystal, and I’m then going to repeat the exercise as if you didn’t have the SDK using the tables that exist in every Serio system. The second example will come later.

I’ve described the report I want above. You can see the finished report here. This is how it’s done. You can save your work at any time using file…save.

1. From the Insert menu, select ‘Insert Database Field’. You will then see the Fields browser, and from this expand all database fields as shown here. Select sv_issue_assignment, expand this, and add the field ‘logging_team‘. You can find out what sv_issue_assignment contains in the SDK documentation. Add the field to the detail band.

2. Now click ‘Insert…Group’ and add a new group. We need to do this because we want to have Incidents logged grouped by Team.

Select the field dbo.sv_issue_assignment.logging_team from the list and create a group. You can see how it looks here.

3. Now click ‘Insert…Special Field’. Drag a ‘Group record count’ over to the section of the report marked ‘Group #1 Footer’. Now add another Group record count to the bottom of the report, in the band labeled ‘Report footer’ as shown here.

4. Now we need to hide the ‘detail’ band. If you run the report now (select ‘Report…Run’ from the menu) then you’ll see why – it lists the team many times. Hiding the detail is easy – right click where it says detail, select ‘Suppress’ and then click ‘Always hide’. The detail band will be hidden, and displayed as grayed-out.

5. Now we have to turn our attention to data. The view sv_issue_asignment contains both Incident, Problem and Change data. By examining the documentation in the SDK, I can see that Incident records are denoted in this view with an ‘i’ in the column called type_code. I need to add a filter, and to do that I select ‘Report..Select records’ and enter this line

type_code = ‘i’

as shown here.

6. Finally, we need to add our date range criteria. To do this, select ‘Insert…Parameter field’ from the main menu. You’ll see a floating window called ‘Fields’ appear. From the menu on this window, choose ‘New Parameter…’. Call the parameter ’start_date’, and you’ll be taken to the Parameter definition window, as shown here. Complete the data as I’ve done.

7. Repeat step six, except creating an End Date parameter (ie, just change the word start to end) so you now have two parameters.

8. Finally, we need to use these parameters when we select Incidents. To do this, from the main menu, and click ‘Report…Select Records’.

Looking at the SDK documentation, I see that sv_issue_assignment has a column that stores the date logged, called ‘issue_logging_date_time’. Therefore we can add an extra filter here, as follows:
issue_logging_date_time >= {?start_date} and
issue_logging_date_time <= {?end_date}
so that our selection filter reads as follows

type_code = ‘i’ and
issue_logging_date_time >= {?start_date} and
issue_logging_date_time <= {?end_date}

as we can see in this picture.

Just to recap, we have a filter for Incidents, and want to only count Incidents logged between two dates.

9. By right clicking on fields on the report, you’ll find you can change fonts, alignment, styles and so on, and make the report quite tidy. Datavision also has some nice options for exporting reports – one of which is pdf. You can see the report again here.

Now have fun creating your own reports!

[Edit by admin: A more advanced Datavision example is here ]

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

SerioBlog:: (C) Copyright Serio Ltd
If you found this article useful please link to it.

Powered by WordPress