How to Create a DEFINE-Based JOIN in MRE

By Laura Drezek

While the WebFOCUS end user tools do not lend themselves to creating a DEFINE-based joined structure, it is possible to accomplish this through the Managed Reporting environment.

Let’s get started. There are two ways an end user can create a DEFINE-based JOIN:

	Use the editor through the Custom Reports area. To use this option, you must be a user with the advanced capability.
	Open a new reporting object and use the components and editor.

The second option requires a little more explanation. Upon opening a reporting object, the user is presented with multiple components. The first component is “Other,” which used to be the preferred component for a DEFINE-based join. However, with WebFOCUS 5.2 that is no longer the case. Since a user can put any code in the “Other” component and HTML end user tool does not understand all code, the "Other" component is not used for input to the HTML Report or Graph Assistant. If a DEFINE or JOIN is placed inside this component, since it is not used as input, it will not be available to the HTML Report Assistant or Graph Assistant.

In WebFOCUS 5.2 you need to create the DEFINE and JOIN, then edit the code to include the WITH statement. Once this is done the HTML Report and Graph Assistants will be able to pick up this code. But if you want to do multiple JOINs or DEFINEs using the GUI, you must create them first and then edit the code for the JOIN to use the WITH statement. Once you use the editor to add code, it may not reopen in the GUI tool.

After creating the DEFINE and JOIN components, you can open the HTML Report Assistant or Graph Assistant to create your report. The DEFINE field and JOINs will now be available. I created a dummy DEFINE and JOIN. The bold section in the following example is the code I had to add using the editor.

DEFINE FILE EMPLOYEE
IBICODE/A10= EMP_ID;
END

JOIN
EMPLOYEE.IBICODE WITH EMP_ID
IN EMPLOYEE TO ALL EDUCFILE.EMP_ID
IN EDUCFILE AS J1
END

My DEFINE field of IBICODE is included in my field list, and fields from both the EMPLOYEE and EDUCFILE are available.