Sources

Sources appear in the bottom left part of the Add/Edit Report Template dialog under the SOURCES node. Sources are the links between the database data and the visual elements.

Most of the visual elements in the report require a source element to retrieve data from. To ensure that a visual element can retrieve data from a source element, the source element must be added to the Report Layout structure and it must be in the same element group.

You can edit the parameters of sources either by double-clicking them in the Report Layout structure or by selecting them and choosing Edit from the toolbar.

Note

A source without a parent source or table will be displayed with a question mark indicator over its icon. This is an easy way to identify sources that have not been set up or are no longer in use.

Distinct Source

Sometimes, you want only a subset of a table’s rows, showing each value, or a combination of values, just once. In this case, you create a distinct source that references a simple source.

For example, you want a table with a list of asset types that are actually in use, but this might be different from the full list from the Asset Location table. In this case, you proceed as follows:

1. Create a Simple Source and set its table to Asset Location.

2. Create a Distinct Source and make the following settings:

   • Set its Source to your simple source.

   • Select the Asset Type column.

   

3. Create a Table of Data and set its source to your distinct source.

Result

The Distinct Source returns one row for each value of asset type found in the Asset Location table.

Tip

Another way of grouping rows is using a pivot table.

Parameter Source

If you want to use parameters specified by the user in headings, text, and so on, add a Parameter Source. You don’t need one of these to use parameters in simple source filters.

Simple Source

Simple sources are the most frequently used sources in report templates. Each single source must be linked to a table, which you can do on the Properties tab of the Edit Simple Source dialog. The table you choose will depend on the data you want to show, for example, if you want to display GVI event data, you choose your GVI event table. If you want to display data about events overall, you would choose the Event table.

Optionally, you can add additional fields to the source or set up filters as required (see below for more information).

Manage Fields

In addition to the fields that are included in the selected source table, you can add additional fields to the source, which can be a field from another table, or a new field that contains the result of a function. You can do that on the Fields tab of the Edit Simple Source dialog. The Fields tab shows all the fields in the selected source table (visible when the Show Local Columns button is selected), plus, any extra fields that you have added.

To add a new field, proceed as follows:

1. On the Fields tab of the Edit Simple Source dialog, choose Add.

2. In the toolbar, choose Input and select one of the following options:

   • Field…, if you want to add a new field from another table. This adds a new field with the type Simple Column (Existing Field).

   • Function…, if you want to add a new field that contains the result of a function. This adds a new field with the type Function (Result from a Function).

3. From the dialog that pops up, select the field or function as required.

4. Click OK to save your changes.

You can delete only fields that you have added, you cannot delete local fields from the selected source table.

Set Up Filters

You can set up filters for the simple source on the Filters tab of the Edit Simple Source dialog. This allows you to control which rows are returned from the table.

To set up a filter for a simple source, on the Filter tab of the Edit Simple Source dialog, proceed as follows:

1. In the Fields column, select the field based on which you want to set up your filter:

   • If you want to use a field from the local source table, select the field from the drop-down list.

   • If you want to use a field from another table, choose to pick the field you want from any table.

2. In the Comparison and Not columns, select the operation that you want to perform for filtering.

   Operator	Description
   =	Use this operator if you want the value of the field to be exactly the same as the value you select in the Value column. If you want to test whether a key is null, just use ‘=’ as the comparison, and leave the value blank.
   <, >, <= and >=	These operators are only meaningful for numeric and whole number types.
   Is Child Of	This is a special operator that is only available if you selected the VN_ID field of the Asset Location table. VN_ID is the unique identifier of an asset in the hierarchy. If you select this field with the Is Child Of operator, and as a Value, you select an asset in the hierarchy, then the simple source returns the asset together with all its children in that hierarchy.
   in	This operator is similar to = (equal), however, in this case, you can multiselect items in the Value column, that is, you can pick several items that the field can be equal to.
   like	This is basically a “contains” operator and is relevant only for alphanumeric fields. If you use this operator, then in the Value column, you can use ‘%’ (percent) to match several unknown characters, and ‘_’ (underscore) to match a single unknown character. For example, if you specify _VI, it would match ‘GVI’ and ‘CVI’; if you enter PL %, it would match ‘PL Anode’, ‘PL Damage’, and so on.

   If you select the checkbox in the Not column in conjunction with the selected operator, the opposite function of the operator will be used for filtering. For example, if you select the checkbox in the Not column and you select the = operator in the Comparison column, the system will filter for the values that are NOT equal to the selected value.

