Configuring Advanced Features → 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:
You can create one or more view definition XML files on the Application Engine server here:
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.
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:
View generation is invoked after you reset IIS.
Instead of restarting IIS, on the Application Engine server, open the command line, and then navigate to:
Execute the following command: ttadmin.exe /GenerateViews
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.
To fully remove views that have been removed from the XML:
When views are generated, logging messages are added to the ViewGenerator.log file located here:
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 and both Work Center and User Workspace.
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.
Copyright © 2007–2017 Serena Software, Inc. All rights reserved.