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
On the BMC Helix ITSM: Smart Reporting console, click the + button, and select Report.
In the New Report window:
Enable Advanced Authoring.
From the Author Method list, select Freehand SQL.
From the Data Source list, select a data source.
Click Create Analysis button at the bottom of the New Report window.
To write complex queries, in the SQL Query editor, perform the following steps:
Enter the SQL query for creating the report.
Make sure you give an alias in theSELECT
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.
To know how to handle Daylight Saving Time while writing a query, see Consider or ignore Daylight Saving Time in a report query.Click Validate.
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 |
---|---|
| Indicates all parts in the function. |
| Indicates the number of arguments in the first part: The opening and closing brackets are prefixed with the string |
| Indicates the number of arguments in the next part |
| Indicates the number of arguments in the next part |
| Indicates the number of arguments in the next part |
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 |
---|---|
| Indicates all parts in the function. |
2 | Indicates the number of arguments in the first part: The opening and closing brackets are prefixed with the string |
1 | Indicates the number of arguments in the next part |
5 | Indicates the number of arguments in the next part |
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
Consider or ignore Daylight Saving Time in a report query
Because of Daylight Saving Time (DST) , reports having pre-defined DATE functions in the calculated fields fetched incorrect results when run in the DST timezones. For example, if an incident is submitted at 3 p.m., then the submission date is displayed as 2 p.m. in the report due to DST. An administrator can handle DST on the server side by setting a CCS parameter. To know more about the server side setting, see the Ignore-DST-Handling
parameter in
Centralized configuration
.
Additionally, as a report writer, you can handle DST by passing the following parameters to the report query:
Parameter | Description |
---|---|
APPLYDST | Considers DST while generating reports |
IGNOREDST | Ignores DST while generating reports |
Important
Make sure you pass the DST parameter in the outer query of the report.
If an administrator has set the CSS parameter Ignore-DST-Handling
to handle DST on the server side, and if the report query has parameters APPLYDST
or IGNOREDST
, these parameters override the server settings. If the report query does not have the DST handling parameters, the server settings are applied to handle DST.
If an administrator has set the CSS parameter Ignore-DST-Handling
to handle DST on the server side, and if the report writer generates the report from a timezone that does not have DST, the server settings are considered while generating the report.
Examples: Consider or ignore DST in a report query
Consider the following examples to understand how you can consider or ignore DST through a report query.
Example 1: Sample query to generate a report by considering DST
Consider a report writer wants to generate a report to fetch all incidents that were created on 03/05/2020 by considering DST handling. The sample query to generate this report would be as follows:
SELECT DISTINCT `HPD:Help Desk`.`Incident Number` AS C1,
`HPD:Help Desk`.`Submit Date` AS C2
FROM `AR System Schema`.`HPD:Help Desk`
WHERE DAY(`HPD:Help Desk`.`Submit Date`) = 3
AND MONTH(`HPD:Help Desk`.`Submit Date`) = 5
AND YEAR(`HPD:Help Desk`.`Submit Date`) = 2020 APPLYDST
Example 2: Sample query to generate a report by ignoring DST
Consider a report writer wants to generate a report to fetch all incidents that were created on 03/05/2020 by ignoring DST. The sample query to generate this report would be as follows:
SELECT DISTINCT `HPD:Help Desk`.`Incident Number` AS C1,
`HPD:Help Desk`.`Submit Date` AS C2
FROM `AR System Schema`.`HPD:Help Desk`
WHERE DAY(`HPD:Help Desk`.`Submit Date`) = 3
AND MONTH(`HPD:Help Desk`.`Submit Date`) = 5
AND YEAR(`HPD:Help Desk`.`Submit Date`) = 2020 IGNOREDST
Example 3: Using DST parameters to show correct incident data in a report
Consider a service desk agent creates an incident 03/05/2020 at 3 p.m. in Mexico timezone. A report writer generates a report in the same timezone to fetch all incidents that were created on this date. See the following table to see how DST is handled in different scenarios:
Setting: Ignore-DST-Handling | Query Parameters | Report Result |
---|---|---|
Not set | None | The incident is displayed incorrectly in the report with submit time as 2.p.m. |
True | None | The incident is displayed incorrectly in the report with submit time as 2.p.m. |
False | None | The incident is displayed correctly in the report with submit time as 3.p.m. |
True | APPLYDST | The incident is displayed correctly in the report with submit time as 3.p.m. |
False | IGNOREDST | The incident is displayed incorrectly in the report with submit time as 2.p.m. |
If the above report is run from a timezone which does not have DST, the report will display correct time in all the above scenarios, which is 3 p.m.
Comments
Log in or register to comment.