Creating Rows From Data

In this section:

How to:

A normal TABLE request sorts rows of a report according to the BY phrase you use. The data retrieved is sorted from either low-to-high or high-to-low, as requested. The rows may be limited by a screening phrase to a specific subset, but:

In contrast, the FML FOR phrase creates a matrix in which you can structure your report row-by-row. This organization gives you greater control over the data that is incorporated into a report, and its presentation. You can:

Syntax: How to Retrieve FOR Field Values From a Data Source

The syntax for specifying rows is: 

FOR fieldname [AS 'coltitle'] value [OR value OR...] [AS 'text'] 
[LABEL label] OVER
.
.
.
[value [OR value ...]] [AS 'text'] [LABEL label]
END

where:

fieldname

Is the FOR field for the FML report.

coltitle

Is the column title for the FOR field on the report output.

value

Is the value (also known as a tag value) describing the data that is retrieved for this row of the report.

AS 'text'

Enables you to assign a name to a tag value, which replaces the tag value in the output. Enclose the text in single quotation marks.

label

Assigns a label to the row for reference in a RECAP expression. The label can be up to 66 characters and cannot have blanks or special characters. Each explicit label you assign must be unique.

Even if you assign an explicit label, the positional label (R1, R2, and so on) is retained internally.

By default, a tag value for a FOR field (like 1010) may be added only once to the FML matrix. However, if you wish to add the same value of a FOR field to the matrix more than once, you can turn on the FORMULTIPLE parameter (the default setting is OFF). For more information, see Use the Same FOR Field Value in Multiple Rows.

For more information about the FMLFOR, FMLLIST, and FMLINFO functions that return the tag values used in an FML request, see the TIBCO WebFOCUS® Using Functions manual.

Example: Creating Rows From Values in a Data Source

Assume you have a simple data source with financial data for each corporate account, as follows: 

CHART OF ACCOUNTS
 
ACCOUNT          DESCRIPTION
 
1010             CASH ON HAND
1020             DEMAND DEPOSITS
1030             TIME DEPOSITS
1100             ACCOUNTS RECEIVABLE
1200             INVENTORY
.                     .
.                     .
.                     .

Using the FOR phrase in FML, you can issue the following TABLE request in which each value of ACCOUNT is represented by a tag (1010, 1020, and so on), and displays as a separate row: 

TABLE FILE LEDGER
SUM AMOUNT
FOR ACCOUNT
1010 OVER
1020 OVER
1030 OVER
1100 OVER
1200
END

The output is shown as follows. 

      AMOUNT
      ------
1010   8,784
1020   4,494
1030   7,961
1100  18,829
1200  27,307

Creating Rows From Multiple Records

How to:

There are different ways to combine multiple values from your data sources into an FML report row. You can use:

By default, a FOR field value can only be included in a single row of an FML matrix. However, by turning on the FORMULTIPLE parameter, you can include the same data value in multiple rows in the FML matrix. For example, the same value can exist as a solitary value in one row, be part of a range in another row, and be used in a calculation in a third row. For more information, see Use the Same FOR Field Value in Multiple Rows.

In addition to these methods, you can extract multiple tags for a row from an external file.

Syntax: How to Sum Values in Rows With the OR Phrase

To sum the values of two or more tags in a single report row, use the OR phrase in the FOR phrase. The syntax is: 

FOR fieldname 
value1 OR value2 [OR valuen...] [AS 'text'] [LABEL label] [OVER]
.
.
.

where:

fieldname

Is a field name in the data source.

value1, value2, valuen

Are the tag values to be retrieved and summed.

AS 'text'

Assigns a title to the combined tag values. Enclose the text in single quotation marks (').

label

Assigns a label to the row for reference in a RECAP expression. The label can be up to 66 characters and cannot have blanks or special characters. Each explicit label you assign must be unique.

Even if you assign an explicit label, the positional label (R1, R2, and so on) is retained internally.

Example: Summing Values in Rows

The following model sums the values of three tags (1010, 1020, 1030) as CASH. 

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT 
1010 OR 1020 OR 1030  AS 'CASH'                   OVER 
1100                  AS 'ACCOUNTS RECEIVABLE'    OVER
1200                  AS 'INVENTORY'
END

The output is shown as follows. 

                     AMOUNT
                     ------
CASH                 21,239
ACCOUNTS RECEIVABLE  18,829
INVENTORY            27,307

Syntax: How to Identify a Range of Values With the TO Phrase

To sum the values of a range of tags in a single report row, use the TO phrase in the FOR phrase. The syntax is: 

FOR fieldname 
value1 TO value2 [AS 'text'] [LABEL label] [OVER]

where:

fieldname

Is a field name in the data source.

value1

Is the tag value at the lower limit of the range.

TO

Is the required phrase.

value2

Is the tag value at the upper limit of the range.

AS 'text'

Assigns a title to the combined tag values. Enclose the text in single quotation marks (').

label

Assigns a label to the row for reference in a RECAP expression. The label can be up to 66 characters and cannot have blanks or special characters. Each explicit label you assign must be unique.

Even if you assign an explicit label, the positional label (R1, R2, and so on) is retained internally.

Example: Identifying a Range of Values

Since CASH accounts in the LEDGER system are identified by the tags 1010, 1020, and 1030, you can specify the range 1010 to 1030: 

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT 
1010 TO 1030 AS 'CASH' 
END

Syntax: How to Use Masking Characters to Retrieve Tag Values

If the tag field has a character (alphanumeric) format, you can perform a masked match. Use the dollar sign character ($) as the mask. For instance, 

A$$D

matches any four-character value beginning with A and ending with D. The two middle places can be any character. This is useful for specifying a whole group of tag values without having to name each one.

Example: Using Masking Characters to Match a Group of Tags

In this example, the amounts associated with all four-character accounts that begin with 10, expressed with a mask as 10$$, are used to produce the CASH row of the report. 

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT 
10$$ AS 'CASH'                 OVER 
1100 AS 'ACCOUNTS RECEIVABLE'  OVER
1200 AS 'INVENTORY'
END

The output is shown as follows.

                     AMOUNT
                     ------
CASH                 21,239
ACCOUNTS RECEIVABLE  18,829
INVENTORY            27,307

Syntax: How to Use the Same FOR Field Value in Multiple Rows

You can use the same value of a FOR field in many separate rows (whether alone, as part of a range, or in a calculation) by including the following syntax before or within an FML request. 

SET FORMULTIPLE={ON|OFF}

or

ON TABLE SET FORMULTIPLE {ON|OFF}

where:

ON

Enables you to reference the same value of a FOR field in more than one row in an FML request.

With FORMULTIPLE set to ON, a value retrieved from the data source is included on every line in the report output for which it matches the tag references.

OFF

Does not enable you to include the same value in multiple rows. OFF is the default value.

With FORMULTIPLE set to OFF, multiple tags referenced in any of these ways (OR, TO, *) are evaluated first for an exact reference or for the end points of a range, then for a mask, and finally within a range. For example, if a value is specified as an exact reference and then as part of a range, the exact reference is displayed. Note that the result is unpredictable if a value fits into more than one row whose tags have the same priority (for example, an exact reference and the end point of a range).

For more information, see Reporting Dynamically From a Hierarchy.

Example: Referencing the Same Value in More Than One Row

This request retrieves the tag values for accounts 1010, 1020, and 1030, and lists corresponding values individually. It then aggregates the same values and displays the sum as TOTAL CASH. Similarly, the tag values for accounts 1100 and 1200 displays as detail items, and then summarized as TOTAL NON-CASH ASSETS. 

SET FORMULTIPLE=ON 
TABLE FILE LEDGER
SUM AMOUNT
FOR ACCOUNT 
1010 AS 'CASH ON HAND'                OVER 
1020 AS 'DEMAND DEPOSITS'             OVER 
1030 AS 'TIME DEPOSITS'               OVER
BAR                                   OVER 
1010 OR 1020 OR 1030 AS 'TOTAL CASH'  OVER 
" "                                   OVER 
1100 AS 'ACCOUNTS RECEIVABLE'         OVER 
1200 AS 'INVENTORY'                   OVER
BAR                                   OVER 
1100 TO 1200 AS 'TOTAL NON-CASH ASSETS'
END

The output is shown as follows. 

                       AMOUNT
                       ------
CASH ON HAND            8,784
DEMAND DEPOSITS         4,494
TIME DEPOSITS           7,961
                       ------
TOTAL CASH             21,239
                             
ACCOUNTS RECEIVABLE    18,829
INVENTORY              27,307
                       ------
TOTAL NON-CASH ASSETS  46,136

Example: Using Tags From External Files

In this example, the values for a row of the FML report come from an external file called CASHSTUF, which contains the following tags. 

1010
1020
1030

The following TABLE request uses the tag values from the external file, summing the amounts in accounts 1010, 1020, and 1030 into the CASH row of the FML report. 

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT
(CASHSTUF)  AS 'CASH'                 OVER
1100        AS 'ACCOUNTS RECEIVABLE'
END

Notice that the file name must be enclosed in parentheses.

Using the BY Phrase in FML Requests

Only one FOR phrase is permitted in a TABLE request. It substitutes in part for a BY phrase, which controls the sort sequence. However, the request can also include up to 32 BY phrases. In general, BY phrases specify the major (outer) sort fields in FML reports, and the FOR phrase specifies the minor (inner) sort field. Note that the BY ROWS OVER phrase is not supported in a request that uses the FOR phrase.

Combining BY and FOR Phrases in an FML Request

In this example, the report results for ACCOUNT (the inner sort field) are sorted by REGION (the outer sort field). 

DEFINE FILE REGION
CUR_YR=E_ACTUAL;
LAST_YR=.831*CUR_YR;
REGION/A4=IF E_ACTUAL NE 0 OR E_BUDGET NE 0 THEN 'EAST' ELSE 'WEST';
END
 
TABLE FILE REGION
HEADING CENTER
"CURRENT ASSETS FOR REGION <REGION"
" "
SUM CUR_YR LAST_YR 
BY REGION NOPRINT 
FOR ACCOUNT 
10$$ AS 'CASH'                      OVER
1100 AS 'ACCOUNTS RECEIVABLE'       OVER
1200 AS 'INVENTORY'                 OVER
BAR                                 OVER
RECAP CUR_ASSET/I5C = R1 + R2 + R3;
END

The output is shown as follows. 

          CURRENT ASSETS FOR REGION EAST           
                                                   
                             CUR_YR         LAST_YR
                             ------         -------
CASH                       9,511.00        7,903.64
ACCOUNTS RECEIVABLE               .               .
INVENTORY                         .               .
                     --------------  --------------
CUR_ASSET                     9,511           7,903

A sort field value can be used in a RECAP command to allow the model to take different actions within each major sort break. For instance, the following calculation computes a non-zero value only for the EAST region. 

RECAP X=IF REGION EQ 'EAST' THEN .25*CASH ELSE 0; 
AS 'AVAILABLE FOR DIVIDENDS'

For more information, see Performing Inter-Row Calculations.