Reporting on artifacts across projects with Report Builder

Using Report Builder, you can create reports to view data from across your projects.

For example, create a report to determine whether you have adequate test coverage and to ensure that defects are identified and tracked. You can define conditions to focus only on the data you need. You can specify whether conditions can be changed when the report is run, so other organizations can reuse and tailor your report. In this case, you create a report about requirements, related test cases, and the defects that are related to those test cases.

To learn about reporting on data in configurations, see Reporting on data in configurations in IBM® Knowledge Center and Getting started with reporting by using Lifecycle Query Engine data sources on Jazz.net.
To create historical metrics reports about artifact and project trends, see this topic.

About this task

As you build your report, you see your choices on the right side of the page. You can change your choices in two ways: click the heading near the top of the page, and go to the section to change, or, in the My Choices pane, click the pencil icon Edit

beside the section to change.

Procedure

Choose data.
Format results.
1. Format a table.
2. Format a graph.
Optimize the report.
Name and share the report.
Run the report.

Step 1. Choose data

Edit online

Procedure

Open Report Builder.

Go to https://server_name:port/rs. On the Reports page, click Build report. If the page doesn't open, contact your application administrator.
Choose a report type.
- Click the pencil icon to select the data source. If only one data source is defined, it is selected by default. To decide what data source to choose, read this topic.
- Select Current Data (table or graph) to report on the latest information about artifacts in and across projects.
Limit the scope.
Choose the projects to report on, and click Continue. If you don't select any projects, the report includes all projects that you can access.
Note: If you are reporting on IBM Engineering Requirements Management DOORS®, select the DOORS Database - RM Project.
Whatever data can be seen by the DOORS user, which is set in the Data Collection Component resource group for DOORS, is the data that gets seen by all users in Report Builder. The DOORS Database - RM Project that gets created in the data warehouse is fully public and any Jazz Team Server user can see all the data in any Report Builder reports, since there is no correlation between JTS users and DOORS users.
The list shows the projects that you can access in the data source that you selected. If your projects are not in the list, see the administrator who created the data sources for Report Builder.
- For Data warehouse, all projects are listed.
- For Lifecycle Query Engine, only projects that are not enabled for configurations are listed.
- For Lifecycle Query Engine scoped by configurations, only configuration-enabled projects are listed.
Some artifact types are project-specific. Go to step 4 to select the artifact type, then return here, and select List only the project areas that contain the artifact for your report.
Choose an artifact.

Select an artifact or specific types, and, click Continue. You might have to expand some artifacts to make a choice; if you don't expand the artifact, and select it, all of its types are included in the results.

If you select an artifact type that is project-specific, you can return to the Limit the scope section, and select List only the projects that contain the artifact for your report.

Tip: To report on requirements from specific modules, click Requirement, and select the types. You specify the requirement collections or modules later.
Trace relationships and add artifacts.
Explore how your artifacts are linked to other artifacts from the same, or from other lifecycle tools. Click Add a relationship. For each artifact type, you see all the existing relationships. Pick one, and click OK.
Example: To show that a requirement is validated by a test case, select the Validated by Test Case relationship. To also list the work items that are associated with the test cases, click Add a relationship > Related Work Item, and then click OK. In the Set conditions section, you can further define the selected relationships. The example uses a data warehouse data source.

To trace multiple relationships from one artifact, or to add source artifacts, select the checkbox. For details on how to create traceability reports, read Tips for working with Report Builder traceability reports.
- Click Add a relationship, and choose how to combine the results:
  
  Merge
  
  The results are shown in the same rows.
  
  Append
  
  The report shows the results for each traceability relationship. All the source artifacts are in the same column.
  Note: You can mix Append and Merge traceability paths in a single report. Merge paths take precedence and are evaluated first; then the results are appended together.
  
  Append in new columns
  
  To count the source artifact relationships separately, append the results in different columns.
  
  For more information, watch the following video:
- Click Add an artifact, and select an artifact type.
For each relationship that you trace, you can set conditions. For step-by-step instructions on how to build this type of report, read the Tutorial.
Example: You can trace how features are linked to work items, test cases, and child requirements. You see all the relationships in a single table.
If you use Data Warehouse, you can trace relationships to iterations, releases, or timelines from the following artifact types: work item, work item history, test case execution record, test case result, and test plan.
Example: Show all open work items that are assigned to an iteration that is completed.
1. Select Work Item in step 4.
2. Click Add a relationship > Iteration, and then click OK.
3. Set conditions in step 6.
  
  To show only open work items, set the status group category for the work item to is not Closed, then click Add.
  
  To focus only on work items that are assigned to an iteration that is completed, set the iteration end date to before today. Click Add and close.
To remove the last relationship from the right of an artifact, click Back. To remove an artifact or relationship and all the items to the right, click the X beside the item name.

Tip: You can change the name of an artifact in the traceability box by double-clicking its name and typing the new one. For example, if you want your report to refer to related defects instead of change requests, double-click the Change Requests box in the traceability diagram, and type Defects. Changing the name in the traceability diagram makes it clearer to your team members, especially if your report uses custom artifact types or custom links.

After you build the artifact relationships, click Continue.
Set conditions.

To further refine the content of your report, set conditions. You can set conditions for any attribute of the artifact type that you selected, and any attribute of the related artifact types in your traceability paths. By setting conditions, you can further identify relationships among artifacts. For example, to show that a requirement is validated by a test case, select the Related Test Case relationship. Then, set a condition to focus only on approved test cases. For the artifact type Test Case, choose the attribute State, and set the value to Approved.
1. Click Add condition.
2. From the list, select an artifact type.
3. Choose the attribute that you want to specify a condition for, and select the values to return the artifacts you want.
4. To keep the window open for adding other conditions, click Add. Otherwise, click Add and Close.
5. Optional: Change the lock to control whether people can or must supply a value for the condition when they run the report.
- To edit a condition, click the pencil icon beside it.
- To create logical groups of conditions, select the conditions and click Group.
- To create nested groups of conditions, use the grouping buttons or drag conditions to existing groups.
- To exclude data from a report by grouping conditions, use the Not all match (AND NOT) or None match (OR NOT) options.
  - Not all match (AND NOT) is the negation of an AND group. For example, if a group has three conditions named A, B and C, the AND NOT operation is "NOT (A AND B AND C)."
  - None match (OR NOT) is the negation of an OR group. That is, if a group has three conditions named A, B and C, the OR NOT operation is "NOT (A OR B OR C)."
- To reorder conditions, drag them into position.
- To remove conditions, select them, and click Remove.
LQE note: If your condition uses an is not statement, your report might show different results than an equivalent data warehouse report. If you use the is not operator for a specific attribute value, the report shows only artifacts with different values from the specified one. If no value is selected for that attribute (the value is unassigned), the artifact is not included in the report.
To include artifacts with different values from the one you specified, and also artifacts with unassigned values, create two conditions and group them with Some match (OR).

Example:
if you group the conditions, you get the expected results, but the report takes longer to run.

Sometimes several projects use the same custom attribute, and although the attribute has the same name across the projects, its ID is different in each project. To report on this attribute, add a condition for each project. Then, to consolidate them in your report, group the attributes by using an OR condition.
Example: Each project that you report on has a risk status attribute, and it means the same to each project. Select this attribute for all the projects; then, group the attributes, and add an OR condition between them. To show the results in one column, instead of one column for each project, see the Show the report as a table section.

Tip: To report on requirements in specific modules, from the attributes list, select the requirement or requirement type, and choose the Collection or Module attribute; then, choose the collections or modules, and click Save.

After you set your conditions, click Continue.

Step 2. Format results

Edit online

Procedure

Format the results to determine whether to show the report as a table or a graph.
Click Refresh to see a sample layout of your report. To see all the data, you must run the report.

Step 2a. Show the report as a table

Edit online

Your report already has some columns, including columns for the attributes you created conditions for.

Procedure

Add attribute columns: Select the artifact type from the list, and the attributes to add to the report. Then, click Add.
To link directly to the artifacts in the lifecycle products, include these attributes in the report:
- Name and URL, if you use Data warehouse
- Title and URL, if you use Lifecycle Query Engine
Add calculated value columns: Add columns that show calculations such as averages, sums, or counts.
1. Select the artifact type.
2. Select the calculation to show.
3. Select the attribute for your calculation.
4. To add other calculated values, click Add. Otherwise, click Add and Close.
Example: To count the defects that were created in a specific time range, select Count number of artifacts by ID > Limit (Count all artifacts with selected attribute values) > Creation Date. Then, set the time range.
Add custom expression columns if you are a report manager, and if your data source is configured to allow report managers to edit queries: To show the data in the form that you need, you can add columns that use custom expressions written in the query language of your data source. You can also display combined data from multiple properties of the same artifact and properties of multiple artifacts from the same traceability path in the columns. For example, you can add a column that calculates the difference between two dates or combines multiple fields in string format.
1. Select the artifact type from the list.
2. Select the attribute to add to the report.
  Currently you must select an attribute before entering the expression directly in the Custom expression field.
