×
 
 
  •  
$helper.renderConfluenceMacro('{bmc-global-announcement:$space.key}')

 

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.

BMC Remedy IT Service Management Suite 19.02 ... Using Using BMC Remedy Smart Reporting BMC Remedy Smart Reporting reports Creating a custom report using BMC Remedy Smart Reporting Report Builder - Data step

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.03If you upgrade to version 9.1.03 from and earlier version
  • Public Content Writer & Collaborator
  • Public Content Writer & Collaborator - Advanced
  • System Administrator
  • Administrator
  • Corp Write


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. 

Subquery typeDescription
Append

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. 

  1. In the BMC Remedy Smart Reporting console, use one of the following navigation methods to click Create > Report.
     . Left side navigation
    . Top right button
    . Toolbar
  2. To use the Incident Management view fields in report creation data step, in the New Report dialog box, select the Incident Management view.
  3. In the data creation step, drag and drop the Company and Number of Incidents fields. (Master query)
  4. To add a subquery, click the + sign under the Master Query .
  5. Set the Sub Query Type to Append.
  6. To combine another view with the report, set the Style to Advanced.
    Note: The Advanced option allows you to select a different view for your subquery. The Basic option 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.
  7. In the Data Source list, select AR System.
  8. In the View list, select Problem Management.
  9. Click OK to set up the query.
  10. To define report table join type, select Inner Join. Selecting inner join fetches the matching records from both the reports.
  11. To define the join fields:
    • From the Master Query Fields list, select Company as this is a common field in both the queries.
    • To join the Company field of Problem Management to the Master Query, drag the Company field to Sub Query Fields. 
  12. To be able to identify the subquery, in the Name & Description fields, enter appropriate name and description. Naming a subquery is useful if you have multiple subqueries defined in BMC Remedy Smart Reporting.
  13. Click Save.
  14. Drag and drop the Number of Problems field from the fields list to the report column list.
  15. If you need to make any changes to your subquery, click Edit Settings in the report data creation area.
     
  16. Click Report > Save to save the report. The report results display the incidents and problems for each company.
Union

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.

  1. In BMC Remedy Smart Reporting console, use one of the following navigation methods to click Create > Report.
     . Left side navigation
    . Top right button
    . Toolbar
  2. To use the Incident Management view fields in report creation data step, in the New Report dialog box, select Incident Management view.
  3. In the data creation step, drag and drop the Assignee Support Company and Number of Incidents fields. As this is the first query in the data creation step, it is referred as Master Query.
  4. To add a subquery, click the + sign under Master Query .
  5. (Optional) You can add a label field to the master query. As ticket IDs are not displayed in this example, add a Label field to identify the incident and change record numbers. To create a Label field, click the + button at the bottom of the field list in the Data step of the report builder.

    1. On the Calculated Field dialog box, select the following options:

      Field nameValue
      Calculated Field NameName for the text field to be created. In this example, enter Label.
      Formula TypeTo add a simple text field, select Simple.
      Formula boxEnter Number of Incidents in the text box under the Formula box, and click + Add.
    2. Click Save.
  6. Drag and drop the Label field to the Columns section to complete the master query.
  7. Change the Analysis Style to No Drill.
  8. Click the + sign under Master Query  to add a subquery.
  9. To include the incidents and changes in the same report, set the Sub Query Type to Union.
  10. To combine another view with the report, set the Style to Advanced.
    Note: The Advanced option allows you to select a different view for your subquery. The Basic option 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.
  11. In the Data Source list, select AR System.
  12. In the View list, select Change Management.
  13. Click OK to set up the query.
  14. On the subquery data view, add a label field for the Change Management view, as described in Step 5.

  15. Link the Master Query fields to the Sub Query fields.

    Master Query FieldSub Query Field
    LabelLabel
    Assignee Support CompanyAssign Support Company
    Number of IncidentsNumber of Changes
  16. Select the Show Duplicate option to view duplicate results. By default, duplicate records are not displayed.
  17. To be able to identify the subquery, in the Name & Description fields, enter appropriate name and description. Naming a subquery is useful if you have multiple subqueries defined in your BMC Remedy Smart Reporting application.
  18. Click Save. Your report displays the number of Change and Incident requests for each Assigned Support Company.
  19. Rename the Number of Incidents field to Counts and Label field to Application by using the Format > Edit > Display option.
  20. Click Save to save the changes made to field names.
  21. Click Report > Save to save the report. The report results display the incidents and changes created for each assignee support company.

Note

 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:

Subquery styleDescription
BasicThis 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.
Advanced

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.

Related topics

Creating a custom report

Data step in report creation

Was this page helpful? Yes No Submitting... Thank you
Last modified by Jyoti Nerkar on Apr 07, 2019
sub_queries advanced_sub_queries bmc_remedy_smart_reporting sub_query_type_append sub_query_type_union sub_query_type_advanced create_report_using_append_sub_query create_report_using_union_sub_query create_report_using_advanced_sub_query sub_query_types_supported_by_bmc_remedy_smart_reporting sub_query_use_cases sub_query_examples append_sub_query_example union_sub_query_example advanced_sub_query_example

Comments

Log in or register to comment.

Drill Anywhere reports
Report Builder - Charts step
© Copyright 1991 - 2019 BMC Software, Inc. © Copyright 1991 - 2019 BladeLogic, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2023 BMC Software, Inc. Use of this site signifies your acceptance of BMC’s Terms of Use . BMC, the BMC logo, and other BMC marks are assets of BMC Software, Inc. These trademarks are registered and may be registered in the U.S. and in other countries.