WebFOCUS Online Help > Managed Reporting Developer > Creating Reporting Objects > Creating a Join in the Browser

Creating a Join in the Browser

In this section:

How to:

Reference:

The following table describes the icons used to represent different data types in the Master File fields list in the Join Tool:

Icon

Description

- Letter

Represents an alphanumeric data type.

- Number sign

Represents a numeric data type.

- Clock

Represents a date data type.

After you have at least two data sources in the Join Tool window, you can create the links between the data sources. The data source from which you drag a field becomes the host file and the data source you link to becomes the cross-referenced file. If you are using a FOCUS data source, index field names are highlighted in blue to identify fields that are indexed. The Join Tool supports a maximum of 63 joins.

You can also create inner and outer joins with single or multiple instances.

Note: When joining two FOCUS data sources, the cross-referenced file field must be indexed.

The following image shows the Join Tool window containing two data sources with CATEGORY as the index field.

The Join Tool represents a join by displaying a connector line with a single arrow from the field in the host file to the field in the cross-referenced file. The Join Tool also displays a red button to the left of the linked field in the host file and displays a blue button to the left of the linked field in the cross-referenced file. These buttons provide a quick way to identify host and cross-referenced files.

Note: Click Run to validate the syntax.

Top of page
Procedure: How to Create a Join in the Browser
  1. Expand the Reporting Object in which you want to create links between data sources.

    The Reporting Object window opens.

  2. Select the Join component and click Open.

    The Join Tool window opens and displays the data source associated with the Reporting Object.

  3. In the Join Toolbar, click Add Master.

    The Add Master(s) dialog box opens.

  4. Select a file and click OK.

    WebFOCUS adds the field list for the selected data source to the Join Tool window.

  5. To link the data sources:
    1. Click and hold a field name in one data source. This file becomes the host file.
    2. Drag the field name to the field list of the other data source. This file becomes the cross-referenced file.
    3. Position the cursor over the field name you want to link in the cross-referenced file and release the mouse button.

      The Join Tool displays the connector line between the linked fields and the red and blue buttons next to the linked fields.

      To link fields other than those indicated by the Join Tool, right-click the connector line and select Delete from the Link Properties dialog box. You can then follow the directions in Step 5 to link the fields you want.

  6. Close the Join Tool window to return to the Reporting Object window.

If you have finished working with the components of the Reporting Object, remember to click Done in the Reporting Object window to save the changes.

Note: Syntax and error checking are not performed on the commands you enter until the Reporting Object is executed.

Top of page
Procedure: How to Create a Left Outer Join in the Browser
  1. Open the Join Tool.
  2. Create your join.
  3. In the Join Tool window's toolbar, click the Joins icon.

    The Join Properties window opens.

  4. Select your join properties:
    • Multiple Instance (formerly Non Unique). A one-to-many join structure that matches one value in the host data source to multiple values in the cross-referenced field. For example, joining employee ID in a company employee data source to employee ID in a data source that lists all the training classes offered by that company would result in a listing of all courses taken by each employee, or a joining of the one instance of each ID in the host file to the multiple instances of that ID in the cross-referenced file.
    • Single Instance (formerly Unique). A one-to-one join structure that matches one value in the host data source to one value in the cross-referenced data source. Joining an employee ID in an employee data source to an employee ID in a salary data source is an example of a single instance (unique) join.
    • Unspecified. This option is selected in the Join properties when a join that was created in a release earlier than Version 7 Release 1 is opened in the Join Tool. In versions prior to Version 7 Release 1, the defaults for join type and other operators were assumed and not specified in the generated code. Since the Join Tool cannot determine the type of join that was created and if there were other commands set to control the join, select the appropriate options (Inner Join, Left Outer Join, Multiple Instances, Single Instances) from the Join Tool to upgrade the generated code.
    • Inner Join. A join that results when a report omits host rows that lack corresponding cross-referenced rows.
    • Left Outer Join. Extends the results of an Inner Join and retrieves records from both host and cross-referenced tables, including all records from the left table (host) and any records from the right table (cross-referenced) where the condition values match. If there are no matching values in the cross-referenced table, the join still retrieves records from the host table.
  5. Click OK.
Top of page
Reference: Usage Notes for Creating Joins
Top of page
Automatically Repositioning Files in the Join Tool

There is an option in the Managed Reporting Join Tool Applet that enables you to automatically arrange the files that currently display in the tool. This is useful when you add and remove files and there is extra space.

To reorder files, in the Applet Join Tool toolbar, click the Auto Arrange Master Files icon.

WebFOCUS