The Added Power of Multi-Verb Requests

The FOCUS language supports a remarkable range of functionality, all centered around a single aggregating or detail-generating pass through the data. It includes sorting, selection, virtual field creation (both at retrieval time and prior to output time), text insertion, and subtotaling and grand totaling within the sort. Verb object prefixes allow you to modify the data. Functions can be applied both in expressions and directly to verb objects. There is a range of styling that opens the door for the artist in every one of us. And often these features are really all that are required to generate the output that you need.

But there is more here than meets the eye. The FOCUS language also supports this same amazing range of functionality when you need to make multiple passes through the data to:

• Provide aggregation at multiple levels of your sort.
• Create your own hierarchy.
• Display both aggregation values and details in the same output, allowing for common styling.
• Generate subquery-like data for use in the output generation.
• Generate hierarchical output when creating a data file.

The use of multiple verbs—like multiple clauses in a compound complex sentence—allows us to easily generate complex outputs.

The Basic Rules

You can use up to 64 verbs in a single request.

• You can only have one detail verb, PRINT or LIST, and it must be the last verb in the request.
• Sort fields must be nested within higher level sorts. Fields aggregated at the same sort level will be displayed together.
• Selection criteria are applied to the lowest sort level containing the fields selected.

Creating Multi-Verb Requests in App Studio

Let’s start by creating a report from the ggsales file, which you can set up using the Tutorial in the Web Console. Select the following fields in this order from the Object Inspector:

REGION
ST
CITY
DOLLARS
DOLLARS
DOLLARS

Notice how the DOLLARS fields are all aggregated and the REGION, ST, and CITY fields are set up as sort fields.

There is a range of styling that opens the door for the artist in every one of us. And often these features are really all that are required to generate the output that you need.

We then change to the Sort Groups view in the Object Inspector.

Click the DOLLARS in the fourth row and drag it to REGION. This creates Sort Group 1.

Finally, click the first DOLLARS in Sort Group 2 and drag it to ST, producing three sort groups.

Running this request generates the following output, showing the aggregated value at the three different levels.

The hierarchy showing the total dollar sales by region, by state, and by city, is displayed in three distinct dollar sales columns. To bring these values into a single column displaying the hierarchy vertically, you can SET DUPLICATECOL = OFF using the SET tool.

You can even go as far as converting the lowest level sort in your hierarchy to an ACROSS field, with the following results:

The most obvious effect of using multiple verbs is the generation of hierarchies.

Underneath it all, the FOCUS code that generates this request is more explicit about the structure of the hierarchy. Notice that the entire hierarchy is repeated with each verb.

Creating Your Own Hierarchy

The most obvious effect of using multiple verbs is the generation of hierarchies. This is the result of the requirement for nested sorts. How can you make use of this behavior? For the natural hierarchies in your data—like date and time, and geographical location—the use of multi verbs for drill-downs is obvious. But what about the less obvious, or more improbable possibilities? We use hierarchies for analysis because they provide a known, possibly dimensional, structure that makes comparison easy. Consider the time dimension, the lowest level being days, aggregated to weeks or months, aggregated to quarters, and finally aggregated to years. We can look for trends and drill into any level where the data seems unexpected or out-of-place to examine the details, and ultimately look for hints as to the cause.

Are there hierarchies that apply to your data that are not represented by time or location? For example, order type, delivery method, staff requirements, or customer education and experience? What you build your hierarchy with is entirely under your control.

I may be convinced that the product sales on a given day of the week are related to the cost of the product, where the consumer resides, and how it is being sold—web versus store.

Further, I might want to see if the patterns in these variations are the same, or if there are variations that can be detected and explained by the dimensions that we are working with.

Including these fields as sorts, and aggregating revenue in the wf_retail_lite database, gives us a report that shows the aggregated values at only the lowest level.

Turning this into a multi-verb report increases the information that is made available to the user, providing totals for comparison at each level.

Using Multiple Verbs to Include Both Summary and Detail in the Same Report

Perhaps one of the simplest and most effective uses of multi-verb requests is to create reports that contain both summary data and detail information to support that summary. The basic mechanism is to create a multi-verb request and ensure that the verb at the lowest sort level provides the detail that you need. You may need to change the verb at the lowest level to either PRINT or LIST.

In this case, the SUM verb gives us the level of detail at the product category level that we want to see in the breakdown of the store totals.

With a simple change of the lowest level sort to ACROSS, this can also be displayed in the following way. The initial Revenue column is broken down into the product category components across the row.

Should you need to drill down to or display the lowest level of the hierarchy, you may need to use the PRINT or LIST verb. This will display a row for every record that is read and selected, which could be somewhat large. When working at this level, use WHERE to limit the amount of data that is read.

Using Multi-Verbs to Create Selection Criteria

In SQL, subqueries are often used to bring in data that will be used in the selection process for the main request. In the FOCUS language, this can be done in several ways: creating a request to gather the selection criteria values and placing them in a HOLD or SAVE file, followed by the main request that uses the WHERE IN file syntax to use that data; or JOINing to the file that contains the selection criteria values prior to creating the request. If the selection criteria that you need to use is a function of the data that you are already reading, then multi-verb requests can also provide you with a solution.

For instance, if your selection is based upon including data only for the highest selling product, then you can collect that data in the highest level of a multi-verb request, and use it for selection—WHERE TOTAL selection—to limit the request to including only data for that product, as in the following:

Notice that the selection value in the WHERE TOTAL phrase is created in the highest level sort of the multi-verb request. That value can be displayed or made invisible with NOPRINT, as shown in the example above.

Likewise, you can also use other prefix operators, like AVE., MIN., FST., and LST., to create additional conditions. However, you are not restricted to only verb objects here. COMPUTEs can be used at this level to generate more complicated values.

We will explore multi-verb functionality further in a future topic, for next time however, we will examine MATCH and the new behavior that makes this command so powerful.