Custom Listing Reports

Key Benefits

List format offers easy navigation and drill-down capabilities.
Enables you to repeat searches, returning a listing of all items based on the filter that you define.
Listing reports let you search data found in related items using Join conditions.

Content Options

The following options are available in the Content area for Listing reports:

Report Item Type
Contains the primary table for the selected application and any auxiliary tables for which you have privileges. Select the table that contains the information for the report.
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.
Joins
Refer to: Adding Join Conditions.
Note:
- For information on why you would use the join condition, see Including Data from Related Items.
- Join conditions are not supported with some custom templates, such as editablegrid.htm. Selecting a non-supported template will disable join conditions.
- The Add button may disabled due to the following conditions:
  - The application has no relational field.
  - All of the relational fields point to restricted tables which you do not have privileges to view tables or created reports.
- Existing Join conditions can be modified or deleted by selecting them and clicking Delete or Change.
Select Columns to Display
Refer to Selecting Fields to Display as Columns in the Report.

Search Filters

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	Allow 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.

Sorting Options

Sort options enable you to sort search results based on values in selected fields. For example, you can sort items by the state they are in or by their active/inactive status. You can also choose to sort results by project hierarchy and enable dynamic column sorting.

Tip: The fields you select for sorting are independent of the fields you select to display. For best results, you may want to display the fields you choose for sorting. For details, refer to Selecting Fields to Display as Columns in the Report.

The following sorting options are available:

Option	Description
Sort by/Then by	From the drop-down lists, select the fields for which you want to sort report results based on field values. To specify the sort order for each field, select Ascending or Descending from the drop-down list next to the field.
Always Perform a Primary Sort by Project	If you include multiple projects in the report search filter, select this check box to sort items by project hierarchy. If you clear the Always Perform a Primary Sort by Project check box and do not provide other sorting criteria, items are listed randomly.
Enable Dynamic Column Sorting	Select this check box to display column headers as links. Click to sort the data in ascending or descending order. Your administrator determines if this option is available. Tip: To sort projects alphabetically rather than hierarchically, add the Project field to the Select Columns to Display box on the Content area of the form, clear the Always Perform a Primary Sort by Project check box, and then select the Enable Dynamic Column Sorting check box. After running the report, click the Project column to sort the items alphabetically by project.

Additional Options

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

Add Column of Linked Data
The check boxes in this section enable you to view links to files, URLs, notes, and item links attached to items returned in the report. An Associated Attachments column appears in the report when at least one of the following check boxes is selected:
- Include Linked Files From Attachments – Select this check box to display links to any files attached to items. Links only display if you have privileges to view attachments.
- Include Linked URLs From Attachments – Select this check box to display any URLs attached to items. Links only display if you have privileges to view attachments.
- Include Linked Notes – Select this check box to display any notes attached to items. Links only display if you have privileges to view notes.
- Include Linked Items – Select this check box to display any links to other items associated with the item being viewed. Click the item link to view the item in the Item Details pane. Links only display if you have privileges to view the linked item.
Add Columns of Calculations
You can include calculations on Numeric, Binary/Trinary, and Date/Time fields in your report results. The calculations available are addition, subtraction, multiplication and division. For details, refer to Calculations in Listing and Multi-Table Reports.

To add a calculation to a report:
1. In the Column box, type the name that will appear as the Column Header in the report.
2. In the first calculation box, select a field that will serve as the first operator from the drop-down list.
3. Select an operand from the next list.
  Note: Date fields can only be subtracted. Multiplying, dividing, and adding date fields will yield an error.
  
  Note: For any calculation you create that begins with a Date/Time keyword (like now or startof_thisweek), you must ensure that the keyword is the first token in the expression. For example, the following expression:
  Submit Date = (now Minus 30)
  
  Should be specified as (without the parentheses):
  
  Submit Date = now Minus 30
4. In the second calculation box, select a field that will serve as the second operator from the drop-down list.
5. Click Add New Calculation to add another calculation, if desired.
Tip: For Date/Time fields, click the Date/Time Keywords link to select a Date/Time keyword, such as Now or Startof_NextWeek, for your calculation.
Important: Certain calculations are not possible, such as multiplying, dividing or adding dates or Date/Time Keywords. Invalid calculations will result in an error when you run the report. The errors may prevent the report from running at all, resulting in the following message:
```
An error occurred while processing the last request.

The error was:

Database exception in ....
```