3. Click Add.
4. Modify the expression in the Custom expression field.
  For LQE, you can pick a frequently used function from the Choose functions list to help you choose the functions quickly without worrying about the syntax of the query language. If you add one of these functions to the custom expression, you must replace the placeholder text. In the custom expression, position the cursor at the placeholder text, choose an attribute, and click Add; then, delete the placeholder text.
  You can also write the custom expression manually. The following example is a custom expression for counting the number of defects that took 6 to 10 days to be resolved:
```
SUM(IF(lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) > 5 && lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) <= 10, 1, 0))
```
  You can show the owner names on all the reports open for more than 10 days with the following expression:
```
GROUP_CONCAT(IF(lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) > 10, $Defect:Owner$ + ', ', ''))
```
5. Select the Contains an aggregate expression checkbox if your custom expression includes an aggregate expression.
  
  Note: Aggregate expressions calculate a single value based on a set of values such as SUM, COUNT, AVG. Each database vendor has its own set of aggregate expressions. Check the vendor documentation.
6. Optional: Specify how to display the values. You can use the default String.
7. Validate your expression.
8. Click Add to include the column in the report.
For more information, watch the following video:
Format columns:
- Change the column name: Type in the Column Label field.
- Arrange columns: Click the drag handle beside the column label, and drag it to another location, or use the controls in the Actions column.
- Adjust the column width: Click the column boundary in the table layout at the top, and drag it into position.
- Sort columns: Select the sort method from the Sort Type list. You can sort more than one column. To change the sort order, edit the number.
- Color-code your report: Click in the Color section to configure rules for highlighting specific data in your report. You can add multiple coloring conditions. If several conditions target the same cells in your report, prioritize them:
  - Within the same column: Drag a condition to adjust its priority. In a list, the priority increases from top to bottom. The first condition is met first. The last condition (at the bottom) can override the first one, so it has the highest priority.
  - Across all columns in your report: Use the Priority field to define the sequence to apply your conditions in. Enter a value from 1 (highest) to 9 (lowest). To give a condition the lowest priority, leave the Priority field empty.
- Combine consecutive columns: Enter the same column label for equivalent attributes that are grouped in one condition. Using the risk status example from Set conditions, for each instance of the attribute (for example Risk Status in QM), set the column label to Risk Status. When you run the report, one Risk Status column shows data from all the projects.
  Important: If the consecutive columns contain calculations, the labels are combined in the report that you export, but not on the Run Reports page.
- Remove columns: Use the controls in the Actions column.

Step 2b. Show the report as a graph

Edit online

Report Builder picks the graph type based on your selections so far. Experiment to find the best graph type. Typically, line graphs convey trend information effectively, but you might find that a bar chart (stacked or grouped) works better with your data.

Procedure

Before you can preview a graph, you must select a value for the unit of measure. You can also select the dimension.

Enter the chart title.
Select the category to show on the X-axis (horizontal), and enter a label name.
Typically, you select what you want to measure.
- If you use a date attribute in the Group data by attribute field, click Date scale, and choose what date range to show in the report: days, weeks, months, or years.
- To show data gaps, select the Fill in gaps with zero value checkbox.
For more information, watch the following video:
Select the quantities or units to show on the Y-axis (vertical), and enter a label name.
- Count all the results for the selected attribute, or select a unit to count. To compare different aspects of the value that you're measuring, select a dimension.
- If you use numeric values, add lines or bar segments.
If your graph has some values in a low range, and other values in a high range, one of these ranges might be difficult to see. In this case, you can magnify the values in this range to see the trend and variation of the graph by adding another axis. For example, if you are reporting on defect severity, and the number of Normal, Major, and Minor defects are in a large range (50-100) and the Blocker and Critical defects are in a smaller range (0-5).
1. To magnify the values for the smaller range, in the Y Axis column, change Blocker and Critical from Y Axis 1 to Y Axis 2.
2. The values for Blocker and Critical are drawn with another scale. Another Y axis is added with the same color as the label for these smaller values. The legend also shows the same color for labels and (Y axis 2) is added to the legend.
3. You can also add an axis for horizontal graphs and line graphs.
Adjust what data points to show in the graph.
- To show more data, click Attribute.
- To add calculations such as averages, sums, or counts, click Calculated Value.
  1. Select the artifact type, calculation, and the attribute for your calculation.
  2. To add other calculated values, click Add. Otherwise, click Add and Close.
