Adding Tables and Joins on a Report Template
Table and joins can be added to a query in multiple ways:
• Tables can be added individually. See section Add an Individual Table.
• Joins can be drawn between two tables. See section Draw Join Between Tables.
• From one table, a new table and the join between the tables can be added concurrently. See section Add Table and Join Concurrently.
• Populate an empty query with a join model. See section Populate with a Join Model.
Once added, tables can be moved in the display using drag-and-drop for ease of viewing. The new position of the table is saved along with the report template.
Add an Individual Table
Individual tables can be added by clicking
Add
. The
Select Types to Add window opens. From the
Select Types to Add window, you can view the available types or report templates:
• Show Types—Select this radio button to view the list of available types and subtypes. You can further filter the list by selecting the Show Types in Use checkbox to narrow down the list of types to only those that have data in the database. Select the Show Internal Type Names checkbox to cause the internal type name, or class name, of the type to display in brackets after the type display name for all types. (If multiple types have the same display name, the internal type names for those types are always displayed in brackets after the display names.)
• Show Report Templates–Select this radio button to view the list of available report templates.
Enter text in the Filter list field to find specific types or report templates. If Show Internal Type Names is selected, then the internal type names are also searched.
Hover over an information icon
next to a type or report template to view a description of that item. Click a help icon
next to a type to view additional documentation about that type, such as Javadoc or knowledge-base articles.
| The content displayed for the descriptions or additional documentation is taken from localized source documentation which matches the user’s browser locale setting, as well as from non-localized source documentation, such as the PTC-provided Javadoc. The com.ptc.windchill.enterprise.queryBuilder.documentationDefaultLocale property can be used to suppress or control this behavior by specifying a locale value which the system automatically applies to all non-localized source documentation. The non-localized source documentation is then treated as though it is localized for the locale specified in the property. • If the user’s browser locale matches the locale specified for the property, then content from the source documentation explicitly localized for that locale displays, as well as content from any non-localized source documentation. • If the user’s browser locale does not match the locale specified for the property, then only content from the source documentation explicitly localized for that locale displays. • If the property is not set, then content from any non-localized source documentation displays, regardless of the user’s browser locale setting. For example, if the locale specified for the property is fr and the user’s browser locale is set to French, then content from all non-localized source documentation displays, as well as any source documentation that is localized for French output. If the user’s browser locale is changed to German, then only content from source documentation that is localized for German output displays. For more information, see properties.html. |
Select the desired type or report template, and click OK.
Once a table has been added, you can change the alias displayed on the table by selecting Modify Alias from the right-click actions menu for the table, and entering a new alias.
Draw a Join Between Tables
Each table has a connector area in the bottom right corner. Create a join between two tables by clicking and dragging a line between the two connector areas. The Create Join window opens, displaying the available joins between the two tables.
On the Create Join window, click the Show Joins in Use checkbox to filter the list to only those joins that are actively in use. Enter text in the Filter list field to further filter the list. If the join should be an outer join, select from the Outer Join Against drop-down list which table the outer join should be against. This means that rows or instances from the selected table should be included even when the join conditions between the two tables are not satisfied. Essentially, selecting a value for Outer Join Against means “allow null entries for the unselected table when joining against the selected table”. If a join is an outer join, the link arrow is green instead of blue.
Some joins are link joins, which are an explicitly modeled association between two types. Each of the types plays either the A or B role in the association as specified in the data model. Some link joins can go in either direction, that is, either type can be the A or B role. When these joins are selected in the Create Join or Modify Join windows, the Role A and Role B designation displays at the bottom of the window:
Click the arrow between the roles to swap which type is Role A and Role B:
Select the desired join, and click OK.
Once a join has been added, you can select Modify Join from the right-click actions menu for the join. The join can be modified by choosing a different join, or changing the Outer Join Against selection for the join.
| The display of Show Joins in Use checkbox on Add Table and Join pop-up window is based on com.ptc.windchill.enterprise.queryBuilder.showjoinsinuse property setting. If the value of the property is not defined or set to true, then Show Joins in Use checkbox is displayed on Add Table and Join pop-up Window. If the value of the property is set to false, then checkbox is not displayed. For large set data report, it is recommended to set the value of the property to false. This property needs to be updated in wt.properties file. |
Add a Table and Join Concurrently
Right-click an existing table, and select Add Table and Join to add a new table and the join between the two tables in a single action. The Add Table and Join window opens. Since the table on one side of the join relationship being added is already known, only those tables that can be joined to the current table, and their applicable joins, are presented. Selecting a table automatically selects the appropriate join between the selected table and the table from which the Add Table and Join action was launched. Similarly, selecting a join automatically selects the appropriate table.
If the join should be an outer join, select from the Outer Join Against drop-down list whether the outer join is against the table being added, or if the outer join is against the table from which the Add Table and Join action was launched.
If the join is a link join with interchangeable A and B roles, ensure that the roles are set appropriately. Click the arrow between the roles to swap which type is Role A and Role B:
Select the desired table and join, and click OK.
Once added, the table alias and join can be modified as previously described.
Populate with a Join Model
If no tables are present, you can click Populate with a Join Model to select from the available join models. On the Select a Join Model window, all available join models are displayed, along with their descriptions. Select the desired join model and click OK.
The advantage to using the
Populate with a Join Model action is that the table and join relationships have already been defined. You do not need to individually add each table and join yourself when defining a new query. When using a join model, you can add the desired report items for your query from the
Select or Constrain tab. When your query has been completely defined, you can have the
Query Builderautomatically remove any tables and joins that are not used.
For more information, see
Join Models.
