Creating SQL queries by using the visual query builder

The visual query builder supports the following BMC Helix ITSM views and field types:

The following process flow explains the steps involved in creating queries by using the visual query builder.

Before you begin

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

To create queries by using the 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.
      
      
      
      
   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) 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.
   8. (Optional) To change the sequence of fields, drag the fields to move them to the required position.
   9. Click SQL to view and verify the generated query.
      
      
      
      
   10. 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, or f.
   - From the Type list, select SQL and manually edit the query.c
7. (Optional) To remove all the existing view and field selections, click Reset.
8. Apply visualizations and data transformations; for details, see Configuring-dashboards-panels-and-queries. 
9. Save the dashboard.

To add 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.

To apply filters

1. Open the 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) Drag the calculated fields to be used as filters to the Filters area.
   For more information about calculated fields, see the Using calculated fields as filters video.

6. Define filter conditions for the fields by using the operators.
7. Preview and use the query.

Example

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.

To 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 the 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: