Create an Executive Overview Dashboard

This intermediate tutorial assumes some knowledge of MAQL, a proprietary query language for writing custom metrics. For more information, see MAQL - Analytical Query Language.

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

Executive Overview Dashboard

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

    Combo chart showing data from multiple years

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.

Metric #

Metric Name

MAQL Formula

1

Created Tickets - Last Year

CONFIX STYLE=code:SELECT # Tickets WHERE Year(Created) = PREVIOUS

2

Created Tickets - This Year

CONFIX STYLE=code:SELECT # Tickets WHERE Year(Created) = THIS

3

Average Tickets Created - Last Year

CONFIX STYLE=code:SELECT (SELECT # Tickets BY ALL IN ALL OTHER DIMENSIONS WHERE Year(Created) = PREVIOUS WITHOUT PF) / 12

4

Average Tickets Created - This Year

CONFIX STYLE=code:SELECT (SELECT # Tickets BY ALL IN ALL OTHER DIMENSIONS WHERE Year(Created) = THIS WITHOUT PF) / (SELECT COUNT(Month(Created),Ticket Id) BY ALL OTHER WHERE Year(Created)=THIS WITHOUT PF)

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

SELECT (SELECT # Tickets BY ALL IN ALL OTHER DIMENSIONS WHERE Year(Created) = PREVIOUS WITHOUT PF) / 12


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.

You can combine the keywords WITHOUT PF and BY ALL IN ALL OTHER DIMENSIONS to derive a single, consistent calculation of a metric that can be referenced throughout the report.


Metric 4 - Average Tickets Created - This Year

SELECT (SELECT # Tickets BY ALL IN ALL OTHER DIMENSIONS WHERE Year(Created) = THIS WITHOUT PF) / (SELECT COUNT(Month(Created),Ticket Id) BY ALL OTHER WHERE Year(Created)=THIS WITHOUT PF)


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) BY ALL OTHER WHERE Year(Created)=THIS WITHOUT PF)

  • 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:

Type

Items to Include

Metrics (What pane)

Created Tickets - Last Year, Created Tickets - This Year, Average Tickets Created - Last Year, Average Tickets Created - This Year

Attributes (How pane)

Month (Created)

Filters (Filter pane)

Month (Created) isn't "(empty value)"

We recommend that you include this filter. The date hierarchy includes all months (Jan-Dec) plus an "empty value" month for cases in which no date appears. Including this filter ensures that no extra entry is added in your report for these empty values

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

       Advanced report configuration pane

  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.

Percent of Tickets Solved report

This headline report utilizes the following % Solved custom metric: 

SELECT (SELECT # Solved Tickets WHERE Year (Solved)=2011) / (SELECT # Tickets WHERE Year(Created)=2011)

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

SELECT # Solved Tickets WHERE (SELECT Full Resolution Time (hrs) [Avg] BY Ticket Id) < 24

"Current Year Backlog" Headline Report

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

Current Year Backlog report
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.

You can add drill paths to your report to create links between metric and attribute values and detailed reports that are pre-filtered based on the selection. See Drilling into Reports.

Powered by Atlassian Confluence and Scroll Viewport.