Adding calculated results to your reports

Applies to NAM 2018

Use calculated results in DMI reports much like you would use calculated columns in a spreadsheet: based on the information you have in existing columns, use a formula to calculate and display additional information in further columns.

Technical notes

  • Calculated columns are evaluated after merging Subject and Benchmark data, so you can use Subject and Benchmark columns in evaluated columns.
  • Calculated columns are evaluated before pivoting the result.
  • Calculated columns can be used in coloring, in row filter expressions (see the Result display tab), and for passing filters in drilldowns (if the renderer is a string).
  • You cannot pass a filter value directly to an evaluated column.

Mathematical expressions

For example, create a table with three columns. You have two metrics:

  • Client bytes - a count of client bytes
  • Server bytes - a count of server bytes

From them, we want to calculate and display a third value:

  • Total bytes (the sum of client and server bytes)
  1. On the NAM Server, select Tools ► DMI  ► New report to open DMI with an empty report section ready for you to define.

  2. Set Display name to the string that will be displayed at the top of the section.
    Example: My calculated column.

  3. Select a Section type.
    Example: Table.

  4. On the Data tab, select the input data for this section.

    1. Click the edit control on the Subject line to display the editing controls for the required data input for this report section.

      edit

      You could now select a different data view from the Data views list, but that's not needed for this example.

    2. Click Metrics to show all available metrics.

    3. Find and click (select) the two metrics on which we are going to base our calculated metric.

      • Client bytes
      • Server bytes
        If you have selected them, they should both be displayed under Selected metrics.
  5. On the Result display tab, click Add new calculated column.
    An edit window is displayed.

  6. Describe how to calculate and display the calculated column.

    • Name is a column header suitable for adding client bytes and server bytes. Example: "Total bytes (calculated)".

    • Rendering is the output type. Example: "Bytes".

    • Expression describes how to calculate the value to display in this column. Example:
      <Client bytes> + <Server bytes>

      • You are restricted to metrics you have added to the report on the Data tab.
      • If you make a syntax error or use metrics that you have not added to the report, an error message is displayed.
      General syntax for formulas

      Type a metric name in < and > brackets, like <Client bytes>.
      You can use the following mathematical operators on them: + - * / ( )

    • Description is text that will not be displayed to report users. Like a comment in code, this is a comment to you, or to other people who edit this report definition, explaining what this calculated value is for and how it works.

    • Help is text that will be displayed to report users.

  7. Click OK to save your changes.
    The new column should be listed on the Result display tab.
    Click the edit link next to the name of the new column if you need to open it again for additional editing.

  8. Save your report.

  9. Click Display Report to see your report.
    Using the example settings above, we created a one-section report with a three-column table, where the third column is calculated from the first and second columns. You should see something like this in your browser (we have highlighted the calculated column):

    edit

    Note

    We discuss this functionality in terms of tables and columns, and the button is labeled Add new calculated column, but you are not limited only to tables and columns. You can, for example, display the same data as a pie chart, where one slice of the pie is calculated based on other slices of the pie.

    1. Set Section type to Chart.
      The Chart settings tab is added to the row of tabs displayed.

    2. Open the Chart settings tab.

    3. Set Title to My calculated pie.

    4. Set Type to Pie.

      Using the example settings above, you should now see something like:

      edit

      The example is not a very useful pie chart, but it shows you how to make a chart with calculated results.

      For the rest of the examples, we will stick to tables, but remember that you can change the display of your results to suit your needs.

f_MIN and f_MAX

Use f_MIN and f_MAX to find the minimum and maximum values in a set of numbers:
<f_MIN(ArrayExpression)> returns the minimum value in ArrayExpression. <f_MIN(ArrayExpression)> returns the maximum value in ArrayExpression.

Conditional statements

Conditional statement f_IF

<f_IF(BooleanExpression, ReturnIfTrue, ReturnIfFalse)>

Instead of adding Client bytes and Server bytes and showing the results in the third column, we now want the third column to display the string "Client bytes" or the string "Server bytes" depending on which value is greater.

  1. If you changed Section type to Chart earlier, set it back to Table for the rest of the examples.

  2. On the Result display tab, click Add new calculated column.
    An edit window is displayed.

  3. Describe how to calculate and display the calculated column.

    • Name in this example: "Which is greater, client or server bytes?"
      The column heading asks a question that each row will answer.

    • Rendering in this example: "String"
      We are going to show a simple string.

    • Expression in this example uses the f_IF (logical IF function) to compare our two metrics and say which one is greater. Copy and paste this expression into the edit box:

      <f_IF( <Client bytes> > <Server bytes>, 'Client bytes', 'Server bytes' )>

      The expression says

      • if <Client bytes> is greater than <Server bytes>
      • then show the string 'Client bytes' (not the value of that metric) in this column
      • else show the string 'Server bytes' (not the value of that metric) in this column

      where then and else are implied by the positions of the if/then/else terms in the expression, and where you use commas to separate the if/then/else terms.

  4. Click OK to save your changes.

  5. Save your report.

  6. Click Display Report to see your report.
    Using the example settings above, we added a column called "Which is greater, client or server bytes?" that shows the string "Client bytes" if <Client bytes> > <Server bytes> is true.

    edit

    As you may have noticed, we did not account for cases in which the two values are equal. We do that in the next example.

