Create an Executive Overview Dashboard

Suppose you are running a help desk and want to create a dashboard that lets executives easily monitor support activities.

First - Create Two Simple Headline ReportsPREVIOUS/THIS

• Tickets Created headline report This report shows the number of tickets created so far this year, updated daily. It slices # Tickets by the Year (Created) attribute.

  
  

  Select the following in the Report Editor:

  • WHAT: # Tickets
  • HOW: (none)
  • FILTER: Year (Created) is this year.
  • Choose the Headline Report visualization to create a single-number visualization.

• Tickets Solved headline report This report shows the number of tickets solved so far this year, updated daily. It slices # Solved Tickets by the Year (Solved) attribute. 

  
  
   Select the following in the Report Editor:
  • WHAT: # Solved Tickets
  • HOW: (none)
  • FILTER: Year (Solved) is this year

Second - Create a Tickets Created by Month combo chart

This combo chart provides the following key information:

• Ticket volume by month
• Direct comparisons of the current year and the previous year
• Average ticket volume as an indicator of the company’s growth  
  
  

This report uses four custom metrics and some advanced visualization techniques.

Metrics

Create these metrics in the Custom Metric Editor. See below for details on the meaning of the MAQL syntax. For more information, see Get Started with Using MAQL to Write Metrics.

Reading the Metrics

PREVIOUS/THIS

Metrics 1 and 2 in the table above are identical except for the keyword that appears at the end of each metric. PREVIOUS returns the number of tickets created in the previous year; THIS returns the number of tickets created (so far) in the current year. For more information about these keywords, see Referencing Floating Time Periods.

Metric 3 - Average Tickets Created - Last Year

 Although the syntax looks different, this metric is similar to Metric 1. The inner SELECT statement uses two keywords to total the number of tickets from the previous year, before dividing the total by 12 to calculate the monthly average.

• BY ALL IN ALL OTHER DIMENSIONS: This keyword means that, regardless of the attributes used to slice your report, the metric returns the total number for # Tickets. In this case, the maximum value for the metric is always returned, no matter how the report is sliced. For more information, see BY ALL IN ALL OTHER DIMENSIONS.
• WITHOUT PF: This keyword, which can also be written as WITHOUT PARENT FILTER, ensures that no report filters will be applied to this metric, which means that the total number of # Tickets is applied to the calculation. For more information, see PARENT Filters.

To summarize, the numerator of Metric 3 defines an unfiltered way to calculate the total number of tickets created in the previous year. The denominator then divides this number by twelve to produce a static metric that returns the monthly average of tickets last year, regardless of how it is sliced in a report.

Metric 4 - Average Tickets Created - This Year

 Very similar to Metric 3, Metric 4 calculates the monthly average number of tickets by factoring only the months that have elapsed so far in the current year. If we were to divide the total number of tickets (numerator) for the year by 12, we would get an inaccurate number if, for example, the current month is June. 

• SELECT COUNT(Month (Created), Ticket Id): This part of the denominator counts the possible month values based on the ticket IDs. The first parameter we pass into the COUNT metric is Month (Created)), which counts the number of unique values present. The second parameter, Ticket Id, ensures that the count is performed in the Ticket Id dataset. Note that while this part of the denominator counts all possible month values for the unique ticket identifiers, this data is later filtered by the WHERE clause (WHERE Year(Created)=THIS), which ensures that only months for the current year are counted. Because no data for future months of the current year has been generated, the metric only returns data for the present month and the prior months of the current year.
• BY ALL OTHER: Shorthand for BY ALL IN ALL OTHER DIMENSIONS, this keyword means to count the available month names without regard to any slicing and dicing by any attributes in the report.
• WITHOUT PF: This keyword causes any report-level filters to be ignored in the calculation of this part of the metric. When combined with BY ALL OTHER, the denominator generates a constant value for the current year.

For more information about COUNT, see Count Attribute Values In Specific Data Sets.

Add Metrics to Define the Report

Now that you have defined the metrics, you can add them to a new report:

Configure the Combo Chart

After you have saved the report, configure it as a combo chart. A combo chart uses multiple visualizations in a single chart. In this case, we’ll use the following visualizations:

• An area chart to show the number of tickets
• A line chart to show the averages

Steps:

1. Open the Configuration pane and set the following values:

   • Horizontal (X): Month (Created)
   • Vertical (Y): Metric Values
   • Color: Metric Names

2. Click the drop-down menu next to Metric Values. For each metric, select the following axis:

   Metric Name	Axis
   Created Tickets - Last Year	Primary
   Created Tickets - This Year	Primary
   Average Tickets Created - Last Year	Secondary
   Average Tickets Created - This Year	Secondary

3. Apply a different chart type to each axis. This allows you to combine multiple chart types in a single chart. In the Advanced Configuration pane, expand the Y axis panel.

   • For the Primary Axis chart type, select Area chart.
   • For the Secondary Axis chart type, select Line chart.
   • Set the Max value for each axis to the same value. This makes it easier for your users to read the data.  
     
     
       

4. Save your changes.

Third - Create Additional Headline Reports

Now, you create the other headline reports for the dashboard.

“Percent of Tickets Solved” Headline Report

This small headline report shows the percentage of tickets solved for the current year. This report uses a custom metric to divide the total number of tickets created in this calendar year by the total number of tickets solved for the year.

This headline report utilizes the following % Solved custom metric: 

“Tickets Closed in Less than 24 Hours” Headline Report

This simple report shows the total number of tickets solved in less than twenty-four hours. It references a custom metric that builds on pre-existing metrics to calculate the result. 

Create the Fast Resolution (< 24 hrs) metric. Feel free to adjust this metric to define a suitable time period for your enterprise. 

“Current Year Backlog” Headline Report

This headline report shows the number of tickets created this year that remain open.

This report can be defined using standard metrics and filters: WHAT: # Unsolved Tickets HOW: (nothing) FILTER: Year (Created) is “this year” With a bit of customization, you can create a similar executive overview dashboard for your project. Remember to focus on 1-2 meaningful reports and headline reports.