This documentation supports the 19.02 version of Remedy IT Service Management Suite.

To view the latest version, select the version from the Product version menu.

Advanced Authoring

Use the Advanced Authoring option to create reports by using complex database queries. You can use this option to create complex reports by using the complex database functions that contain multiple parts, such as over, partition by, order by, and so on, in a single function. To know more about how to run a complex database function by using the Advanced Authoring option, see Examples of how to execute complex database functions having multiple parts.


Important

  • You can use Advanced Authoring for MS SQL and Oracle database only.
  • Advanced Authoring or Freehand SQL reports do not support User Prompts & Source Filters.

You must have the Admin, Corp Writer, or Report Writer role to use Advanced Authoring.

To create a report using Advanced Authoring

  1. On the Remedy Smart Reporting Admin Console, click + button to create a new Report.
  2. In the New Report window:
    1. Enable Advanced Authoring.

    2. In the Author Method field, select Freehand SQL.
    3. Select 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. For examples of complex queries, see Examples.

    2. Click Validate.
    3. Click Save.

The report fields are saved to a folder called SQLFields in the Data tab, and the report data is displayed on the right.


Examples of how to execute complex database functions having multiple parts

You can execute complex functions using the Advanced Authoring option.

Example 1

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

To run this function by using Advanced Authoring, type 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:

ArgumentDescription
lag;over;partition by;order byIndicates all parts in the function.
4Indicates 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#).
1Indicates the number of arguments in the next part over, which has only one argument, (, that is prefixed by #KWD#.
1Indicates the number of arguments in the next part partition by, which has only one argument Short Description.
2Indicates 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 by using Advanced Authoring, type 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:

ArgumentDescription
rank;over;order by;countIndicates all parts in the function.
2Indicates number of arguments in the first part - (, and ).
The opening and closing brackets are prefixed with the string #KWD#. For example, #KWD#( and #KWD#).
1Indicates 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

  1. Tohfa Ansari

    Can you provide examples on using parameters inside Advanced authoring? I am unable to use dynamic parameter using {?} always gives me an error saying parameter mismatch found 0 expecting 1

    May 20, 2021 10:36
    1. Vrishali namdev Galinde

      Hello Tohfa Ansari,

      Thank you for your comment. We shall discuss this with the R&D team and update the documentation accordingly.

      Thanks & Regards,

      Vrishali

      Jun 08, 2021 03:24
      1. Vrishali namdev Galinde

        Hello Tohfa Ansari,

        Apologies for the delayed response. I checked with the R&D team. This looks like a specific request. As you are getting the parameter mismatch error, request you to raise a ticket with the BMC Support team who would help you with this requirement.


        Thanks & Regards,

        Vrishali


        Aug 06, 2021 08:38
        1. Joshua Moldover

          It's not a request for a specific system. It's a global issue. When first validating the SQL code, you receive a message stating "One or more filter data types are missing." and a list of the filters appears below the query with a dropdown for each to select the data type. After selecting data types for the filters, subsequent attempts to validate the SQL code receive the error (parameter mismatch found 0 expecting 1) and prevent the SQL from being saved.

          Mar 17, 2022 10:50
          1. Murali Mohan Thandava Murthy

            Hi Joshua, Post discussion with the R&D team, the dynamic parameter as mentioned in your comment is not supported.

            May 16, 2022 01:26