Multi-Table Reports

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

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

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.

The following sorting options are available:

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.

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.