Create a Join data source

How to create a Join data source

A Join data source represents a table formed by joining two other tabular data sources together.

You can join together any combination of:

• AQS data sources

• AQS statistics data sources

• other Join data sources

It's therefore possible to join successive pairs to create complex data sources with one to many links.

See diagram

Note

Join data sources are the only type that won't be shown to the user when they run the report.

How does it work?

If you're familiar with SQL, a Join data source is the equivalent of performing an inner or outer join.

To create the first Join data source in a report, select two AQS data sources that both have a column containing the same data, (Defects and Defect ID in the example below).

For each row in the first data source, the value in its nominated column is compared to the values in the second data source's nominated column. For every match, a combined row is created in the Join data source.

Depending on the join type, other rows from either data source may also be included.

Create a Join data source

To create a Join data source in the Report Builder:

1. Open the Data sources tab and select Create data source.

2. Supply a meaningful Name. Select Type and choose Join.

3. Select Join type and choose one from the list:

   Inner join (default)

   Includes matching rows only. Each row in the first data source's nominated column is compared to each row in the second data source's nominated column.

   

   If a value repeats in either data source, each occurrence will be included in a separate combination row.

   
   Left outer join

   Includes matching rows, plus all rows from the first data source. As these extra rows have no matches in the second data source, they're combined with null (blank) values.

   
   Right outer join

   Includes matching rows, plus all rows from the second data source. As these extra rows have no matches in the first data source, they're combined with null (blank) values.

   
   Full outer join

   Includes all rows from both data sources! As with the left/right outer joins, non-matching rows will be combined with null (blank) values.

   

4. Select Join from, select beside the first data source you wish to join, and then select the header you wish to join on.

5. Select Join to, select beside the second data source you wish to join, and then select the header you wish to join on (which should match the first).

6. Select Create to finish.

Customise sorting and headers

If you edit the data source after it's been created, there will be two more optional fields to configure:

• Sorting - specify how the data should be sorted. See Data source sorting.

• Headers - retitle selected headers for easier identification. See Data source headers.