Solutions

SBM 11+: Creating Query Views for Advanced Reports (SBM Advanced XML Report)



ID:    S141342
Published:    16 September 2015
Updated:    05 September 2019

Operating System(s)

  • All Windows

Product(s)

  • SBM
 

Description

Overview
 

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.
 

Quick Start Instructions


To implement views, you must:

  1. Create an XML file in the correct format as defined below.
  2. Copy the XML file to the Application Engine server to the ViewDefinitions folder (C:\Program Files\Serena\SBM\Application Engine\ViewDefinitions).  If the ViewDefinitions folder does not already exist, create it in this location.
  3. Generate the views as follows:
    NOTE: This step creates values in the TS_TABLES, TS_FIELDS, TS_SELECTIONS, and TS_FIELDORDERINGS tables with enough metadata for SBM to use this view as an AUX table.
    NOTE: Views are also generated when IIS is restarted, and when the associated application is deployed or promoted.
    • Open a command prompt.
    • Use the following command to navigate to the ttadmin.exe
      cd Program Files\Serena\SBM\Application Engine\bin
    • Execute the following command:
           ttadmin.exe /GenerateViews
  4. In Application Administrator, give privileges to the tables for users who need to run the reports:
    • For managed administrators: Administration > Table > Find the table defined in the XML file.
    • For everyone who needs access: Open the group > Privileges > Table > Find the table defined in the XML file.
  5. In SBM Work Center (or classic shell), create reports on these tables:
    NOTE: At this time, only List, Distributed, Calendar Feed, and Summary reports can be run against a view.
    • Select the appropriate table under Report Item Type.
    • Choose the correct columns to display.
    • Add your appropriate filter criteria and query at runtime parameters.
    • Run report.
  6. To promote your views to another environment:
    • In order to promote an advanced report to another environment, the application to which the report is defined against must already exist in the second environment. In other words you must have deployed or promoted the application at least once in order for the initial view to get created.
    • Once you have the application available in the other environment, you have to copy the query view xml file(s) to the other environment, and run the GenerateViews command. Simply promoting the application from one environment to the next does not automatically create the custom view in the new environment, that is a manual process.
    • NOTE: If you alter your .xml file in one environment and regenerate the view, you must also copy the new .xml file to your other environments and regenerate views there as well.
About the XML File


All views must be defined in the XML file with the following format: (See attached files for additional examples. If you want to use the attached .xml files make sure to modify the app uuid first to match one of your apps. See view | app tag for details) 

XML Tag Tag Properties Description / Information / Tips
 <!--  Comment Text  -->    It is a good practice to include a comment section at the top to explain and give details about this report. Standard XML comments can be used.
<views>   Container for one or more view definitions
     <view>

dbname - Required. Unique name to identify this view in the TS_TABLES table. It is recommended that you prefix the view dbname with "TSV_" to distinguish this entry from other tables in TS_TABLES.

name - Required. Plural name of the items returned by this view. This is also the name of the table as seen when giving permissions or creating reports based on this table.

singleItemName - Required. Name of a single item returned by this view.

notes - Optional. Can be used for internal awareness of which tables this view is making visible. This is ignored by the App Engine view generator. 

app - The UUID of the application to which this table should be bound. For example, if the information in this table will be displayed on a form, use the UUID of the application that contains the form. The UUID for existing applications can be found in the TS_APPLICATIONS table.

uuid - Required. The unique UUID to be given to this table. You can generate a UUID on-line at https://www.uuidgenerator.net/

Defines the properties for this view.

 More than one view may be defined within a single XML file.

          <sql>  

SQL used to populate this view/table.

To test your SQL, look at the Database Server tab of SBM Configurator. Log into your SQL tool with the same username/password given here. Then, run the SQL. This user must be able to run this SQL. If the SQL will not run within your SQL tool, it will not run within SBM. Note that even though it may run within your SQL tool, this is not a guarantee that it will run within SBM. If you get errors when trying to run the report from SBM, then look at the Windows Event Viewer.

