Creating Query Views for Advanced Reports

Administrators with advanced knowledge of SQL can create queries that are bound as read-only auxiliary tables (views) in the SBM database, which users can access to create advanced reports. These virtual tables can potentially include data from any table in the SBM database, and you can write SQL queries that join data from tables outside the SBM database as well.

The SQL that is used to generate the views is defined in XML files that are hosted on the Application Engine server. When view generation is invoked by SBM, the query is executed, and the data is made visible in a way that allows SBM to treat it like any other auxiliary data in the system.

Common use cases include:

Note: Creating Advanced Reports requires advanced knowledge of SQL and the SBM schema that is beyond the scope of this guide. For technical details, including sample XML, important information about SQL queries, logging levels, and more, refer to solution S141342. Visit Community website to view specific use cases for creating Advanced Reports.

Getting Started

Note: This section applies to on-premise installations only. For assistance in an on-demand environment, please contact Professional Services.

You can create one or more view definition XML files on the Application Engine server here:

installDir\SBM\Application Engine\ViewDefinitions

Each file can have one or more views defined, and each view entry in the XML must have dbname, name, and singleItemName attributes.

Application Engine processes the XML content from top to bottom; therefore, if tables have relational fields to other views, place them in logical order in the XML. Application Engine processes the XML files in alphabetical order, but view definitions that relate to each other should be placed in the same file.

Once you have created the view definitions that you need, the views need to be generated and made available in SBM. For details, refer to Generating Views.

Generating Views

The views that you create are generated at various times by Application Engine. When view generation is invoked, Application Engine processes the XML view definition files and creates values in the TS_TABLES, TS_FIELDS, TS_SELECTIONS, and TS_FIELDORDERINGS tables, which creates the necessary metadata for Application Engine to use the SQL as a virtual table.

View generation is invoked after any of the following:

Important: Any time the view generation is invoked, Application Engine will process every XML file in the ViewDefinitions folder and update existing views from that definition. If a field has been defined from previous XML, but is no longer found in current XML, the field is marked as deleted and removed from the TS_FIELDORDERINGS. The same is true for selections that are no longer found in the current XML. However, if an entire view is missing from the XML, it is ignored (and not deleted) in order to prevent loss of data.

To fully remove views that have been removed from the XML:

  1. Ensure that the XML files in installDir\SBM\Application Engine\ViewDefinitions are exactly what you want for views in Application Engine.
  2. From the command line, navigate to: installDir\SBM\Application Engine\bin.
  3. Execute ttadmin.exe /GenerateViewsAllowDelete. This permanently deletes any view not found in the XML.
    CAUTION:
    This does not delete notes and attachments that have been added to items in the auxiliary table; however, any notes and attachments that remain are orphaned and no longer belong to any items. Once you delete the view, if you add the XML back to the ViewDefinitions folder and invoke view generation, the views are re-generated in the TS_TABLES table with new TS_IDs, which means the orphaned data is not re-added to the newly restored views.

When views are generated, logging messages are added to the ViewGenerator.log file located here:

installDir\SBM\Application Engine\Log

Accessing the Tables and Creating Reports

Because SBM treats the view definitions as auxiliary tables, you must grant users and groups privileges to view items, create/modify reports, and add notes and attachments in order to interact with these views. After you save the view definition and reset IIS, the new tables appear on the Privileges | Tables tab in Application Administrator. Grant table privileges to the users and groups as appropriate, and then advise your users that the new tables are available in the global search in Work Center.

Once users and groups have been given the necessary privileges in Application Administrator, the new tables appear just like any other auxiliary table in the Report Item Type drop-down list that appears when you create a new report. The tables are also accessible to privileged users via Manage Data.

Reports that users create against the views can be promoted from one environment to another, but the views themselves cannot be promoted.