- To show the data in the form that you need, you can use attributes and functions to build custom expressions.
Adjust the graph.
- Change the colors of the lines or bars on the graph to make the categories more visible, or to comply with conventions in your organization.
- Combine line and bar chart segments to show the dimensions in your report. For each segment specify the graph type: bar, line, or dashed line.
- In time series reports you can add goal lines to compare the current state or values with target values. When you use the Add lines or bar segments for numeric measures option, click Goal line. Choose a bar, a solid line, or a dashed line. Use an angled, curved, or an S-curve trajectory for the goal line. You can add several goal lines to your chart.
- In time series reports you can also add date lines to mark specific milestones. When you use the Add lines or bar segments for numeric measures option, click Date line and specify the date. For each date line, select the graph type for your date line: the default, line, or dashed line.
- To show values or totals on the chart, click Graph type, and select the appropriate checkbox.
When you finish arranging your report, click Continue.

Step 3. Optimize the report

Edit online

About this task

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.

If you want to manually edit the generated query, see this topic. You can also generate alternate queries and optimize your reports based on your requirements.

Procedure

Generate alternate queries using the following options. To find what works best, try turning these options on and off. The options appear only when you select Lifecycle Query Engine or Lifecycle Query Engine scoped by a configuration as the data source.

Select the Defer finding relationships with an alternative query checkbox to generate an alternate query that filters on source artifacts before finding relationships. Try this option if your data has one-to-many relationships in the Artifacts relationships section.
Select the Defer scoping by projects with an alternative query checkbox to generate an alternate query that filters on artifacts first before scoping by projects. Try this option if your data has large number of projects in the 'Limit the scope section.

For more information, watch the following video:

Step 4. Name and share the report

Edit online

Procedure

Give your report a name and a description. The description helps other team members find your report if it is public.
Tag your report to make it easy to find, or to group it with related reports. Each tag becomes a category on the All Reports and My Reports pages.
Specify how to publish the report:
- Public (publish to catalog): The report is in the Report Builder catalog. Team members can add the report as a widget to their Jazz dashboards.
- Private (publish to catalog and visible only to creator and owners): The report is in the Report Builder catalog. Only the report creator and owners can add the report to a dashboard and see it. Other users see the widget, but they cannot run the report.
- Private (visible only to me): The report is available only on the My Reports page.
Specify whether the default visualization for your report is a table or a graph. For example, if you select Graph, but then run the report to generate a table, the next time you run the report, the results are shown again in a graph.
Click Add owner.
Your report can have multiple owners who can modify the report, and assign other owners.
Click Save, and click Continue.

Step 5. Run the report

Edit online

To see the complete report, click Run report. Provide values for all required parameters or filters.

About this task

You can use the dynamic Filters option to edit the configuration or conditions set for the report. When you clear dynamic filters, the default choices from the report, which include the conditions specified in the "Set conditions" section and the projects selected in the "Limit the scope" section, are retained.

If you show the report as a table, you can add and change filters on columns by clicking the pencil Pencil icon icon.

After you apply filters to a column, you can see a pencil Pencil with an asterisk above it

icon with asterisk:

To change the filter values, click that icon.
To see which filter values are applied, hover over the column name.

If you choose an LQE-based data source in Step 1. Choose data and Report Builder reports don’t show the artifacts that you expect, show artifacts more than once, or show a different number of artifacts than application views, ask an application administrator to validate the TRS feeds for the application. See Validating TRS feeds and the LQE index.

If the report is incomplete due to different conditions, such as, data sources that are indexing, validating data, or being removed, appropriate error messages are displayed. You can rerun the report after the LQE processes are complete. The report also displays an information icon info icon

along with hover text, if LQE conditions affected the execution time of the report.

A report with error message and query execution time details hover text

For more information, watch the following video:

What to do next

To see your report in the list of other reports, click All Reports or My Reports.
To further edit your report, click a pencil in the My Choices pane at the right. Click Save to save your changes.
You can export your report to various document types (Microsoft Word, PDF, HTML). To do further editing on the report, export it to IBM Engineering Lifecycle Optimization - Publishing if you have a license. You can also export a report graph to an image file. See the related topics.