Reporting Examples

According to ITIL, the objective of service reporting is to analyze and deliver service measurement information to stakeholders, governors, and decision makes in a form that enables action. This is a wonderful statement and I urge you to build reports that enable action. Therefore in this article I will give you the information you need in order to build reports that enable action from the data collected by Kinetic Request CE.

Scroll down for Example Reports

Usage

Building reports will require the report writer to understand the data. To do this the report writer will need to have an understanding of the Forms and the database storing the Form data.

Forms

The Form is presented to the end user and will collect information in order to process and fulfill the Form Submission. The report writer should try to gain an understanding of the Form to learn what information is collected and how it will be used. For this article we will use the General Support Request:

General Support Request

From the screenshot above we can see that the General Support Request collects Summary and Details from the end user.

Database

The columns in the database tables are prefixed by the following letters:

  • c: The c indicates that this is a Core field which is added automatically by Core.
  • l: The l indicates that this is a Question on your form and contains the Limited Answer. The data stored in the l column is Limited to 4,000 characters.
  • u: The u indicates that this is a Question on your form and contains the Unlimited Answer.

The purpose of this article is to demonstrate how to build actionable reports utilizing the following database tables:

services - This table contains a record for every Form submission in your environment and includes many fields but I will highlight the following:

  • c_id: Confirmation # of the Submission. Note - the last 6 characters of this field is your Confirmation #.
  • c_formSlug: This is the Name of the Form.
  • c_coreState: This is the current State (Draft, Submitted, and Closed) of the Form submission. The data held in this field is used by the application to determine the State.
  • c_createdAt: This is the Date/Time of the initial creation. Meaning if you have a Form with multiple pages it is the Date/Time value when moving from page 1 to page 2.
  • c_submittedAt: This is the Date/Time of the Form submission. This is set when the last page on the Form is submitted or when the State is set to Submitted.
  • c_closedAt: This is the Date/Time when the Form submission has been Closed.
  • l_Status : This is the Status of the submission. This field is similar to the c_coreState field however the l_Status field is set from your business process and therefore could be any Status value you choose.

services_general-support-request - This table contains a record for each submission to the General Support Request Form. The fields that I want to highlight are listed below:

  • c_id: Confirmation # of the Submission. Note - the last 6 characters of this field is your Confirmation #.
  • c_coreState: This is the current State (Draft, Submitted, and Closed) of the General Support Request Form submission. The data held in this field is used by the application to determine the State.
  • c_createdAt: This is the Date/Time of the initial creation. Meaning if you have a Form with multiple pages it is the Date/Time value when moving from page 1 to page 2.
  • c_submittedAt: This is the Date/Time of the Form submission. This is set when the last page on the Form is submitted or when the State is set to Submitted.
  • c_updatedAt: This is the Date/Time of when this Form submission was last updated.
  • c_closedAt: This is the Date/Time when the Form submission has been Closed.
  • l_Requested For Display Name: This is the Requested For name displayed in a "user friendly" way.
  • l_Status: This is the Status of the submission. This field is similar to the c_coreState field however the l_Status field is set from your business process and therefore could be any Status value you choose.
  • l_Summary: This is the Summary submitted by the Requestor. Note: we are using the Limited version as this answer will always be less than 4,000 characters.
  • u_Detail: This is the Detail submitted by the Requestor. *Note we are using the Unlimited version as this answer can be more than 4,000 characters.

queue_approval - This table contains a record for each Approval associated to your Forms. The fields that I want to highlight are listed below:

  • c_originId: This field contains the c_id (Confirmation #) of the end users submission. Therefore this field is how we will join the queue_approval record(s) back to the services table.
  • c_createdAt: This is the Date/Time of the initial creation of the Approval record.
  • c_closedAt: This is the Date/Time when the Approval record has been Closed.
  • l_Decision: This field contains the Approval Status (Approved or Denied).
  • I_Resolution: This field contains a concatenation of I_Decision (Approved or Denied), the l_Assigned Individual (approver), Notes for Customer (Additional Comments if applicable), and Denial Reason (if applicable).
    With the information above we now have an understanding of General Support Request Form and the Database. We are now ready for the fun part, to build the reports that enable action.

Examples

There are many reports that you can create in order to enable action. However, for this article I will discuss the following:

  1. **Aging:** This report will show a listing of all Submissions that are in an "Open" Status and display Confirmation Number, Form Name, Status, Age, Create Date, and Updated Date. The action enabled by this report will allow you to apply more resources to the "Old" submissions.
  2. Form Submissions - Monthly: This report will display a Graph of the Form Submissions broken down by Month. The action enabled by this report will allow you to determine which Forms are being heavily used and which aren't being used. Having this information will allow you to "advertise" to your end users to ensure that they are aware of the forms that aren't heavily used.
  3. Form Activity: This report will provide a count of the number of form submissions that were Created in the Last Month, Closed in the Last Month, and Currently Open broken down by the Form name. The action enabled by this report will allow you to establish an Activity baseline and will show you if your current resources are able to keep up with the Submissions of each Form.
  4. Form Duration:This report will display the Average Duration time it takes for a Submission to be Closed by each Form. The action enabled by this report will be to apply more resources to the Forms with the longest durations and reduce the fulfillment time.
  5. Submissions by User: This report will display a Treemap of the number of submissions by User. The action enabled by this report will allow you to identify your users. This will help determine your champions and also help you determine how to get more people using your forms.
  6. Request by Month - Status: This report will display a bar graph of a Request form's submissions broken down by Month and Status. The action enabled by this report will allow you to determine if your specific Form has enough resources in order to complete the fulfillment process in a timely fashion.
  7. Request - Submitters: This report will display a Treemap of the number of submissions by User for a specific Request. The action enabled by this report will allow you to identify your users. The people who are submitting your form the most should be able to provide you with feedback improvements for continuous improvement.
  8. Approval Process:This report will show all Closed Submissions and displays the Form Name, Count of the Times Requested, Count of the Number of Approvals, the Approved Count, Denied Count, Denial % (Denied Count/Times Requested), Average Fulfillment Duration, and the Average Approval Duration.