* Each returned column must start with "TS_". Be sure to use SQL aliasing to accomplish this. For example, if your table's column name is ID you would use this syntax 
     select ID as TS_ID from table

* All views must include at least TS_ID, and TS_UUID columns. If your table doesn't have a UUID column you can allow the query to generate a UUID using syntax similar to the following:
     MSSQL: select ID as TS_ID, NEWID() as TS_UUID from table
     Oracle: select ID as TS_ID, SYS_GUID() as TS_UUID from table

* TS_ID must be a unique value. If TS_ID values are not always unique, see Multiple Columns to Create Unique Key for more details. Or, you could try using ROW_NUMBER(). The only trick with this is that you must also sort the results so the row number returned is always the same. For example:
      select ROW_NUMBER() over(order by ts_title ASC) as TS_ID
The column aliased as TS_ID must also never be null, but should also be greater than 0. You can use coalesce(xxx, 2147483647), or something similar, to adjust null values to a unique integer.

* User fields, Relational fields, Project fields, and Selection fields should not return null values. Use COALESCE to convert null values to 0. It is best to keep the SQL compatible with MSSQL and Oracle, so do not use ISNULL or NVL.

* Case statements are required if you define a selection field and want to convert the enum/integer values into the TS_ID of the actual selections created in the TS_SELECTIONS table. An example is given in the attached TablesAndReports.xml file.

* There are XML Variables which can be used to help simplify the SQL. See XML Variables for more details. NOTE: These variables are replaced by the Application Engine when the SQL is processed. MSSQL and Oracle will not understand these variables from within your SQL tool.

 

COMMENTS: If you want to add comments within the <sql> tag, you must use the standard SQL comment characters:
     --  Double dash for a single line comment
     /*  For multiple lines, wrap the comment with slash-asterisk, then asterisk-slash  */

 

          <valuedisplayformat>   Similar to the Value Display Format defined in SBM Composer for a primary or AUX table.
               <format></format> Example Value:  {0}: {1} Can be made up of text and indexed curly brackets which denote where the field values will be inserted.
               <fields></fields> Example Value:  ISSUEID,TITLE Comma separated list of fields (using the DBNAME) to be inserted into the format (starting with zero). For example, <format>{0}</format><fields>TITLE<fields>, would simply put in the title field. A more complex example would be <format>{0}, {1} {2} ({3}) -- {4}</format><fields>LASTNAME, FIRSTNAME, MIDDLENAME, EMAIL, COMPANY</fields>, which would format some sort of contact view to display a person's information like this: "Smith, Alex J. (AJSmith@serena.com) -- Serena, Inc.".
          </valuedisplayformat>    
          <fields>  

The fields section provides information about the columns that will be returned by the SQL. You do not need to define a field for each column returned. For example, you do not need to create a field for TS_ID or TS_UUID.

When a report is created using this table, the first column in the report will be a link to the details for that row. When you click this link, the fields will be listed in the order shown in the <fields> section.

               <field>

dbname - Required. The name of the column without the "TS_" in front. Every column returned from the SQL must be begin with "TS_", but the "TS_" must be removed from the dbname property. In other words, the SQL SELECT statement must alias every column to start with "TS_" (e.g.: TS_MYTEXTFIELD), but the dbname should only be "MYTEXTFIELD".

name - Required. The field name as seen when creating reports based on this table.

type - Required. See the Field Types section below.

size - Optional. Only used with text fields.

options - Optional.  in SBM 11.4 and higher, this enables you to specify field options as defined in TS_FIELDS.TS_OPTIONS.

target - Optional. Only used with type "relational" and "multi-relational"

syscode - Optional. Used to designate a field as a system field. For example, you may want a certain text column to be used as the system TITLE field. This is done by applying the appropriate "syscode" for the system field. See the Syscode section below.

precision - Optional. Integer used to denote the fixed precision of a number-fixed field.

hidden - Optional. The field can be set to hidden="true" to move the field into the Hidden section.

