Phased rollout

 

This version of the software is currently available only to early adopter SaaS customers as the first step in our phased rollout. Click here to view an earlier version.

Creating reports with advanced database functions by using Advanced Authoring

Use the Advanced Authoring option to create reports with complex database queries. You can use complex database functions that contain multiple parts (such as over, partition by, order by) in a single function. 

Important

  • You can use Advanced Authoring for MS SQL and Oracle databases only.

  • Advanced Authoring or Freehand SQL reports do not support User Prompts & Source Filters.

To use Advanced Authoring, you must have the Admin, Corp Writer, or Report Writer role.

To create a report using Advanced Authoring

  1. On the BMC Helix ITSM: Smart Reporting Admin Console, click the + button, and select Report.

  2. In the New Report window:

    1. Enable Advanced Authoring.

    2. From the Author Method list, select Freehand SQL.

    3. From the Data Source list, select a data source.

    4. Click Create Analysis button at the bottom of the New Report window.

  3. To write complex queries, in the SQL Query editor, perform the following steps:

    1. Enter the SQL query for creating the report.

      Make sure you give an alias in the SELECT statement. For example:

      SELECT DISTINCT
      `HPD:Help Desk`.`Incident Number` AS C1,
      `HPD:Help Desk`.`Status` AS C2
      FROM `AR System Schema`.`HPD:Help Desk`

      For examples of complex queries, see the examples below. 

    2. Click Validate.

    3. Click Save.

The calculation is available in an SQLFields  folder on the Data page, and the report data is displayed on the right.

Examples of how to execute complex database functions having multiple parts

The following examples show how you can execute complex functions by using the Advanced Authoring option.

Example 1

LAG(`Short_desc` , 1) over (partition by `Short_desc` order by `Short_Desc`)

To run this function, enter the following query in the SQL Query editor:

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'

The following table describes how to interpret the DBFN functionality:

Argument

Description

lag;over;partition by;order by

Indicates all parts in the function.

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#).


The flow continues as shown in the table for all the arguments in a function that has multiple parts.

Example 2

RANK() over(order by COUNT(Incident_Number) desc) RANK

To run this function, enter the following query in the SQL Query editor:

SELECT DBFN('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') RANK FROM 'AR System Schema'.'HPD:Help Desk'

The following table describes how to interpret the DBFN functionality:

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 5 arguments (, Incident_Number, ), desc, and ). The opening and closing brackets are prefixed with the string #KWD#. For example, #KWD#( or #KWD#).

Example of a Union subquery

Consider the following example of a Union subquery that you can construct by using the Advanced Authoring option:

SELECT DISTINCT `HPD:Help Desk`.`Incident Number` AS 'TicketNumber', `HPD:Help Desk`.`Assigned Support Company` AS 'AssignedSupporCompany', `HPD:Help Desk`.`Status` AS 'TicketStatus', `HPD:Help Desk`.`Priority` AS 'TicketPriority' FROM `AR System Schema`.`HPD:Help Desk` UNION SELECT DISTINCT `PBM:Problem Investigation`.`Problem Investigation ID` AS 'TicketNumber', `PBM:Problem Investigation`.`Assigned Support Company` AS 'AssignedSupporCompany', `PBM:Problem Investigation`.`Investigation Status` AS 'TicketStatus', `PBM:Problem Investigation`.`Priority` AS 'TicketPriority' FROM `AR System Schema`.`PBM:Problem Investigation`

Example of an Append subquery

Consider the following example of an Append subquery that you can construct by using the Advanced Authoring option:

SELECT HPDHELPDESK.TicketNumber, HPDHELPDESK.TicketAssignee, HPDWORKLOG.Summary FROM ( SELECT DISTINCT `HPD:Help Desk`.`Incident Number` AS 'TicketNumber', `HPD:Help Desk`.`Assignee` AS 'TicketAssignee' FROM `AR System Schema`.`HPD:Help Desk` ) HPDHELPDESK LEFT OUTER JOIN ( SELECT DISTINCT `HPD:Help Desk`.`Incident Number` AS 'IncNumber', `HPD:WorkLog`.`Description` AS 'Summary' FROM `AR System Schema`.`HPD:Help Desk` LEFT OUTER JOIN `AR System Schema`.`HPD:WorkLog` ON ( `HPD:Help Desk`.`Incident Number` = `HPD:WorkLog`.`Incident Number` ) ) HPDWORKLOG ON HPDHELPDESK.TicketNumber= HPDWORKLOG.IncNumber
Was this page helpful? Yes No Submitting... Thank you

Comments