3. In the Parameters column, specify how you want the values for the filter to be selected. You have the following options:

   Parameter	Description
   None	This option allows you to immediately select the value from the Value column of this dialog.
   User	If you select this option, the user will be able to select the value from a dialog upon executing the report. The field that you selected will be a selection field and the user must select a value for that before the actual report output is generated. if you choose ‘=’ for your Comparison, the user will be able to choose one item; if you choose ‘in’, the user will be able to choose several items.
   Group	This option has meaning only if this Simple Source is inside an Element Group. If you choose this option, then this filter will pick its value from the Simple Source used for the Element Group.

4. In the Value column, assign a value to your filter as required. If this is a string or a numeric or a whole number field, you can simply type a value in. If your filter field is a key to another table, you can use the to pick a value. For example, in the Event table, the fields Asset, Event Type, Survey Set and Workpack are keys to other tables. If your source table is Event, and your filter is on the Workpack field, clicking the button will bring up a list of workpacks to pick from.

5. In the Actions column, you can add additional filters or remove filters as required by choosing the + or - button respectively.

6. If required, you can connect your filters by grouping them and using the All or Any connectors. If you have multiple filters, you’ll see a drop-down list at the left and you can choose one of the following options:

   • If you select All, then all the filter criteria must be met for data to be returned.

   • If you select Any, then if any criterion is met, data will be returned. (All and Any are equivalent to SQL’s AND and OR.)

   You can construct more complex combinations by grouping rows. To do that, use the Shift and/or Ctrl keys on the keyboard, click filters to multi-select and click Group. The rows you selected will be joined together into a group with their own Any/All filter, and that group will be connected to other groups with a separate Any/All connector. By choosing your grouping and choosing Any/All as appropriate, you can construct any logical expression you desire. You can ungroup rows by selecting a row within a group and clicking Ungroup.

Example

You want the simple source to return assets within the Asset Location table, which fulfil the following conditions:

• In the asset hierarchy, they are under the Offshore node AND

  • They either have the characters PL in their asset name OR

  • They have the asset type Pipeline, Pipeline Section, Platform or Topside Structure

In this case, you create the filters as shown below:

That is, you create the filters as follows:

1. You create a filter for the field VN_ID within the Asset Location table, with Comparison Is Child Of and Value Offshore.

2. You add a filter for the field Name within the Asset Location table, with Comparison like and Value %PL%.

3. You add a filter for the field Asset Type within the Asset Location table, with Comparison in and Values Pipeline, Pipeline Section, Platform or Topside Structure.

4. You group the filters for VN_ID and Asset Type (select them and choose Group) and set their connectors to Any. You leave their connection to the first filter with the connector All.

Special Fields

There are special fields that can be useful for reports run using the Reports toolbar button from specific screens in NEXUS IC:

Screen	Special Field
ASSETS	VN_ID
LIBRARY	Library_ID
PLANNING	VN_ID
WORKPACKS	Workpack_ID
INSPECTION	Header_ID or Finding_ID (depending on what is visible)
ANOMALY	Anomaly_ID

If you select one of these special fields when you set up the filter for your source and you select the User parameter option, then when a user runs your report template from the appropriate screen, the currently selected item will be pre-populated into the parameter in the report generation dialog.

For example, if you set up a report template with the Anomaly_ID field, then when the user runs your template from the ANOMALY screen, the currently selected anomaly will be automatically selected in the report generation dialog.

Union Source

A Union Source lets you join together results from several other Sources, much like SQL’s UNION ALL. In the Union Source, select the sources you’d like to pull data from. Only columns that are common to all selected sources will be passed through to any element that consumes the Union Source. So if you chose two tables, both of which had a Name column, your Union Source would have a Name column.

Note

When configuring a Union Source, ensure that every source being unioned is named differently, otherwise when saving and loading a report template, the sources within the Union Source will not all be retained.

See also

• Configure Report Templates

• Elements

• Configure Reports and Dashboards

• Example: Set Up Anomaly Summary Report Template

• Example: Set Up Asset Integrity Report Template

• Example: Set Up Workpack Report Template