Creating Your Own Reports with Datavision - Formulas

This is a follow-on from my earlier posts about the open source report writer Datavision - the installation post is here and the getting started post is here. If you have not read the earlier post you need to do so before continuing.

In the getting started post last week I did some simple grouping, used some simple calculated fields, and added some variable report criteria.

This week I'm going to do something a little more complex, involving data evaluations and counting.

The Target Report

The report I'm going to create will take a start date and end date, and will list for each Serio Team resolving Incidents the number of Incidents resolved, and the percentage of Incidents resolved on time.

Creating the Report

Start by logging-in to Datavision and click Create a new report.

As previously, I'll use the views provided as part of the Serio SDK. Looking at the documentation for the views, I can see that I can get the resolving team from the view sv_issue_resolution.

So, from the Datavision menu, choose Insert...Database Field and from the list provided under All Database Fields, drag pending_team to the detail band, as I've done in Figure 1. P160 Pendiing Team

Figure 1 - Pending Team

Now we need to group by the Team. From the menu, choose Insert...Group and select the column sv_issue_resolution.pending_team. Having created a group, you can add a computed field to count the group. To do this, choose Insert...Special Field and drag a Group Record Count over to the Group #1 Footer band (do this by dragging and dropping).

This gives us a count of records by team. However, now we need to find out how many were resolved on time. I can see by consulting the Serio documentation there is another view called sv_issue_sla that contains SLA data - so I'll add this table to the detail band and perform a join between sv_issue_resolution and sv_issue_sla.

From the Datavision menu, choose Insert...Database field. Add sv_issue_sla.pending_mins_actual and sv_issue_sla.completion_mins_target to the detail band by drag and drop, as I've done in Figure 2. These two columns contain our SLA target and actual SLA achievement. P160 Add SLA fields

Figure 2 - Add SLA fields

To make the join between the two views sv_issue_resolution and sv_issue_sla, which we can do now we've placed database fields from both tables, select Database...Table Linker and link the two views as shown in Figure 3.P160 Linking Tables

Figure 3 - Linking Tables

Now it's time to look at a really cool feature of Datavision - formulas. The formula language is something called Ruby - google for 'ruby script' and you'll find lots of info about it. For me this makes Datavision a serious tool.

Here is how I'm going to use a formula. I've brought in our SLA target and actual SLA timing. If the target time we took is less than or equal to how long we actually took to resolve the Incident, I'm going to return a 1 - if not, I'll return a 0. Then in the footer I'll add-up all the 1's I have, and this will tell me how many Incidents were on time.

It sounds harder than it is. Here is the formula in Ruby:

if {dbo.sv_issue_sla.pending_mins_actual} <=

P160 FormulaTo add the formula, click 'Insert..Formula Field'. Then from the Fields window, choose 'Field...New Formula'. Give the formula name (such as 'On time') and then enter the formula above, as shown in Figure 4. 






Figure 4 - Formula


Having created your formula, drag it to the Detail band.

P160 Aggregate FieldNow we need to add-up all the 1's from our formula. Luckily this is easy in Datavision. First of all, click the 'On time' formula and then, while it's selected, click 'Insert...Aggregate Field' from the Datavision menu. Create a 'Sum' aggregate field as shown in Figure 5. 





Figure 5 - Aggregate Field


This will add-up all the 'on time' resolutions for each team, and give us a grand total at the bottom. I think that's really neat.

We are now almost finished. We need to tell Datavision to only count resolved Incidents, and from the Serio documentation on the views I can see this is what we need:

sv_issue_sla.issue_status in ('p', 'c') and
sv_issue_sla.type_code = 'i'

From within Datavision, select Report...Select Records and enter the SQL above.

Finally, We need to hide the Detail band - we are not interested in it's contents. Right-click on the 'Detail' label, and click 'Supress'. Do a little bit of tidying-up by removing unused headings, and your report definition should look like Figure 6. P160 Final Definition

Figure 6 - Final definition

And you can see the finished report attached (exported to PDF).

Tip: The default font seems to be Time New Roman, which I think is rather ugly. If you click Format...Default Format you can change this however to something more attractive.