Multi-Table Reports

Multi-Table System Fields reports enable you to query system fields to list items from multiple primary and auxiliary tables. For example, you can create a report that shows all items in the Issues and Incidents tables that were submitted after a particular date. The system fields in each table vary depending on the type of table you create the report against and the system fields added to each table by your administrator.

Tip: A second method of searching across tables is to add a Join condition to your report. This allows you to report on items that are related to a main item through relational fields. For details, see Including Data from Related Items.

Content Options

The following sections describe options available in the Content area for Multi-Table reports.

  • Tables to Search

    Select multiple primary and auxiliary tables to search against. You must select at least one table before you can run the report.

    If you save a Global Search as a report, the tables specified on the Search page are added automatically to the Tables to Search box.

  • Multi-Table System Fields

    Select system fields to display as columns in your report. The Fields list contains all system fields that are available in all primary and auxiliary tables in your system, except for the State field.

  • Optional Width

    By default, the width of field columns is set automatically based on the size of your browser window and the number of fields displayed on the report. To change the field, select a field, and then specify an absolute width in pixels to force the column for the field to be larger or smaller. Repeat for each field as needed.

Search Filters

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

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.
  • 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 filter field values based on field-type dependencies and the project selected for the report. When you add an independent field to the search filter, the value you select determines the available values for any dependent fields that you add to the search filter.

    Note: Changing this setting does not affect values that have already been selected.
  • 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.

    The following information applies to using Basic Conditions with Multi-Table reports:

    • The Query at Runtime option is not available for Multi-Table reports.

    • The Fields list contains all system fields that are available in all tables in your system. Search criteria that is not applicable to a table is ignored. For example, if your report criteria includes the Companies table and search criteria for the Owner field, which is not available in the Companies table, your search criteria is ignored for that table.

    • The name provided by SBM identifies system fields available for Multi-Table reports; these names may be different than those provided by your administrator. For assistance in using system fields in Multi-Table reports, contact your administrator.

    • If you saved a Global Search as a report, your search criteria is added to the Search Filter area. Keep the criteria, modify it, or remove it before running the report.

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

    Note: The Multi-Table report only supports the pass-through method @ modifier when you use an Advanced SQL condition. Privileges granted by your administrator determine if you can create a Multi-Table report using Advanced SQL conditions.

Sorting Options

Sort options enable you to sort search results based on values in selected fields. For example, you can sort items by the last modified date.

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 Content Options.

The following sorting options are available:

Option Description
Sort by / Then by From the drop-down lists, select the system fields for which you want to sort report results based on field values. The list contains all system fields that may be available in all tables in your system. Sorting criteria that is not applicable to a table is ignored. For example, if you choose to sort by Last State Change Date for a primary table and two auxiliary tables, which do not contain this field, the primary table items are sorted by Last State Change Date, but the auxiliary tables are not.
Ascending / Descending Select one of these options for each field to specify its sort order.
Always Perform a Primary Sort by Project Hierarchy Select this check box to sort primary items in the report by project hierarchy. If you clear the check box and do not provide other sorting criteria, items are listed randomly. This option only applies to primary tables.

Additional Options

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

  • 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 ....
  • Hide Project Titles

    Select this check box to prevent the project hierarchy headers from appearing in the report. You must select this check box if you select the Always Perform Primary Sort by Project check box.

  • Remove line breaks from memo/text fields

    Select this check box to remove line breaks from the system Description field, if used in the report. This option is useful if you plan to export the report data another application, such as Microsoft Excel.

  • 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;.
  • 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.

Results

Multi-Table reports return a list of items based on your report criteria. You can click item links within the results to view detailed information about each item.

The manner in which Multi-Table reports display depends on the display settings in your user profile.

The following information and options appear in the Item List pane for a Multi-Table report.

Field Description
Sorted by Indicates the sort criteria specified on the report form. The arrows indicate if the data is sorted in ascending or descending order. If you did not specify sort criteria when you created the report, for primary items, it is sorted by default, by project hierarchy first and then 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.
Project Hierarchy (For Reports That Contain Primary Tables) Project hierarchy is included as column headers if you did not select the Hide Project Titles check box. The project hierarchy displays 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.
Headings Project hierarchy is the heading for primary items if you chose to display the hierarchy in the report. If you did not choose to display the project hierarchy, the heading for primary items is the primary table name. The heading for auxiliary items is always the table name.
Items The fields that display for each item depend on the selections made in the System Fields to Display drop-down lists on the Multi-Table Report form. The first field always appears as a link, which you can click to view detailed information about the item. A check box is provided to the left of the item; select this check box to create a link to the item in a folder.
Requery

Click this link to requery the database and add the latest information to Item List pane.

Tip: If you do not have privileges to view data from specific fields, the information appears as asterisks in the report.