Creating SQL queries by using Visual Query Builder

Supported BMC Helix ITSM views and field types

Visual Query Builder supports the following out-of-the-box BMC Helix ITSM views and field types:

To enable or disable the display of out-of-the-box views in Visual Query Builder

If you want to create SQL queries based on custom views only, you can disable the display of out-of-the-box views in Visual Query Builder. By default, these views are displayed.

1. Log in to the BMC Helix Dashboards as a reporting editor or administrator.
2. From the navigation menu, select Administration > General > Default preferences.
3. In the Manage dashboard features section, use the Enable OOTB views for Visual Query Builder option to enable or disable the display of views. 
   The changes are saved.

The following video (2 minutes) explains how to use Visual Query Builder to create SQL queries.
 

https://youtu.be/DICMAN5009A

The following process flow explains the steps involved in creating queries by using Visual Query Builder.

Before you begin

Make sure that BMC Helix Dashboards is connected to BMC Helix ITSM.

To create SQL queries by using Visual Query Builder

1. On the Home page, click Add Visualization.
2. From the Data source list, make sure that BMC Helix is selected.
3. From the Query type list, select Service Management.
4. From the Type list, select Visual query builder.
5. On the Visual query builder page, perform these steps:
   1. From the View list, select the required view.
      After you select the view, the left pane shows the fields that you can use to build the query. The fields are listed under view folders. When you hover over a field name, the corresponding database column name for the field is displayed.
      
      
       
   2. Select a view folder and drag the required fields to the Fields area.
   3. (Optional) To add the distinct statement to the query, select Use distinct.
   4. Click Preview.
      The query is built and the results of the query are displayed in the table view.
      
      
       
   5. (Optional) Add filters to view the subset of data fetched by the query.
      For details, see To add filters to restrict data.
   6. (Optional) Aggregate data fetched by the query.
      For details, see To aggregate data.
   7. (Optional) Apply functions to customize field values.
      For details, see To apply functions to fields.
   8. (Optional) To make changes to the query, select and remove fields as required and preview the changes.
      You can remove fields by dragging them to the left pane in the list of fields.
   9. (Optional) To change the sequence of fields, drag the fields to move them to the required position.
   10. Click SQL to view and verify the generated query.
       
       
        
   11. Click Use query.
       You are directed to the panel configuration options and the query is copied in the query box.
       
       
        
6. (Optional) To make changes to the query, perform one of the following steps:
   - Click Query Builder and make changes.
   - From the Type list, select SQL and manually edit the query.
7. (Optional) To remove all the existing view and field selections, click Reset.
8. Apply visualizations and data transformations; for details, see Creating-and-customizing-dashboards and Creating-dashboards-with-advanced-functions. 
9. Save the dashboard.

Filters to restrict data

You can add filters to limit the amount of data that is returned by a query. Data is filtered based on the filter conditions that you specify. You can define conditions by using the operators; for example, define the following condition to view data only for the HR group: Group contains or equals HR

The following operators are supported to define filter conditions:

The default operator is equal for all fields except the Date field for which the default operator is between. The operators are displayed according to the field type; for example, if you select a string-type field, you will not see numeric operators, such as between and greater than.

You can also use dashboard variables as filters. For details, see this example. For information about creating and configuring dashboard variables, see Setting-up-variables-for-dashboard-queries.

To apply filters

1. Open Visual Query Builder.
2. From the View list, select a view.
3. Drag the required fields to the Fields area.
4. Drag the fields to be used as filters to the Filters area.
   You can drag fields from Fields to Filters. The enum-type fields display a set of constant values. You can select a value instead of typing it manually.
5. (Optional) From the Calculated Fields folder, drag the calculated fields to be used as filters to the Filters area.
6. Define filter conditions for the fields by using the operators.
7. Preview and use the query.

Example: Apply filters while creating a query by using the Problem Management view

Bob is a dashboard user. He wants to view the problem summary and other details, such as impact, priority, urgency and assigned groups. He selects the Problem Management view and selects the specified fields under Problem Details. He previews the query and decides to apply the filter to view only critical queries. He applies the following filter:

Bob can now analyze the critical problems.

Example: Use a dashboard variable as a filter 

Bob is a dashboard user who wants to view a list of incidents by submitters. He selects the Incident Management view and the required fields under Incident Details. He previews the query that shows the expected result. Bob now wants to view the incidents submitted by specific submitters. He adds the already-created Submitter dashboard variable to the Filters section and previews the query. The preview results show the incidents submitted by the submitters that match the condition defined in the dashboard filter.

Aggregate data for analysis

You can aggregate data returned by a query. Use data aggregation to summarize data for useful analysis. The following aggregation functions are supported:

To aggregate data

1. Open Visual Query Builder.
2. From the View list, select a view.
3. Drag the required fields to the Fields area.
4. Click the required field and select an aggregate function.
5. Preview and use the query.

Example

Carl is a Service Desk manager. He wants to view the incidents according to assigned groups. He selects the Incident Management view and drags the Incident Number and Assigned Group fields under Incident Details to the Fields area. He previews the query. The query results show the incident ID according to assigned groups. However, he wants to view the count of incidents for each assigned group. He uses the following Count aggregate function and previews the query:

Functions that can be applied to fields

Apply functions to fields to customize field values; for example, you can apply the String function to a string field to display all the string values in uppercase letters. The following functions are available:

• String: Use to customize the values of a string field by using any of the following options:
  • Upper - String is displayed in uppercase.
  • Lower - String is displayed in lowercase.
  • Ltrim - All the leading spaces are removed from the string.
  • Rtrim - All the trailing spaces are removed from the string.
  • Length - Number of characters in the string is displayed.
  • String To Number - String is converted and displayed as a numeric value.
• Date: Use to customize the values of a date field by using any of the following options:
  • Date - Date and time stamp are displayed.
  • Weekday - Number of the day in a week when the incident occurred is displayed.
  • Day - Number of days for which the incident is open is displayed.
  • Week - Number of the week when the incident occurred is displayed.
  • Month - Number of the month when the incident occurred is displayed.
  • Quarter - Number of the quarter when the incident occurred is displayed.
  • Year - Year in which the incident occurred is displayed.
  • First Day Of Month - First day of the month when the incident occurred is displayed.
  • First Day Of Quarter - First day of the quarter when the incident occurred is displayed.
  • First Day Of Year - First day of the year when the incident occurred is displayed.
  • Weekday Name - Day of the week when the incident occurred is displayed.
  • Month Name - Name of the month when the incident occurred is displayed.
• Time: Use to customize the time format for date fields by using any of the following options:
  • Time - Date and time stamp are displayed.
  • Hour - Time is displayed in hours.
  • Minute - Time is displayed in minutes.
  • Second - Time is displayed in seconds.
     

To apply the String, Date, or Time function to a field

1. Open Visual Query Builder.
2. From the View list, select a view.
3. Drag the required fields to the Fields area.
4. (Optional) Drag the fields to be used as filters to the Filters area.

5. Click the required field and select the appropriate String, Date, or Time function.
   The functions are displayed according to the type of field.

   String functions

   

   Date functions

   

   Time functions

   

6. To remove the applied function, select None.
7. Preview and use the query.