If a column in a table is a foreign key to another table, you can get the data from that foreign key using a join in the SQL, then bind to the result as if it were a text field.

If the value in a column is an integer that represents some enum that cannot be dereferenced via SQL, it is possible to define selections for the field by first making sure the field type is "selection" and then by defining selections in the XML (see the selection field type below).

 

Examples:
Enable Rich Text in a memo field:
<field dbname="DETAILS" name="Details" type="text-memo" options="TS_FLDOPTION_RENDER_HTML />
Enable Rich Text and have the field span the entire row on forms:
<field dbname="DETAILS" name="Details" type="text-memo" options="TS_FLDOPTION_RENDER_HTML, TS_FLDOPTION_SPANS_ROW  />

          </fields>    
     </view>    
</views>    

  

Multiple Columns to Create Unique Key

Some views will require more than one column to uniquely identify a row. This will often happen with outer joins in many-many relationships. If rows from the first table in the join may show up more than once, that table’s identifying column (such as TS_ID) will not be unique to any single row. For instance, consider an outer join between TS_TABLES and TS_REPORTS that shows all tables, and if the table has reports, it shows those reports. If the table has more than one report, the table will be repeated, so the table TS_ID does not uniquely identify a specific row. You cannot use the report’s TS_ID because there may not be a report (this is an outer join). However, a combination of the table’s TS_ID and report’s TS_ID will uniquely identify a row.

In order to resolve this, a new syscode has been added (26) that identifies a field as a row identification field. One identifying column must still be aliased as TS_ID, but you can add the other identifying columns as multiple fields with the syscode 26. This will cause those fields to also be used (along with the TS_ID column) when identifying a row. Any field with syscode 26 must have type="number-integer". All identifying columns must not have null values, so use coalesce(xxx, 2147483647), or something similar, to adjust null values to a unique integer. If the syscode 26 column should not be displayed to the user, as it would by default as it is a field, the attribute hidden="true" can be added to the field in the XML to put the field in the hidden section. Generally, on a view with syscode 26 fields, you can create listing, distribution, and summary reports, you can search the view from advanced search, and you can view items.

IMPORTANT:
The fields combined using syscode 26 must create unique values for every row. If it is not unique, there will be data display problems on any reports using this view.

Due to the requirement of more than one integer value to identify a row, multiple features are not supported with views that have any fields with syscode 26. The following features are not supported when using syscode 26:

  • Including this view in a global search or multi-table report
  • Pointing to this view with a relational or multi-relational field.
  • Adding notes or attachments to items in this view.
  • Adding item links from other tables to items in this view.
  • Creating a join report on this view (if the view has relational fields to other tables). Join reports work but are prevented due to performance concerns. They can be enabled by adding a DWORD registry entry called “ViewWithMoreRowIDsAllowsJoin” with a value of 1.
  • Manage data in this table with Application Administrator

  

XML Variables

Several variables exist, which will be replaced by the system while processing the SQL. Below is a list of the variables and their descriptions:

 

Variable Description
{SELECTION_FIELDDBNAME_1} If a column is an enum, where the integer values cannot be interpreted as data by joining to another table, you can create the field as a selection field and define selections for it. The selections must be listed in the XML under the <field> tag. The selection will be added to the TS_SELECTIONS table as a valid selection, and the corresponding {SELECTION_FIELDDBNAME_VALUE} tag will be replaced in the SQL with the TS_ID of the created selection.
{MANYRELATIONAL(TableID,ItemId)} The SQL to concatenate two integers into a colon-separated text value is different in MSSQL and Oracle. Use this tag and Application Engine will build the proper SQL to concatenate the two integer columns into a colon separated text value that Application Engine can then bind to and read from the database.
{NAMESPACENAME} For each view entry in TS_TABLES created for this view definition from the XML (one per namespace), this will have the TS_NAMESPACES.TS_NAME of that namespace. NOTE: Current code in SDF catalog table stores a "0" for the namespace name if this entry is for namespace 0, so the Application Engine accommodates this by replacing {NAMESPACENAME} with "0" in the SQL for views created for namespace 0. Used when creating views based on Solutions.
{NAMESPACEID}  For each view entry in TS_TABLES created for this view definition from the XML (one per namespace), this will have the TS_NAMESPACES.TS_ID of that namespace. This is used mostly when dealing with Application Engine tables.
 
Field Types

Most of the Application Engine field types can be created in the XML. Some require extra information as noted below.

 

Field Type Description Example

text

Creates a fixed length text field. Required attributes: "size" (integer)-length of the field.

<field dbname="NAME" name="Name" type="text" size="256" syscode="4" />

text-memo

Creates a memo text field.

<field dbname="DESCRIPTION" name="Description" type="text-memo" syscode="5" />

user Creates a user field.  
number-integer

Creates a numeric field with data type integer

<field dbname="RANK" name="Rank" type="number-integer" />

number-float

Creates a numeric field with data type float (allows floating point values)

<field dbname="SCOREVALUE" name="Score Value" type="number-float" />

number-fixed Creates a numeric field with data type float with fixed precision. Required attributes: "precision" (integer)  
date Creates a date-only field  
datetime Creates a date-time field  
project Creates a project field (not system project field, aux tables do not have system project fields)  
selection

Creates a single-selection field. Can have a child <selections> tag with a list of <selection> values after it, which must have attributes "value" (integer) and "name" (text).

Note: Think of this more like a lookup for static value. eg 1 - Analysis.

 
<selections>
 <selection value="1" name="Analysis" />
 <selection value="2" name="Analysis Complete" />
 <selection value="3" name="New Plan Requested" />
 <selection value="4" name="Planning" />
 <selection value="5" name="Planning Complete" />
 <selection value="6" name="Pending Sign Off" />
 <selection value="7" name="Execution" />
 <selection value="8" name="Completed" />
 <selection value="9" name="Backlog" />
 <selection value="10" name="Rejected" />
 <selection value="11" name="On Hold" />
</selections>
relational

Creates a single-relational field. This field will display the data defined in the <valuedisplayformat> tag from the related item. The field will display much like a standard SBM relational field. It will show an icon next to the field when viewing an item. Clicking the icon allows you to see the related record in a pop-up window.

dbname: must point to the TS_ID of the related item
Required attribute: "target" (text)-UUID of related table (not application).

<field dbname="DEMANDPLAN" name="Demand Plan" type="relational" target="3c534aa0-cb13-4b27-b8a3-e3b9c9fc4ca6" />

multi-relational

Creates a multi-relational field. The multi relational field displays the Value Display Format from the related item, just like a relational field. Also, you can click an icon next to the multi-relational field value when viewing an item to see the related item in a pop-up (just like relational fields).

To create the link to the related field, there are two option.

Option 1: Reference the related table using the "target" attribute and specifying the UUID of the related table (not the application). The column given in the "dbname" attribute must be a comma separated list of TS_ID values (including a leading and trailing comma). See the Addition Tips section below.

Option 2: The column data for a multi-relational field must be a text column with format tableID:itemID (for example: 1000:17).  In order to generate SQL that works in both MSSQL and Oracle, a special XML Variable can be inserted into the SQL. See the XML Variables section above.

 

<field dbname="AEITEM" name="Item" type="multi-relational" target="3c534aa0-cb13-4b27-b8a3-e3b9c9fc4ca6" />

multi-user (SBM 11.4 and higher) Creates a multi-user field.  <field dbname="SECONDARYOWNER" name="Secondary Owner" type="multi-user" />
binary/trinary (SBM 11.4 and higher) Creates a binary/trinary field.  Use label1, label2, and label3 in the order they are listed in SBM Composer for the field values.  For example, in the sample Issue Defect Management process app, the Reproducible field defines the first value as "no" and the second value as "yes".  You would specify these as label1="No" label2="Yes". <field dbname="REPRODUCIBLE" name="Reproducible" type="binary" label1="No" label2="Yes" />

  

