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.
      Screen Shot 2014-03-28 at 12.25.10 PM.png
    2. Click New.
      Screen Shot 2014-03-28 at 12.26.34 PM.png
    3. Enter a Name and Description.
      Screen Shot 2014-03-28 at 12.23.14 PM.png
    4. Select the Service Request data source.  Tip: Find “CRMSRQHB” to quickly locate this data source.
      Screen Shot 2014-03-28 at 12.28.33 PM.png
    5. Make sure Calculated Key Figure is selected, then click Next.
      Screen Shot 2014-03-28 at 12.30.41 PM.png
    6. Under Key Figure Type: Basic, double-click Counter to add it to the formula field, then click Next.
      Screen Shot 2014-03-28 at 12.31.44 PM.png
    7. Under Exception Aggregation, sety the Aggregation Type to Counter for All Detailed Values.
      Screen Shot 2014-03-28 at 12.35.18 PM.png
    8. Click the Characteristic field, expand Dimension: Created / Changed Information, and select Created On.
      Screen Shot 2014-03-28 at 12.36.30 PM.png
    9. Click Finish and then Close.
      Screen Shot 2014-03-28 at 12.40.51 PM.png

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

    1. Click New.
      Screen Shot 2014-03-28 at 12.26.34 PM.png
    2. Enter a Name and Description.
      Screen Shot 2014-03-28 at 12.49.03 PM.png
    3. Select the Service Request data source.  Tip: Find “CRMSRQHB” to quickly locate this data source.
      Screen Shot 2014-03-28 at 12.28.33 PM.png
    4. Make sure Calculated Key Figure is selected, then click Next.
      Screen Shot 2014-03-28 at 12.30.41 PM.png
    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.
      Screen Shot 2014-03-28 at 12.51.03 PM.png
    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.
      Screen Shot 2014-03-28 at 12.56.41 PM.png
    2. Click New, Report.
      Screen Shot 2014-03-28 at 1.11.48 PM.png
    3. Enter a Name and Description.
      Screen Shot 2014-03-28 at 1.17.54 PM.png
    4. Select the Service Request data source.  Tip: Find “CRMSRQHB” to quickly locate this data source.
      Screen Shot 2014-03-28 at 1.21.04 PM.png
    5. Click Next.
    6. Click Deselect All to remove all the default key figures.
      Screen Shot 2014-03-28 at 1.24.13 PM.png
    7. Check the boxes to select the Counter, Count of Days in Report, and Avg. Tickets Created per Weekday key figures.
      Screen Shot 2014-03-28 at 1.26.08 PM.png
    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)
        Screen Shot 2014-03-28 at 1.41.30 PM.png
    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).
        Screen Shot 2014-03-28 at 1.44.29 PM.png
      3. Click Next and set a Default Value for the Created On characteristic.Screen Shot 2014-03-28 at 1.51.21 PM.png
    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.
    Screen Shot 2014-03-28 at 1.59.46 PM.png
  2. For the best end-user experience, change the Start Options to Show Selection Area and Start Report.
    Screen Shot 2014-03-28 at 2.10.17 PM.png
  3. Click Go.
    Screen Shot 2014-03-28 at 2.14.08 PM.png
  4. Click Settings, Key Figures.
    Screen Shot 2014-03-28 at 2.19.58 PM.png
  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
    Screen Shot 2014-03-28 at 2.23.58 PM.png
    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.
    Screen Shot 2014-03-28 at 2.25.04 PM.png
  7. Under Dimension: Created / Changed Information, expand the Created On characteristic and check the Display in Report box for Weekday.
    Screen Shot 2014-03-28 at 2.30.15 PM.png
  8. Click Ok.
  9. In the Columns and Rows panel, under Not Currently Shown, click Weekday (Created On) and select Move to Rows.
    Screen Shot 2014-03-28 at 2.34.00 PM.png
  10. The report should now look similar to the following image.
    Screen Shot 2014-03-28 at 2.36.40 PM.png
  11. Click Save, Save As.
    Screen Shot 2014-03-28 at 2.38.22 PM.png
  12. Give the report view a Name, Description, and initial Selection Name then click Ok.
    Screen Shot 2014-03-28 at 3.01.42 PM.png
  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.
    Screen Shot 2014-03-28 at 3.07.21 PM.png
  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.
    Screen Shot 2014-03-28 at 3.09.38 PM.png
  4. Click Save.
    Screen Shot 2014-03-28 at 3.11.52 PM.png

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply