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

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

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
  • 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 type Description
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. 

  Example: Create a report which displays incidents and problems recorded 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.

  Example: Use Union subquery to create a report that includes incident and change requests based on the Assignee Support Company
  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 name Value
      Calculated Field Name Name for the text field to be created. In this example, enter Label.
      Formula Type To add a simple text field, select Simple.
      Formula box Enter 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 Field Sub Query Field
    Label Label
    Assignee Support Company Assign Support Company
    Number of Incidents Number 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 style Description
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.
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

Comments

  1. Jerome Walker

    Hello. We need access to Service Request ID in Incident Management, Change Management, and I'd be willing to bet, Task Management. Work Order Management would be good too, although WOI Custom has it so we can get it. And really, the field itself should be made available BUILT IN to all of the applications. But as it is now, we can't even return CRQ and INC numbers to a Survey. It is extremely limiting. Please fix this, and soon. And in the meantime, if there actually is already a solution, or even a temporary workaround, please let me know.

    Thank you very much,

    Jerry

    Jerome Walker

    May 23, 2018 01:08
    1. Jyoti Nerkar

      Hello Jerome,


      I request you to contact Support team for the same.


      Regards,

      Jyoti

      Sep 27, 2018 02:22
      1. Jerome Walker

        Hello Jyoti. We were able to take care of this in-house by modifying our Views. This is resolved. Thanks! Cheers, Jerry (Jerome)

        Oct 18, 2018 04:06
        1. Jyoti Nerkar

          Hello Jerome,


          Thanks for sharing this update.


          Regards,

          Jyoti

          Oct 22, 2018 11:36
  2. Jerome Walker

    Hello again. This is in regards to a URL on this page. For this statement above, "To know how to use the Advanced subquery style, see the example provided in the Using subqueries to create advanced reports subquery.", the URL should be updated. I searched these pages and found this which looks like it might be the correct updated URL:

    https://docs.bmc.com/docs/itsm91/using-subqueries-to-create-advanced-reports-779819560.html

    Jerry

    Jerome Walker

    May 23, 2018 03:46
    1. Jyoti Nerkar

      Hello Jerome,

      Regarding the URL for Using subqueries to create advanced reports subquery, please note that both the following URLs are pointing to the same page, only the template(view) is different:


      https://docs.bmc.com/docs/display/itsm91/Using+subqueries+to+create+advanced+reports

      https://docs.bmc.com/docs/itsm91/using-subqueries-to-create-advanced-reports-779819560.html

      You can revert if you need more details.

      Regards,

      Jyoti



      Jun 22, 2018 12:51
  3. Eliana Montaldo

    the full outer join not work! When will be possible use this type of join?

    Dec 11, 2018 06:50
    1. Vrishali namdev Galinde

      Hi Eliana,

      I shall check with the SMEs and get back to you on this.

      Thanks & Regards,

      Vrishali


      Dec 11, 2018 11:30
    1. Vrishali namdev Galinde

      Hi Eliana,

      Currently Smart Reporting supports only inner joins and left outer joins.

      I have updated the documentation mentioning the same.

      We may consider supporting the full outer join in future releases though.

      Till then, apologies for the inconvenience caused. Do let us know if we can help you with anything else.


      Regards,

      Vrishali


      Mar 18, 2019 02:37