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:

Designing Advanced Reports that are otherwise not possible to create using the standard SBM tables. Note that change history is not created for the virtual records, which limits the types of reports that can be created against the data. The following report types can be used: List, Distribution, Calendar Feed, and Summary reports.
Creating read-only items that can be used just like auxiliary table data. Items can be returned in searches, users can add notes and attachments to the items, and users can send e-mails from the items. In addition, the virtual auxiliary tables that are created can be used in single and multi-relational fields on other virtual auxiliary tables, but use caution when doing so—you must ensure that the TS_ID for each record in the table remains the same each time the view is generated.

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:

IIS Reset
View generation is invoked after you reset IIS.
ttadmin.exe /GenerateViews
Instead of restarting IIS, on the Application Engine server, open the command line, and then navigate to:

installDir\SBM\Application Engine\bin

Execute the following command: ttadmin.exe /GenerateViews
Promoting or deploying an associated process app for the first time
You can associate a view with an application via the app attribute in the view definition XML file. When the app attribute is present and it contains an application's UUID, views are generated when the application is promoted or deployed to the runtime for the first time. If the app attribute is used, but the application has not yet been deployed or promoted, views are not generated until the process app is promoted or deployed.

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:

Ensure that the XML files in installDir\SBM\Application Engine\ViewDefinitions are exactly what you want for views in Application Engine.
From the command line, navigate to: installDir\SBM\Application Engine\bin.
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.