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

Creating and Customizing 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 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 toolbar, click the Auto Arrange Master Files icon.

Top of page
Customizing a Join in the Browser

You use the Join Properties dialog box to view the actual WebFOCUS code that defines a join, create single instance (unique) joins, rename the join, or delete the join.

The Join tool assigns a unique default name to each join you create in a Reporting Object. The default names are J1 for the first join, J2 for the second, J3 for the third, and so on. You use these names to access a Join Properties dialog box.

The following image shows the Join Properties dialog box containing the name of the created join and the WebFOCUS syntax for the join.

Top of page
Procedure: How to Display Join Syntax in the Browser
  1. In the Join tool window toolbar, click the Joins icon.

    The Join Properties dialog box opens.

  2. Select a join from the Created Joins list.

    The WebFOCUS code that defines the join appears in the Focexec box. You cannot edit this code; you can only view it.

Top of page
Procedure: How to Create a Single Instance (Unique) Join in the Browser
  1. In the Join tool window toolbar, click the Joins icon.

    The Join Properties dialog box opens.

  2. Select the Single Instance option button.
  3. Click OK.
Top of page
Procedure: How to Rename a Join in the Browser
  1. In the Join tool window toolbar, click the Joins icon.

    The Join Properties dialog box opens.

  2. Select a join from the Created Joins list.
  3. In the adjacent text box, type a name for the join.
  4. Click OK in the Join Properties dialog box.

    Note: Alternatively, you can change the join name from the Link Properties dialog box. You can access this dialog box by right-clicking the arrow in the join connector line in the Join tool window.

Top of page
Procedure: How to Delete a Join in the Browser
  1. Open the Join tool.
  2. In the Join tool window, right-click the arrow in the join's connector line.

    The Link Properties dialog box opens.

  3. From the Link Properties dialog box, click the Delete Link button.

    The Join tool deletes the link and you return to the Join tool window. Notice that the Join tool removed the join connector line and the red and blue buttons associated with the join.

WebFOCUS