Manually editing Report Builder queries

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.

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.

About this task

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

You can see the query 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:
  • Application data related columns: These describe the data in terms that are familiar to each Collaborative Lifecycle Management (CLM) application.
  • Data warehouse operational datastore mapping: This identifies where the data coming from the CLM application is being stored in the data warehouse. Report Builder creates queries that run against this data warehouse.
The data dictionaries are here:
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.

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 that when you click Edit Query, all of the selections in the My Choices pane disappear, except for Columns.

  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

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, Rational 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

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.

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, Rational 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.
     When you run the report, you see these dynamic filters:
    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.
     When you run the report, you see the new dynamic filter:
    The dynamic filters that are available for the report, showing the new Priority filter.

Limiting manual query editing to report managers

Administrators can determine who can 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 Admin and then click Data Sources: 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 Restrict query editing to report managers check box, and then click Save.
    Screen capture of the Data source properties section, with the Restrict query editing 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.
Feedback