x

The Calculation group contains commands to define Define and Compute fields.

DEFINE fields and COMPUTE fields are two different types of temporary fields. A temporary field is a field whose value is not stored in the data source, but can be calculated from the data that is there, or assigned an absolute value. A temporary field takes up no storage space in the data source, and is created only when needed.

When you create a temporary field, you determine its value by writing an expression. You can combine fields, constants, and operators in an expression to produce a single value.

You can specify the expression yourself, or you can use one of the many supplied functions that perform specific calculations or manipulations. In addition, you can use expressions and functions as building blocks for more complex expressions, as well as use one temporary field to evaluate another.

A virtual field (DEFINE) is evaluated as each record that meets the selection criteria is retried from the data source. The result of the expression is treated as though it were a real field stored in the data source.

A calculated value (COMPUTE) is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed. Therefore, the calculation is performed using the aggregated values of the fields.

• Detail (Define). Opens the Detail Field (DEFINE) dialog box, where you can create a defined field, type a name for the field, and enter a format. A Define field is added to the actual data. The Detail (DEFINE) dialog box is shown in the following image.

  

• Summary (Compute). Opens the Summary Field (COMPUTE) dialog box, where you can create a computed field, type a name for the field, and enter a format.

  The Field dialog boxes, which open when you click Detail or Summary, provide similar functionality, including options to display data source fields in a Tree, Dimension, or List view. You can also view a complete set of functions, instead of data source fields, by clicking the Functions button. The Summary (COMPUTE) dialog box is shown in the following image.

  

x

The Join group contains the Join button to open the Join dialog box, where you can create a new join, edit or delete existing joins, and add data sources to a join.

Note: The Join group is not available when working with Reporting Objects or SAP® Business Information Warehouse (SAP BW), Oracle Essbase®, or Microsoft® SQL Server® Analysis Services cubes.

The following image shows the Join dialog box with two data sources joined by the common ID_AGE indexed field.

Using conditional joins, you can establish joins based on conditions other than equality between fields. In addition, the host and cross-referenced join fields do not have to contain matching formats, and the cross-referenced field does not have to be indexed.

Note: You can edit the description of a Join by clicking Edit in the Join dialog box and typing in the Description section. You can only use letters, numbers, and underscores in your description. No special characters are allowed.

The conditional join is supported for FOCUS and all relational data adapters. Because each data source differs in its ability to handle complex conditional criteria, the optimization of the WHERE syntax differs depending on the specific data sources involved in the join and the complexity of the conditional criteria.

For FOCUS certain data sources, if the host and cross-referenced join fields do not have common matching formats, the following message appears.

Note: If you click Yes, the Advanced Filter dialog box opens, where you can create a Where-Based Join.

If the cross-referenced join field does not have an index, the following message appears.

Note: If you click Yes, the Advanced Filter dialog box opens, where you can create a Where-Based Join.

To create a Where-Based Join, create a filtering condition, as shown in the following image.

x

The Filter group contains the Advanced Filter button to open the Advanced Filter dialog box to set advanced filtering options. Advanced filter options include Where, Where Total, the And conjunction, and the Or conjunctions in a single expression.

When creating a report, you refer to fields in several parts of the request. For example, in display commands (PRINT, SUM), in sort phrases (BY, ACROSS), and in selection criteria (WHERE, WHERE TOTAL, IF).

The WHERE phrase selects records from the data source to be included in a report. The data is evaluated according to the selection criteria before it is retrieved from the data source. You can use as many WHERE phrases as necessary to define your selection criteria.

In WHERE TOTAL tests, however, data is selected after all the data has been retrieved and processed.

You can group conditions and expressions within Simple Filter criteria. In addition, you can apply functions and calculations within criteria. This option provides more functionality than the Filter dialog box. For more information on the simple filter, see Field Tab.

You can create Where and Where Total filters in the Advanced Filter dialog box by clicking WHERE, as shown in the following image.

Double-clicking the Double-click or press F2 to edit! text, as shown in the following image, opens drop-down menus for Fields and Subqueries, Operators, and Values, as shown in the second image to follow.

You can retrieve fields and values from the Master File and data source and Subqueries from a HOLD file.

The Field drop-down menu provides a field list from the Master File. The Field list can be viewed as follows:

• In business order (default)

  

• In a sortable grid

  

• In a hierarchical structure of the data

  

• If you select Subquery, then the Operator drop-down menu is replaced by a menu with the options of In list and Not in list, as shown in the following image:

  

  • In list. Activates a drop-down menu in the right-most part of the expression. The drop-down menu contains a list of all subqueries that you have created from the Master file in use, and an EXISTING option.

    Click EXISTING to open the Open dialog box, where you can select a subquery from a different Master file.

  • Not in list. Opens the Open dialog box, where you can select a subquery from a different Master file.

The Operator (default) drop-down menu provides the following operators:

• Equal to
• Not equal to
• Greater than
• Less than
• Greater than or equal to
• Less than or equal to
• In literal list
• Not in literal list
• Missing
• Not missing
• From - To
• Not From - To
• Includes literal list
• Excludes literal list
• Contains characters
• Omits characters
• Like character mask
• Not like character mask

The Value drop-down menu opens a dialog box with multiple options, as shown in the following image.

The Type drop-down menu contains the following options:

• Constant. Enables the entry of a literal constant value.
• Parameter. Enables you to specify a parameter by entering a name and description in the provided text input areas, as well as selecting the type of parameter (Simple, Static, Dynamic).
• Field. Enables the specification of a field name to compare against.

The value area contains a text input box that you can use to manually insert values. It also contains a Get Values drop-down menu, which supplies the following options.

• All. Retrieves all the values from the selected field.
• First. Retrieves the first value from the selected field.
• Last. Retrieves the last value from the selected field.
• Minimum. Retrieves the minimum value from the selected field.
• Maximum. Retrieves the maximum value from the selected field.
• From File. Retrieves a value that you specify. Selecting this option opens a browse dialog box.

Note: The Get Values drop-down menu is only accessible if you have already selected a field.

After selecting the values that you want, you can move them into and out of the Multiple Values area with the left and right arrows. You can also change the value order and delete values with the up and down arrows and the Delete icon.

After creating a condition, you can insert additional conditions before and after the selected condition by using the Insert Before and Insert After buttons at the top of the Advanced Filter dialog box. You can use either the And or the Or conjunction to link conditions and the Group and Ungroup buttons to nest and organize conditions.

You can create additional filters by clicking the New Filter button at the top of the Advanced Filter dialog box.

After creating the filters that you want, click OK to save and apply the filters. You can access them from the Filter pane of the Resources panel.

x

The Display group contains the Missing Data menu, which contains options to control the display of missing data values in a chart.

The Missing Data menu options are:

• Missing Data. Displays the following options Gap, Zero, Interpolated Line.
• Gap. Displays missing values as a gap in bar, line, and area charts.
• Zero. Displays a bar on the zero line in a bar chart. Displays a solid line that connects the missing value with the succeeding value in a line chart. Displays an area on the zero line in an area chart.
• Interpolated Line. Displays missing values as an interpolated dotted line that connects the plot points preceding and succeeding the missing value in a line chart. Displays missing values as an interpolated bar in a bar chart. Displays missing values as an area in an area chart.

x

The Data Source group contains the Add command and the Switch drop-down menu to add and switch data sources. The Data Source group is only available in Document mode.

Note: The Data Source group is not available when working with Reporting Objects.

The commands are:

• Add. Opens the Select a data source dialog box, where you can add additional data sources to a document, enabling you to insert reports from different data sources into the same document. The Select a data source dialog box is shown in the following image.

  

• Switch. Opens a drop-down list of all the data sources that have been added. You can choose which data source is currently active and being used to create new reports.

  You can also switch data sources by selecting a report that is using a data source different from the one that is currently active.