Display Options
You can choose to hide project titles, remove line breaks from certain Text fields, add a footer, and more. The following display options are available:
- Hide Project Titles – Select this check box to prevent the project hierarchy headers from displaying on the report. You must select this check box if you select the Always Perform a Primary Sort by Project check box.
- Remove line breaks from memo/text fields – Select this check box to remove line breaks from Text and Memo fields in the report. This option is useful if you plan to export the report data to another application, such as Microsoft Excel.
- Footer – Text added here appears as a footer on the report results page. If you save a Basic Search or Advanced Search as a report, your search criteria is added to the Footer box on the report form by default. The footer is limited to 255 characters (bytes).
  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: <Some Text>.
- Font Size - Select a font size from the drop-down list to override the default font size.
- Optional HTML Template
  This drop-down list contains HTML templates for customizing the look of your report. Provided templates include excellist.htm, which improves the display of Listing report results when they are exported (by right-clicking the report output and then selecting Export to Microsoft Excel), and massselectlist.htm, which provides buttons at the top of the page, enabling you to select or clear the check boxes for all items in the results list. Your administrator can customize report templates, and the templates in the list may or may not apply to the type of report you are creating. Select the template from the drop-down list.
  Note: This option is available for applicable report types in the SBM User Workspace and for Listing reports in SBM Work Center.
  
  If you change the HTML template in a report that is pinned to a menu or on a dashboard in Work Center, the change is not reflected in the affected report. You must re-pin or re-add the updated report after you save the template change.
Tip: Select the editablegrid.htm template to display results in a grid view. This enables you to update multiple items at once from the results page.

Note: Join conditions are not supported with all templates. Selecting a non-supported template will disable the join condition.

Results

Listing reports return a list of items based on your report criteria.

By default, the list of items returned by the report opens in the Item List pane. You can then click an item link to view detailed information in the Item Details pane.

Images added to Text fields are shown in each row. You can collapse each row to hide images.

Result Options

The following information and options appear in the Item List pane for the default Listing report view.

Field	Description
Sorted by	Indicates the sort order specified on the report form or by clicking the column headers that appear as links if dynamic column sorting is enabled. The arrows indicate if the data is sorted in ascending or descending order. If you did not specify sort order when you create a report for primary items, the report by default is sorted by project hierarchy first, and by item ID in ascending order. A default sort order is not provided for reports created for auxiliary items, but the sort criteria appears as specified on the report form or by clicking the column headers that appear as links.
Project Hierarchy (For Primary Item Reports)	The project hierarchy is included as column headers if you did not select the Hide Project Titles check box on the report form. The project hierarchy is provided for each project in the item list, and primary items that match the report search parameters are listed in the project in which they reside. Projects in the hierarchy that do not have items that match the criteria do not display.
Dynamically Sortable Columns	Enable Dynamic Column Sorting check box on the Listing report form. Click a column label to sort the field data in ascending or descending order. If items from multiple projects are listed, the sorting applies to all projects. Note: Your administrator can disable dynamic column sorting.
Items	The fields that display for each item depend on the selections made in the Select Columns to Display area on the Listing report form. The first field always appears as a link, which you can click to view detailed information about the item in the Item Details pane. To create a link to the item in a folder, select the check box to the left of the item and click the Create Link In button at the bottom of the page.
Details	Click this button to view detailed information about all of the items listed in the pane. To return to the Item List pane, click the Listing button. Tip: If you do not have privileges to view data from specific fields, the information appears as asterisks in the report.

Using the Editable Grid

Click the Use Editable Grid link to open your report results in a grid format, enabling you to update multiple items at once. Once you have made your updates, click the Back to Listing link to return the the Item List pane.

The Use Editable Grid link may be located in the Actions menu or on the report results page.

Note: The Use Editable Grid link is not available for Listing reports included in Multi-View reports. Click the zoom-in icon (

) on an individual Listing report to expand the report to the full pane and enable the Editable Grid.

Join Condition Results

If the report includes a join condition, the display of the results has some differences from a normal listing report.

An additional column appears which allows you to choose which item to display. Display a related item by clicking and choosing the related table where the item is located.

The report results may contain multiple entries for a particular item. This is due to the item containing multiple values in the relational field that is selected for the join condition. The report treats each selection in a multi-relational field as a positive result for the filter.

For example, if you have included linked Change Requests in your join condition and the DEF000001 has a multi-relational field with change requests CHG000002, CHG000003, and CHG000004 selected, your results will include three entries for DEF000001. Each entry corresponds to a different change request. If you select to display the related item, the item corresponding to the entry will display.

Item ID (Defects)	Item ID (Change Requests)
DEF000001	CHG000002
DEF000001	CHG000003
DEF000001	CHG000004

Data that you do not have privilege to view will appear as *****. This is due to you not having privileges to view the data in the related table. If you attempt to modify the report, you will not be able to modify or add join conditions to a table which you do not have privileges to.

For more information on join conditions, see Including Data from Related Items.