Conditional statement f_SWITCH

<f_SWITCH(bool expression1, value expression1, .... bool expressionN, value expressionN, default value)>

Now we are going to use a switch statement instead of a simple if/then/else statement to determine what will be displayed in our calculated column.

  1. On the Result display tab, click Add new calculated column.
    An edit window is displayed.

  2. Describe how to calculate and display the calculated column.

    • Name in this example: "Which is greater (or are they equal?)"
      The column heading asks a question that each row will answer.

    • Rendering in this example: "String"
      We are going to show a simple string again.

    • Expression in this example uses the f_IF (logical IF function) to compare our two metrics and say which one is greater. Copy and paste this expression into the edit box:

      <f_SWITCH( <Client bytes> > <Server bytes>, 'Client bytes', <Server bytes> > <Client bytes>, 'Server bytes', 'Equal' )>

      The expression says

      • if <Client bytes> is greater than <Server bytes> then show the string 'Client bytes' in this column
      • if <Server bytes> is greater than <Client bytes> then show the string 'Server bytes' in this column
      • else show the string 'Equal' in this column

      You can add as many of those implied if terms as you like. The implied else term at the end takes care of every case that doesn't match one of the implied if terms that preceded it.

  3. Click OK to save your changes.

  4. Save your report.

  5. Click Display Report to see your report.
    Using the example settings above, we added a column called "Which is greater (or are they equal?)" to show one of three possible strings ("Client bytes" or "Server bytes or Equal") depending on the relative values of the two metrics. In this example, the value of Client bytes is always greater.

    edit

String functions

<f_LEN(StringExpression)> returns the length of StringExpression.

f_SUBSTRING(StringExpression, beginIndex, length) returns a substring of string expression starting at beginIndex whose number of characters is length.

<f_CHARINDEX(SubStringExpression, StringExpression [, start_pos = 0 ])> returns the location of SubStringExpression in StringExpression. It returns -1 if SubStringExpression is not in StringExpression.

f_SUBSTRING example

<f_SUBSTRING(<string>, start, length)>

Use f_SUBSTRING to get part of a string.

The syntax is
<f_SUBSTRING(<string>, start, length)>
where

  • start = where to start in the string, expressed as the number of characters from the start of the string, such that 0 means the first character (0 characters from the start).
  • length = the length of the substring you want to extract.

In this example:
<f_SUBSTRING(<Software service>, 0, 10)>
we get the first 10 characters of the Software service dimension, which is a string. If the string is shorter than the requested length, we get the entire string, not an error.

Let's create a new report section that shows the dimension Software service in the first column and the first 10 characters of Software service in the second column.

  1. With your report still open in DMI, select Report definition from the menu, create a new section and open it for editing.

    1. Click a button in Layout Actions.
  2. Set Display name to the string that will be displayed at the top of the section.
    In this example, let's use Substring.

  3. Select a Section type.
    In this example, let's use Table.

  4. On the Data tab, select the input data for this section.

    1. Click the edit control on the Subject line to display the editing controls for the required data input for this report section.

      edit

    2. Click Dimensions to show all available metrics.

    3. Find and click (select) the Software service dimension.

  5. On the Result display tab, click Add new calculated column.
    An edit window is displayed.

  6. Describe how to calculate and display the calculated column.

    • Name is a column header suitable for adding client bytes and server bytes: "First 10 characters of software service".
    • Rendering is the output type: "String".
    • Expression describes how to calculate the value to display in this column:
      <f_SUBSTRING(<Software service>, 0, 10)>
  7. Click OK to save your changes.

  8. Save your report.

  9. Click Display Report to see your report.
    Using the example settings above, we display the entire software service name in one column and the first 10 characters of the same software service name in the second column. If the string you look at ("Software service" in this example) is shorter than the requested length, you get the entire string rather than an error.

    edit

    Don't discard your report. The next section builds on it.

f_LEN example

<f_LEN(StringExpression)>

