Using subqueries to create advanced reports
The subquery capability in BMC Remedy Smart Reporting enables users to generate far more effective reports. For example, you can use a subquery to generate a report that compares the number of incidents recorded in the current financial year with the past years. Create this report by generating two distinct queries and using subquery functionality to combine the query results.
Remedy Smart Reporting supports inner joins and left outer joins only.
Permissions required to create subqueries
BMC Remedy Smart Reporting 9.1.03 introduces new roles. If you upgrade to BMC Remedy Smart Reporting 9.1.03 from an earlier version, you can continue to use the old roles. New set of roles are available for fresh installation of BMC Remedy Smart Reporting 9.1.03.
BMC Remedy Smart Reporting users with one of the following roles can create subqueries.
|If you are using fresh installation of 9.1.03||If you upgrade to version 9.1.03 from and earlier version|
To create a subquery
In the report creation data step, click the + sign under Master Query
Subquery types and examples
In BMC Remedy Smart Reporting, you can create the following types of subqueries.
An Append subquery takes the results of one query and appends them to another set of report results as additional columns. The purpose of this query is to allow the result of one query to be compared to that of another. The two queries must have at least one common column in order to join them.
For example, you may use Append query to compare the number of incidents and problems created for each company.
Example: Create a report which displays incidents and problems recorded for each company
A union subquery combines the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types in order to join them.
For example, you may use Union subquery to create a report which displays the number of incident and change requests based on the Assignee Support Company.
Note: Union subquery type cannot be used if you want to combine multiple SQL queries which have multiple queries within.
Example: Use Union subquery to create a report that includes incident and change requests based on the Assignee Support Company
In a subquery, you can use either Append or Union type. Combining both Append and Union in a subquery is not supported.
Subquery styles and examples
In BMC Remedy Smart Reporting, you can create Append and Union subqueries by using the following subquery styles:
|Basic||This subquery style allows you to include fields from the same view as the master query. For example, you can create a master query to view submitted incidents, and a subquery to view the number of resolved incidents.|
This subquery style extends the basic subquery functionality to enable you to query multiple views.
You may use the Append subquery type and Advanced subquery style to create a report. For example, the following report lists the number of incidents, change requests, and problems created for each company and their assigned group:
To know how to use the Advanced subquery style, see the example provided in the Using subqueries to create advanced reports subquery.