Demo

Help center

View helpful articles, tutorials and FAQs on the set-up and configuration

of the FocalScope omnichannel suite, according to best practice.

How to create new custom data fields in FocalScope reports

FocalScope's default reports can be customized extensively with Excel's pivot tables and charts. However, sometimes the default data fields in a FocalScope report cannot be arranged to represent the data in the pivot table in the desired manner. In such cases it may be required that data from two (or more) columns be combined and processed according to a certain logic. This will create a new data field that can be inserted into pivot tables and charts. This article details how to create customized data fields to extend the functionality of FocalScope's default reports.

Prerequisites

When I open an Excel live report I get an 'Access denied' error

Tutorial

An example of an undesired result in a pivot table

In Figure 1 we can see a shortcoming of a custom report which uses only default data fields. In the highlighted column, it is clear that ticket substates, such as Processing, Cancelled, Pending Invoice, etc., are appearing under the main ticket states (Open, Closed, On Hold, and Deleted). Categories are also displayed in the same column as the ticket states and substates; this whole approach makes the report difficult to read. No matter how the data fields are arranged in the pivot table, the desired result cannot be obtained with only the default data fields.



Figure 1 - Incorrectly displayed ticket states and substates


Creating a custom data field in a FocalScope report

  1. To create a custom data field, click on the [Raw Data] worksheet (Highlighted in red)
  2. At the end of the report columns, create a new label and call it [Combined Ticket States]
  3. Select the first cell of the new [Combined Ticket States] field and click the [Insert Function] button (highlighted in blue)
  4. Continued in next section...



    .

    Figure 2 - Creating a new custom field



    1. In the [Insert Function] window, enter IF in the [Search for a function] field and click [Go]
    2. From the [Select a function] field, select [IF] and click [OK]
    3. Continued in next section...



      Figure 3 - Defining the logic function for the custom field



      1. In the [Function Arguments] window:
        1. At the end of the [Logical_test] field, click the [Function Arguments] button and select the first cell of the [Ticket Substate] field
        2. Add ="" directly to the end of RC[-2], which was added to the field in the previous step
        3. For the [Value_if_true] field, select the first cell of the [Ticket State] field
        4. For the [Value_if-false] field, select the first cell of the [Ticket Substate] field
        5. What this function is basically doing is the following: If the Ticket Substate value is empty, the Ticket State value is used. If the Ticket Substate value is not empty, the Ticket Substate value is used
        6. Click [Ok] to save and activate the function
      2. Continued in next section...



        Figure 4 - Specifying the data sources for the custom field



        1. Double-click the highlighted corner of the cell to apply the function to all cells below it. Please note: This is necessary for the custom data field to work for the entire report



          Figure 5 - Applying the function to all cells


          Adding the new data field to pivot tables

          In order for the pivot table to read the new data field, the source for the pivot table must be updated to include the new data field.

          1. Select the pivot table that is to use the new data field
          2. In the ribbon, click [Options > Change Data Source > Change Data Source]
          3. Continued in next section...



          Figure 6 - Changing the data source for a pivot table


          1. The raw data worksheet will show with a selection box around all columns except the custom data field
          2. Press Shift+Right Arrow to extend the selection to the custom data field. The [Table/Range] field in the [Change PivotTable Data Source] pop up will automatically update
          3. Click [Ok] to save changes



            Figure 7 - Setting the data source range


            Comparing the results of using the custom data field

            With the new data field added to the pivot table's row labels, the main ticket states and ticket substates now appear under the same column, making the report much more intuitive to read at a glance.



            Figure 8 - Ticket states and substates are correctly displayed

            Additional Information



            Related articles

            Let us help

            If you have any questions, or need further support, please don’t hesitate to reach out.

            Contact us