Using the same report we used to extract a substring, let's add a column to show the number of characters in the original string.

  1. Open the report section back up for editing, Add new calculated column, and add another column for string length.

    • Name is a column header suitable for adding client bytes and server bytes: "Length of software service".
    • Rendering is the output type: "String". (Showing this numeric result as a string rather than as an integer makes it left-justified.)
    • Expression describes how to calculate the value to display in this column:
      <f_LEN(<Software service>)>
      where we are just showing the length of the string.
  2. Click Display Report to see your report again.
    Using the example settings above, we see an additional column showing string length.

    edit

Calculated columns from calculated column

Suppose we have operations such as BULK INSERT WebServerLog22 FROM … and we want to display the table name.

  1. Add column isBulk with content <f_CHARINDEX('BULK INSERT ', <Operation>)> == 0
  2. Add Integer column fromPosition with content <f_CHARINDEX(' FROM ', <Operation>)>
  3. Add Table name column with content <f_IF(<isBulk> && <fromPosition> > 0, <f_SUBSTRING(<Operation>, 12, <fromPosition> - 12)>, '-')> (12 is length 'BULK INSERT ')

We can pass the table name as a filter to another report.

Remember that columns are calculated from top in bottom. If you change the order of the columns, the value of the calculated column will not be calculated and a null value is used instead of the value that will be calculated later.

The previous example can be done in one step using the REGEXP function:

<f_REGEXP(<Operation>, 'BULK INSERT ([^ ]*) FROM .*', '{1}')>

Coloring using benchmark metrics

Suppose we want to color Application by Application performance if the number of operations > 5, else we would like to color by Application performance from synthetic traffic.

  1. On the Data tab, for subject data (data view Software service, operation, and site data), choose:
    • Dimensions: Application
    • Metrics: Operations and Application performance
  2. On the Data tab, for benchmark data (data view Synthetic and sequence transaction data), choose:
    • Dimensions: Application
    • Metrics: Application performance
  3. On the Result display tab, click Add new calculated column and set:
    • Name: Performance
    • Rendering: Percent
    • Expression: <f_IF(<Operations> > 5, <Application performance>, <Application performance benchmark>)>
  4. On the Result display tab, Application row, Color rendering column, click None and set:
    • Mode: Custom
    • Metric: Performance [%]
  5. On the Result display tab, Show column, set everything to No except Application.
    edit

Displaying an icon

This one depends on setting Rendering to SVG Icon and providing an expression to display a checkmark or abort icon in a column.

For example, this report uses dimension HTTP Method and an expression that displays the checkmark icon if communication was done using the GET method or the abort icon otherwise.

  • Rendering: SVG Icon
  • Expression: <f_IF(<HTTP Method> == 'GET', 'Checkmark', 'Abort')>

To change the appearance (size, color, or alignment) of the icon, click the Custom attributes column on the Result display tab and set the properties. For example:

  • Size: 1.5
  • Color: rgba(255, 0, 0, 0.2)
  • Align: Left

Displaying a breakdown

This one depends on setting Rendering to Breakdown and providing information on what the breakdown should show.

If you are showing a report with columns for Client bytes and Server bytes, for example, you want add a calculated third column to show a breakdown of these two values:

  • Rendering: Breakdown
  • Count: 2
    The number of components in the breakdown.
  • Type: Bytes
    The data type of the components in the breakdown.
  • Expression: [<Client bytes>, <Server bytes>]
    The report user can click the breakdown to see a popup showing the values for each component of the breakdown.

Regular expressions

Use f_REGEXP to handle regular expressions.

<f_REGEXP(source, findRegexpExpression, printFormat)>

Example: <f_REGEXP(<Operation>, 'BULK INSERT ([^ ]*) FROM .*', 'Table name: {1}')>

Null handling with f_ISNULL and f_REPLACENULL

<f_ISNULL(a)> returns true if parameter a is null.

<f_REPLACENULL(a,b)> returns the first non-null value. It is equivalent to <f_IF(<f_ISNULL(a)>, b, a)>. In Java, the equivalent would be (a != null) ? a : b

Troubleshooting calculated results

Unusual output

If you get strange output in your calculated results, go back and make sure you have selected an appropriate Rendering value.

  1. On the Result display tab, find the new column in the list (under Dimension/Metric name) and click Edit next to the name.
  2. In the edit window, check Rendering to make sure the type matches the expected output type.
    Change it as needed.
  3. Click OK to save your changes.
  4. Click Display Report to see your report.

Editing tip

If it helps you during editing, you can write an expression over several lines like this:

<f_SWITCH(
<Client bytes> > <Server bytes>, 'Client bytes',
<Server bytes> > <Client bytes>, 'Server bytes',
'Equal')>

but the parser will convert it to this

<f_SWITCH(<Client bytes> > <Server bytes>, 'Client bytes', <Server bytes> > <Client bytes>, 'Server bytes', 'Equal')>

the next time you open it for editing, so keep a copy in a separate text file if you want to preserve the multi-line formatting for some reason.