How to: Report average tickets created by day-of-week.

[Former Member]

Businesses frequently need to understand volume by day-of-week or hour-of-day in order to plan schedules and provide adequate staffing.

This document provides steps to create a report showing the average number of tickets created per day, by the day of the week, over a specific time period.  The concepts shown here can also be easily adapted to to show the average by hour of day, by month of year, every Tuesday for the last six months, etc...

In this tutorial, we will cover three main topics:

1. Create key figures to determine the number of days in the reporting period, and to calculate the simple average.
2. Design the report to provide a simple end-user experience.
3. Create a report view so that end-users can easily consume the information.

Creating Key Figures

Part of the challenge is knowing how many of each weekday are in the selected timeframe.  For example, the "last thirty days" might contain five Mondays but only four of each of the other weekdays.  Since your users are able to change date ranges, we need to count the number of occurrences of each weekday for this unknown range in order to produce an accurate calculation.

First, create a Calculated Key Figure to Count the occurrences of each weekday in your date range.

1. Hover over the Business Analytics work center and click Design Key Figures.
   
2. Click New.
   
3. Enter a Name and Description.
   
4. Select the Service Request data source.  Tip: Find "CRMSRQHB" to quickly locate this data source.
   
5. Make sure Calculated Key Figure is selected, then click Next.
   
6. Under Key Figure Type: Basic, double-click Counter to add it to the formula field, then click Next.
   
7. Under Exception Aggregation, sety the Aggregation Type to Counter for All Detailed Values.
   
8. Click the Characteristic field, expand Dimension: Created / Changed Information, and select Created On.
   
9. Click Finish and then Close.
   

Next, create another Calculated Key Figure to calculate (Count of Tickets ÷ Count of Days in Report).

1. Click New.
   
2. Enter a Name and Description.
   
3. Select the Service Request data source.  Tip: Find "CRMSRQHB" to quickly locate this data source.
   
4. Make sure Calculated Key Figure is selected, then click Next.
   
5. Under Key Figure Type: Basic, double-click Counter to add it to the formula field, then enter a forward-slash ( / ), and then under Key Figure Type: Calculated double click Count of Days in Report (or whatever name you gave the key figure in the prior set of steps.)  The formula should appear like the following screenshot.
   
6. Click Finish and Close.

Building the Report

In this set of steps, we will design the report by choosing our data source, key figures, and a couple characteristics to display.  Then we will build a view for the data that aggregates the average tickets by each weekday. 

First, let's design the report.

1. Hover over the Business Analytics work center and click Design Reports.
   
2. Click New, Report.
   
3. Enter a Name and Description.
   
4. Select the Service Request data source.  Tip: Find "CRMSRQHB" to quickly locate this data source.
   
5. Click Next.
6. Click Deselect All to remove all the default key figures.
   
7. Check the boxes to select the Counter, Count of Days in Report, and Avg. Tickets Created per Weekday key figures.
   
8. Click Next.
9. Click Deselect All to remove all the default characteristics.
10. Check the boxes to select the following characteristics and any others you want to appear in your report.
    • Created On (under Created / Changed Information)
    • Service and Support Team (under Organization)
      
11. Click Next.
12. If desired set a default date range and support team filter:
    1. Choose Using Variable to set a default date range for the Created On characteristic.
    2. Choose Fixed Value Selection then click Set Fixed Value Selections to filter the report to one or more Service and Support Team(s).
       
    3. Click Next and set a Default Value for the Created On characteristic.
13. Click Finish.

Next, we'll create a View for the report that your end-users can use to get a simple view of the data.

1. Click Create a View for this Report in a Web Browser.
   
2. For the best end-user experience, change the Start Options to Show Selection Area and Start Report.
   
3. Click Go.
   
4. Click Settings, Key Figures.
   
5. All three key figures should display in the Selected panel.  If not, add them and re-order them into the shown logical order and click Ok. 
   
   Note:  Only the Avg. Tickets Created per Weekday field is necessary, but it's helpful to display all three fields at first to validate the math.
   
   
6. Click Add Fields.
   
7. Under Dimension: Created / Changed Information, expand the Created On characteristic and check the Display in Report box for Weekday.
   
8. Click Ok.
9. In the Columns and Rows panel, under Not Currently Shown, click Weekday (Created On) and select Move to Rows.
   
10. The report should now look similar to the following image.
    
11. Click Save, Save As.
    
12. Give the report view a Name, Description, and initial Selection Name then click Ok.
    
13. Close the report view.

Finally, we will assign the report so that our end-users can actually see it.

1. If you didn't close it earlier, the final step of the report wizard should still be available in the background.
2. Click Assign Report.
   
3. Under Work Center: Analysis, find the Service Overview work center view and check the Assign box.  Note:  The Conflict column should show a green checkmark.
   
4. Click Save.
   

Congratulations!  You've created a handy resource and workload planning report.  Your end-users will be able to view this report under the Analysis > Reports work center.