Writing a Simple Report using Datavision

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: 

Loggin1. 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.

Figure 1 - Logging in

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.

fields list

I've described the report I want above. You can see the finished report attached. 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 (see Figure 2). 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 (as shown in Figure 3).

 

 

 

 

Figure 2 - Database Fields Fields Logging Team Added

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Figure 3 - Fields logging with team added

 

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 in Figure 4.Group Added

Figure 4 - Group added

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 in Figure 5.With Footers

Figure 5 - With footers

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.

Type Code Selection5. 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 the following line, as shown in Figure 6.

 

type_code = 'i' 

 

Figure 6 - Type Code Selection 

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. Complete the data.

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

Final Criteria8. 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:

 

 

 

Figure 7 - Final criteria

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 Figure 7. 

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. Again, you can find the report attached.

Now have fun creating your own reports!

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

 

Tags: 

Categories: 

Attachments: