Custom Trend Reports

Trend reports provide historical totals or submittal rates of items over days, weeks, months, and quarters. Trend reports that track historical totals use the system's change history for generating report data, allowing you to track totals for specific fields over a period of time. Trend reports that track submittal counts allow you to generate a report that shows current value totals of items submitted during the specified time period. Trend reports appear as tabular or graphical reports.

Content Options

The following options are available in the Content area for Custom Trend reports:
  • Report Item Type

    Indicates the application the report is based on.

  • Report Project

    For reports based on primary tables, the project you select when creating a report determines which items are returned in your report. The project selection also determines where the report is stored and which users can access the report. For example, if you select Project A, only users who have privileges to run reports in Project A can run the report.

    For details, refer to Selecting a Report Project.

  • Report Style

    Choose how to display your report.

    Note: For graphical reports, labels may be truncated for fields that contain long selection values.

Trend Criteria

In the Trend On area, select one of the following options:
  • Historical Value Counts

    Select this option to generate a report that shows historical value totals of all items during the specified time period. For example, you can create a report that shows which types of items are active in the system at one-week intervals. To create this report, select the Issue Type field from the 1st Field list, the Active/Inactive field from the 2nd Field list, and then select Weeks from the Total By list.

  • Submittal Counts

    Select this option to generate a report that shows current value totals of items submitted during the specified time period. For example, you can create a report that shows the types of items that were submitted each quarter. To create this report, select the Issue Type field from the 1st Fields list, and then select Quarters from the Total By list.

  • 1st Field and 2nd Field
    From the drop-down lists, select the fields that you want the report to trend on. The list contains Binary/Trinary, Single Relational, Sub-Relational, Single Selection, and User fields. If you do not select fields from the drop-down lists to trend on, the report trends on the total number of items or submitted items found in the selected projects during the specified period of time. Available Sub-Relational fields include those that point to Single Relational, Single Selection, and User fields.
    Note: To suppress values for the field you select, use the options available in the Sorting section. For details, refer to Sorting.
  • Total By

    Choose to increment results in days, weeks, months, or quarters. The date and time selected as a start date determines the beginning of the increment period. For example, if you specify Feb. 15 as a start date and select Quarters from the Increment drop-down list, report results are organized by quarter beginning with Feb. 15. For monthly increments, if you select a start date that does not apply to all months, such as Jan. 31, that date is only used when it applies. In addition, the specified end date is increased when needed to reflect an entire time period. If your last column starts at 3/30/2013 at 5 a.m. and you selected a monthly increment, the end date for the last column is 4/30/2013 at 5 a.m. For details on selecting start and end times for Trend reports, refer to Search Filter.

  • Sum Totals By
    Enables you to see the sum of certain field values rather than count each item returned by the report as one. The following table lists the applicable field types and examples.
    Field Type Notes Example
    Date/Time Applies to Elapsed Time fields only. Use to track the amount of work spent on a set of items, such as a team's work for a sprint.
    Numeric Use to track the amount of money spent on a set of items or the number of sprint points allocated for a set of items.
    Single Selection Applies only to weights assigned to Single Selection field values. If weights are not applied, each item is counted as a single item.

    Contact your administrator to learn about the weights assigned to Single Selection fields in your system.

    A Priority field may have these values and assigned weights: of High - 100, Medium - 50, and Low - 10. Each item returned by the report is counted using the weight from its Priority field selection.
  • Time Period

    Specify a time period for the report. You can either specify a fixed period using a date range or a time period based on a number of days, weeks, or months that have passed prior to running the report.

    • Fixed Period
      • Start Date

        This field is required; however, the time of day is optional. Click the calendar icon to select a date, enter a date specification using the format indicated below the input field, or click Date/Time Keywords to use date values that are recalculated instead of a fixed date when the report is executed. You can also type custom values, such as Minus 5 or Plus 5 (with a different number, if desired) to use custom plus and minus values.

        Select the Query At Runtime check box to require users to enter the Start Date when the report is executed.

      • End Date

        This field is optional. If you do not specify an end date, the report uses the current date. Click Date/Time Keywords to use date values that are recalculated instead of a fixed date when the report is executed.

        Select the Query At Runtime check box to require users to enter the End Date when the report is executed. You can also type custom values, such as Minus 5 or Plus 5 (with a different number, if desired) to use custom plus and minus values.

    • Relative Period
      • Last

        Enter a number of preceding days, weeks, or months to limit report results.

  • Show Labels and Calculate Time Intervals according to User's Time Zone

    Select this check box if you want column labels to display times and calculate time intervals in the time zone selected in the user profile for the user running the report. Clear the check box if you want labels to show times in Universal Coordinated Time (UTC).

Search Filter

Search filters enable you to narrow your search for items. Depending on your privileges, you can define basic conditions or Advanced SQL Conditions.

Note: You can change the type of condition the report uses, but your search criteria is not converted. For example, if you specify an Advanced SQL condition, and then select the Use Basic Conditions option, your Advanced SQL is not converted to a basic condition.

Select the following options for creating a search filter for your report:

