Creating dashboards with advanced functions

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 Dashboards provides 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.

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 > Advanced functions.

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

Function nameDescriptionProcedure
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.

    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 totalDisplays 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.
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 valueDisplays 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 valueDisplays 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 columnDisplays 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.


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 list, click Add field from calculation to add the incident numbers.

The following table lists the set of configurable advanced functions:

Function nameDescriptionProcedure
Filter column values by ranks
Ascending rankDisplays 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 rankDisplays 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 rankDisplays 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 rankDisplays 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 .
  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 rankDisplays 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 rankDisplays 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 .
  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 valuesDisplays 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 > 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 valuesDisplays 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 > 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 valuesDisplays 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 > 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 valuesDisplays 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 > 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 totalDisplays 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 LogarithmDisplays 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 zeroDisplays 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 valuesRemoves 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 tiesDisplays 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 .
  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#).

Was this page helpful? Yes No Submitting... Thank you

Comments