Creating New Reports

Procedure To create a new custom report:

1    In the Reports directory tree, select the folder in which you want the new report to appear (Requirements, Test Plan, Issues, etc.). This determines where the report will be stored in the directory tree.

2    Click the Create New Child Report button on the toolbar.

3    On the New Report dialog, enter the name of the new report. This is the name that will appear in the Reports tree.

4    Select the Share this report with other users checkbox if you want to make this report available to other users.

5    Enter a description of the report in the Description field.

6    Enter a timeout setting (in seconds) in the Timeout [s] field to specify the maximum time period that Issue Manager will wait for report executions to complete.

7    Select the corresponding result type from the Result Category drop-list. This setting specifies the database table and view that is to be filtered for the report. The following result types are available:

-   Issue - Returns issues (including imported issues).

-   Archived Issues - Returns archived issues.

8    Each result type offers a set of selection criteria. Based on the Result Type you have selected, specify an appropriate Selection Criteria for your report. These criteria typically group properties based on a view or some other intuitive grouping (e.g., custom properties).

9    From the Property drop-list, select the property that is to be filtered on. For some selection criteria, properties are dynamic (e.g., Custom Test Definition property).

10    Select an Operator for the query. The available operators depend on the property. The operators are =, <> (unequal), >, <, >=, <=, contains, not contains. Strings are always compared lowercase. Allowed wildcards for Strings are * and ? (where * matches any characters and ? matches exactly one character).

11    Select the Value that the query is to be filtered on.

12    (optional) To add an additional query string to this report, click the More button. An existing query string can be deleted by clicking the string’s Delete icon. When multiple query strings are defined, AND and OR radio buttons appear next to the More button. Use these radio buttons to define if the queries should be considered cumulatively (AND), or if only one query string’s criteria needs to be met (OR).

13    Click Next to configure report columns on the New Report dialog.

14    Click the Add Columns button to display the Add Columns dialog. All available report columns are listed. Select those that you wish to have included in the report and click OK (multiple columns can be selected by holding down the CTRL key).

Note All columns can be removed from the report by clicking the Remove All button.

15    The selected columns appear in tabular format on the New Report dialog. From here you can configure how each report column is to be displayed. For each column, specify a sort direction (ascending, descending, or unsorted) using the up/down arrows in the Sorting column.

16    When a column is selected for sorting, a drop-down list box is displayed in the Sort Order column that allows you to more easily edit the column-sort order. Set these numbers as required.

17    Give each column an Alias. This is the name by which each column will be labeled in the generated report.

18    With grouping you can take advantage of SQL aggregation features (e.g., selecting a number of elements or querying the total sum of values). Check the Group by checkbox on the column selection dialog to specify that SQL group by functions are to be applied.

19    Columns that are not selected for SQL Group by functions are set to aggregation by default (i.e., a single aggregate value will be calculated). From the Aggregation drop-list box, select the appropriate aggregation type (Count, Sum, Average, Minimum, or Maximum).

20    The Actions column enables you to move column listings up and down in the view. The Move Up and Move Down functions do not affect the outcome of the report.

Note Any report column can be deleted by clicking the column’s Delete icon.

21    Click Finish to complete your new report.

Advanced query writing

Advanced reports can be created via manual SQL coding. Virtually any reporting option is available if you know the database schema. Clicking the Advanced button hides the query string drop-lists explained in the section above and opens a Report data query field in which you can insert existing code or write new SQL code.

One approach is to begin query-string construction using the drop-lists as outlined above (if the report criteria are valid, the equivalent SQL statement will be generated and displayed), and then to move to advanced mode for further modifications.

Note Note that you cannot move from advanced mode back to simple mode.

Procedure To write an advanced query directly in SQL:

1    In the Reports directory tree, select the folder in which you want the new report to appear (Requirements, Test Plan, Issues, etc.). This determines where the report will be stored in the directory tree.

2    Click the Create New Report button on the toolbar.

3    On the Create New Report dialog, enter the name of the new report. This is the name that will appear in the Reports tree.

4    Select the Share this report with other users checkbox if you want to make this report available to other users.

5    Enter a description of the report in the Description field.

6    Click the Advanced button to open the Report data query field. Insert previously written code as necessary, or write new code directly in the field. To assist you in writing SQL queries, a drop-list of Issue Manager function placeholders is available. See the following section for details regarding available placeholders. To insert one of the available pre-defined functions, select the corresponding placeholder from the Insert placeholder drop-list.

Note If you manually edit SQL code for the query, it is recommended that, once complete, you click the Check SQL button to confirm your work.

7    Once you have completed editing the report’s properties, click Finish to save your settings.

Available functions

To assist in writing advanced queries, placeholders are available for each function. Function placeholders are replaced with SQL code upon execution. Functions are used like parameters, but their names have a $ (dollar symbol) as a prefix.

Note Unlike parameters, placeholders are defined report elements that cannot be customized per execution.

The following table lists all available function placeholders:

Sample custom report

Below is the code of the pre-installed report All issues modified by <user> in <month>.

Although not a custom report, this report is a helpful example because it makes use of the $THISMONTH function (issueHistoryProp_MonthCreated) and the username parameter (issueHistoryProp_UserCreated).

SELECT r.DefectID, r.Synopsis, r.Status, r.InboxAssigned, r.Severity, r.ProductCode, r.Component, r.ProjectID FROM IM_V_Defects r INNER JOIN (SELECT DISTINCT issues.IssueID_pk as id FROM IM_V_Defects issues WITH (NOLOCK) INNER JOIN IM_DefectHistory history WITH (NOLOCK) ON history.IssueID_fk = issues.IssueID_pk WHERE issues.ProjectID = 98 AND history.MonthCreated like '${$THISMONTH|issueHistoryProp_MonthCreated}' AND LOWER(history.UserCreated) like 'username') data ON r.IssueID_pk = data.id ORDER BY r.DefectID ASC