Option Description
Include Items From Sub-projects Select this check box to include items from sub-projects of the project selected from the Report Project list. This option is not available for reports created against auxiliary tables.
Show Search Filter In Results Select this option to display your Search Filter settings in the output of the report. This option allows report viewers to see which filters are used in the report. It is also helpful when you select to print a report, since it displays which Query At Runtime parameters were selected.
Limit selections using field dependencies and project selections

Select this check box to use the project's field dependency rules.

For example, in Project A, the Priority field has a dependency with the Severity field. The dependency states that when the Severity field is Critical, then the Priority field must be 1 or 2.

You create a report on Project A and add a Search Specification of Severity in Critical. Then, you add a Search Specification for the Priority field. The available Field Values for the Priority field will be limited to 1 and 2.

Changing this setting does not affect values that have already been selected.

Restriction: Dependencies are only honored for fields that have the Allow searching option selected in the field definition.
Use Basic Conditions Enables you to define a search filter by making selections from the list of fields. For detailed information about using basic conditions, refer to Using Basic Conditions in SBM Reports.
Use Advanced SQL Conditions

(on-premise customers only)

SQL (Structured Query Language) is an industry-standard language for selecting records from a database. For detailed information about using Advanced SQL conditions in SBM reports, refer to Using Advanced SQL Conditions in SBM Reports.

About Current Values

When using search filters, remember that trend reports are based on the current values of the item. For example, if your report filters on Active items, and the report shows the following row for High Severity:

  Week 1 Week 2 Week 3 Current Week
High Severity 3 4 4 5
If you close a currently active item that was submitted in Week 1, then the report will change as follows:
  Week 1 Week 2 Week 3 Current Week
High Severity 2 3 3 4

Sorting

By default, all values for fields selected from the 1st Field and 2nd Field lists appear in the report. You can suppress and reorder these field values. however. For example, if you select the Submitter field for the row, you can choose to display the number of issues submitted by specific users instead of all users who submitted issues.

To suppress 1st Field and 2nd Field values:

  1. In the Sorting section of the Trend report form for a custom Trend report, click the Modify button in the Show Only 1st Field Values area.
  2. The field values for the field selected in the 1st Field list appear in the left pane. Select the values that you want to appear in the report, and then click the right arrow to move the values to the right pane.
    Tip:   If there are more field selections than can be returned based on administrative settings, use the Value Find feature to search for values.
  3. Use the up and down arrows to reorder the values as needed.
  4. Click OK.
  5. Click the Modify button in the Show Only 2nd Field Values area.
  6. The field values for the field selected in the 2nd Field list appear in the left pane. Use the Value Find as needed. Select the values that you want to appear in the report, and then click the right arrow to move the values to the right pane.
  7. Use the up and down arrows to reorder the values as needed.
  8. Click OK.

Additional Options

The following options are available in the Additional Options area of the Custom Trend report form. These options can be considered "advanced" report options.

  • Use Logarithmic Scale (Graphs only)

    Select this check box to apply a logarithmic scale to your report. You may want to do this to reduce your report results to a manageable range if they cover a large range of values. This option only applies to certain report styles, such as line, scatter, and bar charts.

  • Add Trend Lines (Graphs only)

    Select this check box to overlay report data with a linear regression that enables you to determine whether the numbers are increasing or decreasing. This option is best used with line charts and scatter charts, and is not available for tabular charts, horizontal bar charts, and percentile stacked bar charts.

  • Show Whole Rows and Columns of Zero (0)

    By default, Distribution reports do not show rows that have only zeros as values. Select this check box to show the rows that only have zeros as values.

  • Show All Labels

    Some display labels are skipped if a large number of values is returned on the horizontal axis. Select this check box to show all labels. If the labels are difficult to read, clear this check box to show fewer labels. This option does not apply to tabular reports.

  • Show Chart Values

    Select this check box to show the number of items returned for each row on the graphical report. If a large number of values are returned, they may be difficult to read. You can clear this check box to show fewer values. To see values, hover your mouse over a bar or pie segment to view the number of items returned. This option does not apply to tabular reports.

  • Thresholds

    Thresholds allow you to apply visual indicators to your report that inform you when data rises above or falls below numbers you specify. Apply a color to each threshold that appears in the background of the report.

    You can set a high threshold, a low threshold, or both. Thresholds cannot be used with percentile stacked bar charts, pie charts, doughnut charts, heatmap charts, or radar charts. The following threshold options are available:
    • Set High Threshold Greater Than – Type a number that indicates a high threshold when the report data is greater than that number. For a report that tracks the number of items submitted by particular users, you may want to set a high threshold that indicates when over 75 items have been submitted.

    • Set Low Threshold Less Than – Type a number that indicates a low threshold when the report data is lower than that number. For a report that tracks the number of items submitted by particular users, you may want to set a low threshold that indicates when fewer than 10 items have been submitted.

    • Using Color – Click the Color link for each threshold to open the Select Color dialog box and select a color for each threshold. You must select a color if you set a high or low threshold. The color for each threshold appears in the background of the report.

  • Footer
    Optionally, supply a footer for your report.
    Note: The footer renders some common HTML tags such as <i>, <b>, and <font>. However, this means that character entity references such as >, <, ", and & are not encoded in the footer. Therefore, in order to display <Some Text> in the footer, you must send the following encoded sequence: &lt;Some Text&gt;.
  • Choose Colors for Values

    Choose custom colors for values for the !st Field you selected on the Content tab. If you do not select colors, random colors are used.

Drill-Down Display Options

The following options are available in the Drill-Down Display Options area of the report form. They enable you to specify which fields to include in the drill-down reports as well as column widths and sorting criteria.
Note:
  • Drill-Down Display Options may not be available if your administrator has disabled Flash components or in browsers that do not support HTML5.
  • You cannot drill down into a report when it is contained in a Drill Through report and used to drive another report. See Drill Through Reports for more information.
The following table describes each of the fields that you use to specify drill-down display options:
Field Description
Use Value Display Format Select this check box to use the default columns for the drill-down report. Clear the check box to enable the Select Columns to Display feature.
Select Columns to Display Select the fields that you want to appear as columns in the drill-down reports. To select or deselect a field, move it to or from the right-hand box using the arrow buttons.
Set Custom Field Widths Optionally, specify the width (in pixels) for the columns in the drill-down reports.
Sorting Select the fields on which you want to sort the data in the drill-down reports.

Results

Custom Trend reports return a tabular or graphical representation of items based on your report criteria.

The following information and options display with Custom Trend report results.

Field Description
Show TSV Click this button to open a window that displays the report in a tab-delimited format. This enables you to copy the report information and paste it into a spread sheet application, such as Microsoft Excel.
Note: This option is not available for graphical reports. Alternatively, you can choose the Excel option to export report results.
Graph or Table Depending on the style you selected when you created the report, a table or graph showing the data that meets the report parameters displays.
Data Set Labels For graphical reports, data set labels display for the report graphic. These labels use a color that corresponds to a category or column in the report.
Date/Time Labels Labels for the date/time columns on the report depend on the time increment you reported on and whether you elected to display dates and times in local time or Coordinated Universal Time (UTC). If you selected the Show Labels and Calculate Time Intervals according to User's Time Zone check box on the report form, the local time zone is shown for the time zone specified in the user profile for the user running the report. The heading for date/time labels indicates whether the report is using local time or UTC.
Trend Lines and Trend Line Labels If you selected the Add Trend Lines check box on the report form for a graphical report, trend lines display on the report background. Trend line labels display next to the data set labels.
Thresholds
If you configured thresholds on the report form, the color you indicated for each threshold displays on the report background.
Note: Note: If you do not have privileges to view data from specific fields, the information appears as asterisks in the report.
Note:

The Backlog trend report is similar to a Custom trend report where you trend on the Active/Inactive field; however, there may be a slight difference in the results. The reason is that the two reports define open and closed in different ways.

The Active/Inactive custom trend uses each item's Change History to see when the item changed from active to inactive and from inactive to active. This change often occurs automatically as items move from active to inactive states and from inactive states to active states. The Active/Inactive field can also be changed manually by a user if the field is set as editable.

The Backlog trend report determines an item is open or closed by looking at how it moved from state to state in the past. It uses the current Active/Inactive setting on the state definition to determine if an item that was in that state a year ago was active or inactive at that time. This can lead to misleading categorization because state could have changed from being active to being inactive, or vice versa, during the past year. Also, the Backlog report does not take into account the possibility of manually altering the Active/Inactive field on individual items.

Trend Report Results

Trend reports return a tabular representation of items based on your report criteria.

The following information and options display with Trend report results.

Field Description
Show TSV Click this button to open a window that displays the report in a tab-delimited format. This enables you to copy the report information and paste it into a spread sheet application, such as Microsoft Excel.
Note: This option is not available for graphical reports. Alternatively, you can choose the Excel option to export report results.
Data Set Labels For graphical reports, data set labels display for the report graphic. These labels use a color that corresponds to a category or column in the report.
Date/Time Labels Labels for the date/time columns on the report depend on the time increment you reported on and whether you elected to display dates and times in local time or Coordinated Universal Time (UTC). If you selected the Show Labels and Calculate Time Intervals according to User's Time Zone check box on the report form, the local time zone is shown for the time zone specified in the user profile for the user running the report. The heading for date/time labels indicates whether the report is using local time or UTC.
Note:

The Backlog trend report is similar to a Custom trend report where you trend on the Active/Inactive field; however, there may be a slight difference in the results. The reason is that the two reports define open and closed in different ways.

The Active/Inactive custom trend uses each item's Change History to see when the item changed from active to inactive and from inactive to active. This change often occurs automatically as items move from active to inactive states and from inactive states to active states. The Active/Inactive field can also be changed manually by a user if the field is set as editable.

The Backlog trend report determines an item is open or closed by looking at how it moved from state to state in the past. It uses the current Active/Inactive setting on the state definition to determine if an item that was in that state a year ago was active or inactive at that time. This can lead to misleading categorization because state could have changed from being active to being inactive, or vice versa, during the past year. Also, the Backlog report does not take into account the possibility of manually altering the Active/Inactive field on individual items.