Field Syscodes

In the field definition section, you can also assign a syscode. These are used to designate the field as a system field. For example, you may want a certain text column to be used as the system TITLE field. This is done by applying the appropriate "syscode" for the system field. The available syscodes are as follows:

 

Syscode System Field Field Type
4 Title text
5 Description text-memo
7 Submit Date datetime

13

Submitter user
16 Issue ID text
26 Row identifying column. Used when the SQL requires more than one column to identify a unique row. Any field with syscode 26 must have type="number-integer".  See Multiple Columns to Create Unique Key for more details. number-integer

 

 

Logging and Troubleshooting


Q: Where is view information logged?

A: The view generation code writes a log file called  Application Engine\log\ViewGenerator.log. When the view is used in a report, the report may log additional information in the Application Event log.

Q: How do I increase the amount of logging that happens as the view is generated?

A: The logging level is controlled by a registry DWORD value in the "HKEY_LOCAL_MACHINE\SOFTWARE\TeamShare\TeamTrack" key named "ViewGeneratorLogLevel". If not present, it defaults to the average logging level of 3, but can be changed to the verbose level 4 for debugging purposes. This enables you to see what is inserted into the database when view generation is invoked.  If you change the logging level, restart IIS to detect the change. 

Q: If I get the following error when running a report on the view "An error occurred while processing the last request", how do I determine the problem?

A: Go to the Windows Event Viewer and check for any errors
 

 

Deleting Views and/or Fields


Q:  What if I delete a field from my XML definition?

A: If a field has been defined in a previous copy of the XML but no longer exists in the current XML, the field is marked as deleted in the database and removed from the TS_FIELDORDERINGS table. The same is true for any selections that are no longer found in the XML.

Q: What if I need to delete a view?

A: If a view is modified or removed from an XML file, it is ignored but not deleted. This is to prevent data loss. If you need to fully remove a view, you can run the following command:

ttadmin.exe /GenerateViewsAllowDelete

This permanently deletes any view not found in the XML. However, it will not delete any notes or attachments related to the view. Instead, this data becomes orphaned data and is no longer accessible through SBM. Even if the view is re-added later, this data will not be re-connected to the restored view.

 

 

Common Problems and Additional Tips

 

TIP: For very complex SQL it might be easier to create a view in SQL/Oracle with all the complex logic and call that view from XML file

The XML file is expecting the SQL to be a SELECT statement. If you need to use a DECLARE or other more advanced SQL commands, try creating a View directly in the database and call that view from the XML SQL..


Problem: Several of the rows in my report have duplicate data.

Answer: This will happen if the TS_ID number is not unique. You can run the same SQL through your database tool to verify. If TS_ID is not unique, you can use a different field for this value, or use the ROW_NUMBER() command described above, or follow the steps in Multiple Columns to Create Unique Key to use the syscode26 feature.

 

Problem: How can get multi-relational fields to display?

Answer: Provide the values comma separated, with starting and ending commas. Blank values should have 2 commas:
e.g. Empty:
,,
e.g. 3 values:
,5,17,22,
Multi-Relational will work for display purposes but will not work with report filters on that field.

 

Problem: There are not any errors in the log, but I don't see my new table.

Answer: Before you can build a report using the new table, you must give yourself permissions to see the table.

Make sure you are logged in as a Regular user and not a Managed Administrator. Then, open the Group > Privileges page. On the Table view, give permissions to the table. If some users login as Managed Administrators, be sure to give them permissions too (User > Administration > Tables)..

Attachment

File NameFile SizeDownLoad
SecondaryOwner.xml 3K HTTP
UsersSkills.xml 1K HTTP
TableAndReports.xml 2K HTTP

Rate this Solution

Find Answers

Type a question or describe what you are looking for below

My Recent Searches

Welcome kb sso

Additional Assistance

  • Submit a Case Online
  • FAQs