OVERVIEW

The Report Builder is a very powerful tool which enables you to get data from the system and display the results. However it can be complex and knowing which Tables and Fields to use to get the data you require can sometimes be daunting. The following is a list of commonly used Tables, Fields, their definitions and how to use them.

Guidance 

Tables 

The Main Tables that you will likely be using are:

• Asset - The Table that holds the Default Asset Information.
• Caller - The Table that holds the Default Caller Information.
• Client - The Table that holds the Default Client Information.
• Ticket - This Table holds the Default Ticket Information for ALL System Ticket Areas.
• TicketHistory - This Table links to the Ticket Table and contains the lines within the Ticket History for ALL System Ticket Areas. 
• User - The Table that holds the Default User Information.
• Any Custom Forms you have created, These are shown as the System Area you created them in, followed by the Form Name. e.g. TicketJiraIssue (Where Ticket is the System Area and Jira Issue is the Form Name.

These are also seen within the Main Data Item Selection within the Report Builder Wizard.

Fields

When you select the Table(s) you are presented with a list of Fields that you can bring into the report data. The default is that all fields are selected. You can of course deselect the ones you don't want. We recommend when you are starting out you add just the fields you need to make managing the report easier, this guide should help you identify those fields.

Working off the most common requested Report which would be related to your Tickets we're going to go through what some of the less obvious fields are used for and how to use them in your Reports.

Quick links to information

• Ticket Reference Numbers
• Status
• Ticket Type
• Custom forms
• Categories
• Service Level Agreements
• Tick boxes and Radio Buttons
• Working with Time
• Survey results

Ticket reference numbers

The Ticket_Id is the unique database reference for the Ticket, best used when building a Ticket Quick report as it results in more efficient query processing and much quicker report production.

The Ticket_DisplayId is the Reference Number that is used in communications, visible on the Ticket and the reference that is sent to your customers when they log Tickets with you, it is prefixed by letters referring to the ticket type e.g. IN001 for Incidents.

The same applies for any fields where there is the id and name options. We would recommend using the names in most cases as you able to see the names as you have them configured within your system. If however you change these names in the admin area of Vivantio and you will need to change your Filters and Parameters to match. For example if you had a category of e-mail and you renamed it to Email.

Status

As you have the potential to have lots of different Statuses setup in your System, excluding or including all the different Statuses from your reports could be a long list of filters. Therefore we would suggest using the Status Type as if you add, remove or rename any of your Statuses you can still capture all the different types of them.

The Status Type's are - 

• Status Type 1 = Open (This will cover any Statuses where the SLA Timer is Running)
• Status Type 2 = On Hold (This will cover any Statuses where the SLA Timer is Stopped)
• Status Type 4 = Closed (When a Ticket is Closed)
• Status Type 5 = Pending (When a Ticket first comes into the System and is in the Pending Queue)
• Status Type 99 = Deleted (When a Ticket is Deleted)

To exclude all Deleted Tickets from your Reports you can use the following in the Filter -

The filter would be built as Ticket, Ticket_StatusType, Does Not Equal, Fixed Value, 99

Different Ticket Types.

As ALL Tickets are stored in the same table you can split them down by Ticket Type. By either setting up Parameters & Filters, Filters Only or Grouping by the Ticket Type. The Field name for the Ticket Type is - Ticket_RecordTypeNameSingular

You can use this in the Report Parameters so you can select which Ticket Type you want to look at, meaning you can use the same report for all your different Ticket Types.

Example - When creating the Parameters

Ticket Type - Lookup - Ticket - Ticket_RecordypeNameSingular.

If you wanted to Filter the Report Based on the Parameter then you would enter the following -

Ticket - Ticket_RecordTypeNameSingular - Equals - Parameter - Ticket Type  

If you didn't want to Select the Ticket Type but wanted to Filter your report so it was always running for a specific Ticket Type then you can do this Directly in the Filter -

Ticket - Ticket_RecordTypeNameSingular - Equals - Fixed Value - Incident 

Custom Forms

Fields 

Custom Forms and Custom Fields are shown in the format of the System AreaForm Name_FieldName.

E.g - TicketJiraIssue_JiraProjectName

Custom Forms within the Asset area will be prefixed with Asset, Client area will begin Client, Callers area will begin Caller and so on.

So in this system within the Ticket System Area we have a Custom Form called JIRAIssue and a field on that form named JIRA Project Name (the field labels that display in the technicians and self service portal maybe different to the name you see in report builder, any spaces in the form name will have been removed - for more information see our guidance on Forms and Fields here).

Even if you have multiple Ticketing areas the Custom Forms will always be prefixed with Ticket so if you have similar information within different ticket areas ensure you are able to differentiate between the Forms. 

Categories

Within the Report Builder you have different options for reporting on Categories.

• Category Name
• Category Lineage (Full Category Path)
• Level One Category Name (Top Level)
• Level Two Category Name
• Level Three Category Name
• Level Four Category Name 

This means you can report on up to Four Levels of Categorization of your Tickets. The following example shows all these fields added to the Report Canvas and the Results in the sample data we are looking at.

This example the Ticket had a Category of Laptop which is a Sub Category of Hardware. We are only using 2 levels of Categorization in this instance. The Category Lineage shows the Full Path of the Category. Then the Level 1 and Level 2 break it down.

Service Level Agreement's (SLA's)

As there are multiple different SLA's you can configure within the main application these are all available within the Report Builder.

