Manually editing SPARQL or SQL Report Builder queries

Edit online

With Report Builder, you can create reports to view data from across your projects. However, there are limitations to what you can do in the guided graphical interface. If you need to create more complex reports, you can use the native query language of the data source.

When you create a report, Report Builder generates the underlying query resource. If you are using Data Warehouse as the data source, an SQL query is generated. If you are using Lifecycle Query Engine, a SPARQL query is generated.

The query is displayed in the text editor in the Advanced section. When you create a report and then make changes, using the guided graphical Report Builder interface, the generated query is automatically updated, and it is read only.

The basic work flow for creating complex reports is to create an initial report using the guided graphical Report Builder interface and then modify the generated SQL or SPARQL query.
Tip: Use the guided graphical interface to build as much of your report as you can; include traceability links and results columns. Then use the data dictionaries as reference to other tables that you might want to add to what is currently exposed in the Report Builder user interface. Pay attention to the following two areas of the data dictionary:
  1. Application data related columns: These describe the data in terms that are familiar to each IBM® Engineering Lifecycle Management application.
  2. Data warehouse operational datastore mapping: This identifies where the data coming from the Engineering Lifecycle Management application is being stored in the data warehouse. Report Builder creates queries that run against this data warehouse.
Warning: Once you edit the generated query, you cannot go back and use the guided graphical interface to update or modify the report.

Because the ability to manually edit the report queries is advanced functionality, and not all users are familiar with SQL or SPARQL query syntax, an administrator can specify that only report managers can edit the generated queries. See Limiting manual query editing to report managers.

Before you begin

Before you can modify the generated SQL or SPARQL query, you must create a report using the guided Report Builder user interface.

Procedure

  1. In Report Builder, open the report that you created.
  2. On the report results page, near the upper right, click Edit.
  3. Click Format results, and expand the Advanced section.
    You can see the generated query for the report that you created.
  4. Click Edit query.
    Warning: Once you edit the generated query, you cannot go back and use the guided Report Builder user interface to modify the report.
    Note:
    You cannot perform the following tasks when you manually edit a query:
    • Edit the selections, except the columns, report name, or privacy and sharing, in the My Choices pane.
    • View the contents of the Choose data tab.
    • Add columns to tables, modify attributes and calculated values for graphs, and optimize queries by using the options on the Format results tab.
    • Use column filters on the Run report tab. Also, the drill-down on the calculated columns is not retained.
    You can perform the following tasks when you manually edit a query:
    • Configure variable attributes for tables; format graphs, change the type of graph type, and change units and dimensions for graphs; and use Data Completeness Check on the Format results tab.
    • Select projects in the Limit the scope field for dynamic filters or edit other dynamic filters by using text values on the Run report tab.
  5. Make the required updates to the query syntax.
    For example:
  6. To ensure that the updated syntax is valid, click Validate Query.
    The query variables are calculated, the query resources are updated, and the report preview is updated in the Format section.
  7. To undo your changes, click Cancel. The query reverts to the state it was in before you clicked Edit Query.
  8. Click Save.

Manually adding columns

Edit online

When you manually edit the generated query for a report, you can no longer use the Columns section of Report Builder to add new columns to the report; you must update the query itself to include new columns. However, you can use the Columns section to rearrange the column order and to update headings.

In the following example, the data warehouse is the data source. You start with the basic report on work items, and add a column for work item tags to the SQL query.

Before you begin

You must have the generated query that you want to modify open in the Advanced section of Report Builder.

Procedure

  1. Expand the Advanced section in Report Builder. The generated SQL query looks like this:
    The generated SQL query for a basic report on work items.
  2. Click Edit query.
  3. The items under SELECT are the columns currently defined for the report. To add a column for tags, insert a line above that last SELECT item (T1.URL) and add the following statement: T1.TAGS as Tags,.
  4. Click Validate Query. The generated SQL query looks like this:
    The generated SQL query for report, showing the new tags column.
    Note that the new column is included in the list in the My Choices pane.

Adding dynamic filters

Edit online

When you run a report, you can use dynamic filters to enter alternative values to filter the report data. When you create a report using the Report Builder user interface, dynamic filters are automatically generated for certain variables in the SELECT statements of the query. However, not all variables generate dynamic filters. You can explicitly add template parameters to the query logic to ensure that particular variables are available as dynamic filters for use with the report.

More details about filter annotations are available on the Jazz wiki.

For SQL, template parameters are defined by a string that is within dollar signs ($), such as $Priority$. This string defines a text parameter for priority that will be substituted into the query at run time, based on what the user enters when they click the lock on the dashboard. When they run the report, the filters list includes Priority.

In the following example, the data warehouse is the data source. You start with the basic report on work items that includes columns for Complexity and Creation Date, and add a dynamic filter for Priority to the SQL query.

Procedure

  1. Expand the Advanced section in Report Builder. The generated SQL query looks like this:
    The generated SQL query for a basic report on work items.
    The dynamic filters that are available when you run the report looks like this:
    The dynamic filters that are available for the report.
  2. Click Edit query.
  3. To add a dynamic filter for priority, under the WHERE statement, insert a line after the first item (T1.PROJECT_ID = 1), and add the following lines: AND and T1.REQUEST_PRIORITY='$Priority$'.
  4. Click Validate Query. The generated SQL query looks like this:
    The generated SQL query for report, showing the new tags column.
    The dynamic filters that are available when you run the report looks like this:
    The dynamic filters that are available for the report, showing the new Priority filter.

Limiting manual query editing to report managers

Edit online
Administrators can determine who is able to manually edit the SQL and SPARQL queries that are generated from Report Builder reports. For each data source that you have set up for Report Builder, you can specify that only report managers can modify the queries.

Before you begin

You must be logged in as administrator.

Procedure

  1. Open Report Builder, and click the Administration icon Gear icon for administration on the product banner and select Data Sources or click Data Sources in the Connect to data sources pane.
    Alternatively, you can navigate to https://server_name:port/rs/endpoint
  2. On the Data Sources page, click a data source name in the list.
  3. In the Data source properties section, enable the Allow only report managers to edit queries checkbox, then click Save.
    Screen capture of the Data source properties section, with the Allow only report managers to edit queries check box highlighted.
  4. Optional: Repeat these steps for each data source.

What to do next

For more information about creating advanced reports, see this Jazz.net article: Advanced Report Creation in Jazz Reporting Service. The article was written for version 5.0.2, but the information is still relevant.