Skip to Content

Creating dashboards with transformations

As an administrator or editor, create dashboards by using advanced functions. You can use these functions to filter the column values in ascending or descending order, calculate the difference in values of two columns and so on. 

BMC Helix Dashboardsprovides a set of out-of-the-box advanced functions. However, you can also use the existing capabilities of BMC Helix Dashboards to configure a set of advanced functions for your dashboards.

Related topics

Creating-and-customizing-dashboards
Supported-database-functions

Out-of-the-box advanced functions

To access the Advanced functions:

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that you want to edit.
  3. Select Transform data > Advanced functions.

image-2024-7-1_15-39-20.png

The following table lists the out-of-the-box advanced functions that can be configured:

Function name

Description

Procedure

Accumulative percentage

Displays a running percentage for the values in the field. The maximum value is 100%.

To display the accumulative percentage in a column:

  1. From the Function name list, select Accumulative percentage.
  2. Select the appropriate field name.
    A new column is added to your dashboard that displays the accumulative percentage of the column values. 

You can display the running percentage for values in a field by a cut-off value. To display the running percentage by cut-off value:

  1. Open the dashboard that you want to edit and add the Accumulative percentage advanced function.
  2. Click Add transformation and select Filter data by values.
  3. In the Field box, select Accumulative percentage.
  4. In the Match box, select Is lower or equal. 

  5. In the Value box, specify the cut-off percentage value.
    The accumulative percentage values are filtered based on the specified cut-off value.

    Warning

    Important

    The Include filter returns the value that matches with the specified condition. The Exclude filter  returns the value that does not match with the specified condition. 

Accumulative total

Displays the accumulative total of the values in the table.

To display the accumulative total value in a column:

  1. From the Function name list, select Accumulative total.
  2. Select the appropriate field name.
    A new column is added to your dashboard that displays the accumulative total value of the columns. 

Delta from last N

Displays the calculated difference between the current row and another row.

To display the calculated difference:

  1. From the Function name list, select Delta from last N.
  2. Select the appropriate field name for which you want to calculate the difference in row values.
  3. In the Previous row box, specify the row number against which you want to compute the difference. 
    By default, this box is set to 1, which means that the difference between values is calculated using the previous row. If you enter 2 in this field, the previous-to-previous row is used in the calculation.
  4. If you want to see the calculated difference in percentage, set Percentage to Yes.
    By default, Percentage is set to No.
    A new column is added to your dashboard that displays the calculated difference between two rows.

Dynamic Fields Formatter

Displays the dynamic content in JSON format or as key-value pairs.

To display the dynamic content in JSON format or as key-value pairs:

  1. Open the dashboard that you want to edit.
  2. Click Add transformation, and select Dynamic Fields Formatter.
  3. In the Field box, select Dynamic Content.
  4. In the Format dynamic field as box, select Key-Value or JSON.

To view the JSON content in tabular format, use the Extract fields transformation.

Percentage against column

Displays a percentage ratio of values in the selected column compared to another column.

To display the percentage ratio of values in a column:

  1. From the Function name list, select Percentage against column.
  2. Select the appropriate field names.
    A new column is added to your dashboard that displays the percentage ratio against the column value.

Percentage against initial value

Displays the percentage of a value compared to the initial value in the column.

To display the percentage against the initial value in a column:

  1. From the Function name list, select Percentage of initial value.
  2. Select the appropriate field name.
    A new column is added to your dashboard that displays the percentage against initial value.

Percentage against maximum value

Displays the percentage of values in the column compared to the maximum value available in the column.

To display the percentage against maximum value in a column:

  1. From the Function name list, select Percentage against maximum value.
  2. Select the appropriate field name.
    A new column is added to your dashboard that displays the percentage against maximum value.

Percentage change against column

Displays the percentage difference of the selected field against a value in another column.

To display the percentage difference of two fields in a column:

  1. From the Function name list, select Percentage change against column.
  2. Select the appropriate field names.
    A new column is added to your dashboard that displays the percentage difference.

Percentage against total value

Displays the percentage of a field value against the total value of the column.

To display the percentage against total value:

  1. From the Function name list, select Percentage against total value.
  2. Select the appropriate field name.
    A new column is added to your dashboard that displays the percentage against total value.

Percentile

Displays the percentile of a field against the complete dataset.

Let's say you are a tenant administrator who wants to view the duration of 85% of the work orders (85th percentile). You can use the Percentile advanced function to calculate the duration. For example, if 85th percentile returns 7 days, that means 85% of work orders took 7 days or less to be completed.

To display the percentile:

  1. From the Function name list, select Percentile.
  2. Select the appropriate field name.

  3. Enter the appropriate percentile value.
    You can enter any value between 1 and 99. A row with the appropriate percentile value appears. 

    Warning

    Important

    If the dataset doesn't contain a value for the specified percentile, BMC Helix Dashboards displays the data available for the nearest percentile value which is lesser than the specified value.

Top N Rank

Displays the top values for the selected field. Use this function to list values according to their ranks. The value with the highest rank is listed first.

Example:

You have a tabular data that shows CPU usage of hosts. You can use the Top N Rank advanced function to identify the hosts with high CPU consumption. 

To list values according to ranks:

  1. From the Function name list, select Top N Rank.
  2. From the Field name list, select the column name for which you want to sort values.
  3. In the Rank value field, specify the number of top value rows that you want to view. 
  4. In the Alias field, specify the name to be used to change the display name of the column. 

To create dashboards with configurable advanced functions

You can configure advanced functions by using BMC Helix Dashboards. Let's say you are a tenant administrator who wants to calculate the sum of the incident numbers for the most recent month. From the Transform data list, click Add field from calculation to add the incident numbers.

image-2024-7-1_15-38-7.png

The following table lists the set of configurable advanced functions:

Function name

Description

Procedure

Filter column values by ranks

Ascending rank

Displays the column values in ascending order.

Filter the column values in ascending order by using the rank() function in DBFN (database functions).

For example, use the following query in the SQL editor to display data in ascending order:

SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#asc','#KWD#)') as RANK ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by Status

Descending rank

Displays the column values in descending order.

Filter the column values in descending order by using the rank() function in DBFN.

For example, use the following query in the SQL editor to display data in descending order:

SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') as RANK ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by Status

Top 10 rank

Displays the highest 10 values in the column

Display the highest 10 values by using the rank() function in DBFN.

For example, use the following query in the SQL editor to display the highest 10 values:

select * from (SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') as rnk1 ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by T1.Status) as T1
where rnk1 <=10

Top N rank

Displays the number of highest values as requested by the user for that column.

Display the number of highest values by using the combination of a variable and the rank() function in DBFN. The returned result should be less than or equal to the value specified in the variable.

To create a variable:

  1. Open a dashboard and click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation bar, click Variables.
  3. On the Variables page, click New.
  4. Specify the following options:
    1. In the Name and Label fields, type Rank.
    2. In the Type field, select Text box.
    3. In the Default value box, enter any value. The returned result will be less than or equal to this value.
  5. Click Update.

Specify the newly created variable in the SQL query. For example, specify the following query in the SQL editor to display the number of highest values as requested by the user:

select * from (SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') as rnk1 ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by T1.Status) as T1
where rnk1 <= $Rank

Bottom 10 rank

Displays the lowest 10 values in the column.

Display the lowest 10 values by using the rank() function in DBFN.

For example, use the following query in the SQL editor to display the lowest 10 values:

select * from (SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#asc','#KWD#)') as rnk1 ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by T1.Status) as T1
where rnk1 <=10

Bottom N rank

Displays the number of lowest values as request by the user for that column. 

Display the number of lowest values by using the combination of a variable and the rank() function in DBFN. The returned result should be less than or equal to the value specified in the variable.

To create a variable:

  1. Open a dashboard and click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation bar, click Variables.
  3. On the Variables page, click New.
  4. Specify the following options:
    1. In the Name and Label fields, type Rank.
    2. In the Type field, select Text box.
    3. In the Default value box, enter any value. The returned result will be less than or equal to this value.
  5. Click Update.

Specify the newly created variable in the SQL query. For example, specify the following query in the SQL editor to display the number of lowest values as requested by the user:

select * from (SELECT
DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#asc','#KWD#)') as rnk1 ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by T1.Status) as T1
where rnk1 <= $Rank

Add, subtract, multiply, and divide the column values

Addition of column values

Displays the addition of two selected column values. 

To calculate the addition of two selected column values:

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Select Transform data > Add field from calculation and do the following actions:
    • In the Mode field, select Binary operation.
    • In the Operation fields, select the names of the fields on which you want to perform the calculation along with the addition symbol.
    • (Optional) In the Alias field, enter the name of the new field.

Difference of column values

Displays the difference of two selected column values. 

To calculate the difference of two selected column values:

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Select Transform data > Add field from calculation and do the following actions:
    • In the Mode field, select Binary operation.
    • In the Operation fields, select the names of the fields on which you want to perform the calculation along with the subtraction symbol.
    • (Optional) In the Alias field, enter the name of the new field.

Multiplication of column values

Displays the multiplication of two selected column values.

To calculate the multiplication of two selected column values:

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Select Transform data > Add field from calculation and do the following actions:
    • In the Mode field, select Binary operation.
    • In the Operation fields, select the names of the fields on which you want to perform the calculation along with the multiplication symbol.
    • (Optional) In the Alias field, enter the name of the new field.

Division of column values

Displays the division of two selected column values.

To calculate the division of two selected column values:

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Select Transform data > Add field from calculation and do the following actions:
    • In the Mode field, select Binary operation.
    • In the Operation fields, select the names of the fields on which you want to perform the calculation along with the division symbol.
    • (Optional) In the Alias field, enter the name of the new field.

Other functions

Percentage of total

Displays the percentage of the attribute when compared to the total added value of the attribute for the entire dataset.

In the BMC Cross-tab plug-in, enable the following options:

  • Show Percentage of the Total row—Show or hide the percentage of the total row.
  • Show Percentage of the Total column—Show or hide the percentage of the total column.

Natural Logarithm

Displays the base logarithm of the values of a given field/column.

Display the base logarithm of the field values by using the DBFN function.

For example, use the following query in the SQL editor:

DBFN('log','2.0',`number`)

Null to zero

Displays all the NULL values found in the field with zero (0).

In the Table visualization plug-in, select Standard options and set No Value to 0 to display all the NULL values as 0.

Remove values

Removes values either above or below a defined threshold.

In the Table visualization plug-in, define the minimum and maximum thresholds under the Thresholds option.

Top N with ties

Displays the top values for the selected field with provision for tied values. If there are multiple records per ranking, this function restricts it to N total rankings. 

Display the top values for selected fields by using the combination of a variable and the DENSE_RANK() function in DBFN function.

To create a variable:

  1. Open a dashboard and click Dashboard settings dashboard_settings_icon.png.
  2. In the navigation bar, click Variables.
  3. On the Variables page, click New.
  4. Specify the following options:
    1. In the Name and Label fields, type Rank.
    2. In the Type field, select Text box.
    3. In the Default value box, enter any value. The returned result will be less than or equal to this value.
  5. Click Update.

Specify the newly created variable in the SQL query. For example, specify the following query in the SQL editor:

select * from (SELECT
DBFN('dense_rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') as rnk1 ,
count(`HPD:Help Desk`.`Incident Number`) AS inccount,
`HPD:Help Desk`.`Status` as Status
FROM `HPD:Help Desk`
group by T1.Status) as T1
where rnk1 <= $Rank

The following table describes how to interpret the DBFN functionalities mentioned in the above SQL queries:

Argument

Description

rank;over;order by;count

Indicates all parts in the function.

2

Indicates the number of arguments in the first part: (, and )

The opening and closing brackets are prefixed with the string #KWD#, for example, #KWD#( and #KWD#).

1

Indicates the number of arguments in the next part over, which has only one argument: ( which is prefixed by #KWD#.

5

Indicates the number of arguments in the next part order by count, which has five arguments (, Incident_Number, ), desc, and ). The opening and closing brackets are prefixed with the string #KWD#; for example, #KWD#( or #KWD#).

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix Dashboards 25.4