Skip to Content

Using database functions for advanced reporting

A database function is a custom function that extends reporting capabilities while using the service management data. You can use these functions to integrate the AR System data with BMC Helix Dashboards and perform complex database transformations that are difficult to achieve by using the standard AR System workflow.

Before you begin

Before you add database functions in the SQL editor of a dashboard to meet your specific business needs, define the database functions that you want to use in the AR System JDBC:Generic Db Functions form on the AR System server.

For more information, see Defining database functions in AR System Server.

To use a database function in a query

  1. Log in to BMC Helix Portal, and open BMC Helix Dashboards.
  2. Click New dashboard.
  3. From the Query Type list, select Service Management.
  4. From the Type list, select SQL.
  5. In the SQL editor, define a SQL query by using database functions.
  6. Configure the required visualization.
  7. Save the dashboard.

Examples of database functions

The following tables describe some of the database functions:

Function nameDescriptionExample query to be used in the SQL editor
Ascending rankDisplays the column values 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

where,

  • rank;over;order by;count - Indicates all parts of 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 5 arguments (, Incident_Number, ), desc, and ). The opening and closing brackets are prefixed with the string #KWD#. For example, #KWD#( or #KWD#). 
Descending rankDisplays the column values 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 columnselect * 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() database
function. The returned result should be less than or equal to the value specified in the variable.

Create a variable and specify this 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.​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 requested by the user for that column.

Display the number of lowest values by using the combination of a variable and the rank()
database function.

Create a variable and specify this 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 a 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
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`)
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() database function.

Create a variable and 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 

CountAdvanced authoringSELECT * FROM  (select * , DBFN('COUNT;OVER;PARTITION BY;ORDER BY', 3, '#KWD#(' , '#KWD#*' , '#KWD#)' , 1, '#KWD#(', 1 , `Support Group Name`, 3, `Submit Date` , '#KWD#DESC' , '#KWD#)') as group_rank FROM `HPD:HelpDesk_CTM_ServiceCompany_SupportGroup_Join`) ranked where group_rank <= 20
LagDisplays values from the previous table rows for comparison.SELECT DBFN('lag;over;partition by;order by', 4, '#KWD#(' , `Short Description` , 1 , '#KWD#)' , 1, '#KWD#(' , 1 , `Short Description` , 2 , `Short Description` , '#KWD#)') FROM 'AR System Schema'.'Test:Form'

where,

  • 4 - Indicates the number of arguments in the first part: 
    (, Short_desc, 1, and )
    The opening and closing brackets are prefixed with the string #KWD#; for example, #KWD#( or #KWD#).
  • 1 - Indicates the number of arguments in the next part over, which has only one argument: ( which is prefixed by #KWD#.
  • 1 - Indicates the number of arguments in the next part partition by, which has only one argument Short Description.
  • 2 - Indicates the number of arguments in the next part order by, which has only two arguments: Short Description and ). The final closing bracket is prefixed as #KWD#).

 

 

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

BMC Helix Dashboards 25.3