You are also able to report on multiple data points for each SLA. Such as the Time Elapsed, Time Remaining, the Time at Completion of the SLA, Target Date and the Completion Date.

The two standard SLA's are -

Response SLA and Close SLA.

Additional SLA's are

Diagnose SLA and Bespoke SLA.

Looking at the two standard SLA's the different fields available are -

Response SLA

• Ticket_ResponseSLATargetDate - This is the Date / Time that the Response should be made by based on your SLA.
• Ticket_ResponseSLATargetDuration - This is the Time in minutes that the Response should be made by based on your SLA.
• Ticket_ResponseSLAElapsedTime - This is the amount of Time in minutes that has passed since the Ticket was created and the Ticket has not currently been responded to.
• Ticket_ResponseSLATimeRemaining - This is the amount of Time in minutes that are remaining until the first response SLA is reached.
• Ticket_ResponseSLACompleteDate - This is the Date / Time that the Ticket Response was sent.
• Ticket_ResponseSLADurationOnComplete - This is the amount of Time in minutes that passed between the Ticket being raised and the Response being sent.
• Ticket_WithinResponseSLA - This is a Tick Box that shows whether or not the Ticket was Responded to within the SLA Time.  

Close SLA

• Ticket_CloseSLATargetDate - This is the Date / Time that the Ticket should be Closed by based on your SLA.
• Ticket_CloseSLATargetDuration - This is the Time in minutes that the Ticket should be Closed by based on your SLA.
• Ticket_CloseSLAElapsedTime - This is the amount of Time in minutes that has passed since the Ticket was created and the Ticket is not currently Closed.
• Ticket_CloseSLATimeRemaining - This is the amount of Time in minutes that are remaining until the Close SLA is reached.
• Ticket_CloeSLACompleteDate - This is the Date / Time that the Ticket was closed.
• Ticket_CloseSLADurationOnComplete - This is the amount of Time in minutes that passed between the Ticket being raised and Closed.
• Ticket_WithinCloseSLA - This is a Tick Box that shows whether or not the Ticket was Closed within the SLA Time. 

 

The same applies for the other SLA's if you are using them within your system (e.g. if you have custom SLAs such as a rolling customer update).

An additional SLA field is - Ticket_WithinNextSLA - This is a Tick Box that will identify whether the Ticket is within the next SLA it is due to expire on.

Tick Boxes and Radio Buttons

These work differently to fields that have data in them by the way that they are either a True or False result or a 1 or 0 result.

For example in the Caller Table there is a field named SelfSeviceLoginEnabled. If the Login is enabled this will return a result of 1 whereas if it is not enabled the result is 0.

Using a very simple report to show the outcome you can see here that we have dragged the Caller Name and the Self Service Login Enabled fields onto our report canvas.

Previewing the Results you can see two different caller records, one of which has this enabled the other doesn't.

Calculated Fields 

To make these more friendly when viewing the report it is possible to create a Calculated field so when the result is a 1 make it Yes and when a 0 make it No.

To do this on the Field list on the Right Hand side within the Report,

Right Click and chose Edit Calculated Fields, then Add -

Give the Field a name, This can not contain any spaces. Select Report Data from the Data Member Drop down.

Then Click on the ellipse on the Expression field -

You can then build out the Expression. So for this example we will use an Iif statement as follows -

The way the Statement is built up is - If ([field name] = 1 Then 'Yes' Else 'No' ) The commas act like the Then and Else.

Then using this Field on your Report will show the following -

A similar expression can be used to replace a field that displays as a Tick Box in the Report. Such as the following example.

Here we've created a Calculated Field with the following expression to Show either Yes or No depending on whether the Tick Box is showing a True or False value.

The results in the Report can then be shown as -

So you can then remove the Field that has the Tick box and Field Description in.

Hour & Minute Label 

By default all time fields are in minutes, to display these are hours and minutes you can use the HH:mm Label Field within the Left Hand Menu.

Drag and Drop the Field into the Report Canvas and then use the > on the Field to see the Properties of the field. One of the options is Data Binding. If you select a Time Field from the Data Binding then the minutes will be converted into Hours and Minutes.

Here we have used the HH:mm Label and the Data Binding to the Ticket_CloseSLAElapsedTime 

Seeing the original minute field and the HH:mm field next to each other on the Report you can see that 62313 minutes has been converted to 1038hrs and 33 minutes -

NB - It isn't possible to use the HH:mm Labels in Charts. These will only show in Minutes.  

Survey Results 

Surveys are Custom Forms and Fields so can be found in the Report Data Tables in the same way as Custom Forms. As the Surveys will be from a Ticket Area they will follow the same principle as Custom Forms in the naming convention. If you only want to report on the Survey Data this will be available in the Main Data Item listed as - TicketSurveyName

In the example we have here our Survey is named My Survey so in the Main Data Item it is shown as - TicketMySurvey.

However if you want to bring in more details from the Ticket you would need to select Ticket as the Main Data Item, then TicketMySurvey in the Sibling View.  

When in the Report you will then be able to see the fields from your Surveys as well as the Ticket Detail Fields.

The fields of most interest will be the Modified Date and the Comments and or the answers to the Questions you have created in your Survey.

As with the Tick and Radio buttons mentioned above if your Survey has a scale and you want to convert these to figures you are able to use Calculated Fields to turn them into percentage results, averages etc. 

Reviewed: 08 March 2023