Column Magic With ACROSS


By  
Walter Blood
Director of FOCUS Product Management & Support

Transform column data display with ACROSS.

One of the most powerful phrases in the FOCUS language—ACROSS—allows you to generate a matrix of data in the simplest and most direct way, comparing any two or more dimensions. For instance:

And when you use this construction, there are a number of aspects of the ACROSS column processing and display that you might wish you could change. Well, most likely, you can!

Getting the ACROSS Columns in the Order That You Want

For instance, you are aggregating data ACROSS a date field

TABLE FILE  WF_RETAIL_LITE
SUM 
REVENUE_US
BY LOWEST STATE_PROV_NAME
ACROSS LOWEST TIME_DAYNAME AS 'Sales Activity – Day of Week'
ON STATE_PROV_NAME PAGE-BREAK
WHERE TOTAL QUANTITY_SOLD GT 100
WHERE TIME_YEAR NE 2017
...
END

and the sequence is not what you want. How could this happen?

If you are aggregating ACROSS the days of the week, the sort will be alphabetical. Is this what you want?

There is an easy way to adjust this sort sequence, using the ACROSS syntax itself.

ACROSS field COLUMNS value1  AS 'header1' AND value2 AS  'header2' … AND valuen  AS 'headern'

A simple change to the request using this syntax, indicating each value to include and the order in which to display them, quickly resolves the sequencing problem.

This procedure allows you to control four important aspects of the ACROSS:

  1. Select the values from the field that you want to use in the output, and only the values that you want to use.

    A column appears for each value specified, whether there is data or not. Using this technique, you can make sure the column value is represented, even if data is not present. Normally, if there is no instance of a specific sort value, it is not shown. If you are looking at days of the week, or weeks or months of the year, your report would not indicate where the missing instances are. And often the lack of data is more significant than its presence—and this control will make sure you can see that case.

  2. Exclude all other values associated with that field without a WHERE or IF statement.

    The selection of the specified values is implicit, no filter is required.

  3. Rename the column header for those values with AS.

    For cryptic data values, you have an easy way to translate the value to an understandable column header.

  4. Specify the order in which you want to display those values.

    This gives you the ability to change the sort sequence to whatever is most meaningful for the output you are generating. (Example: Sales by product across day of the week.)

And that's just for starters—-let's add some additional possibilities.

  1. ACROSS works with database fields and also with DEFINE fields--both in the synonym and in the request itself. When we are working with a data field, we are working with a single value. Do you want to work with a range of values, or a specific grouping of values that is unique to your company? Create the grouping in a DEFINE, and then use that DEFINE in the ACROSS with the above controls.
  2. This same functionality works with BY. The syntax is BY field ROWS value OVER value, which you may recognize from FML, EMR, SML, or FRL, all Financial Reporting Languages. And you can use one BY ROWS and one ACROSS COLUMNS in a given request.

Making Sure Columns Don't Display

You have a report that will be sent to each of the 50 state store managers, detailing total sales by product across their specific state and stores. Not every store sells in every state, so if you break the report by store, there will undoubtedly be lots of empty columns on the page, forcing the user to scroll back and forth to review the data--not the best solution. You could run the report 50 times with a filter on the state to limit the output. But that is not efficient.

Or you can use the SET command to handle this situation:

SET HIDENULLACRS = ON/OFF

The default behavior is OFF, so our basic report will look like the previous report. Notice that we used a PAGE-BREAK on each STATE_PROV_NAME. This is significant, as you will see. Notice also that the third line has columns with no data displayed.

Simply by changing the value of HIDENULLACRS to ON, you will generate the following output:

There are a few key points to remember with this switch.

  1. The scope of this setting is the PAGE. Therefore you want to make sure that you use PAGE-BREAK on the sort key where your break will occur. If the column has no data to display on that PAGE, the column will not appear in the output.
  2. Now that you are aware of this switch and what it does, you can make use of it. This is true of all of the switches (SET commands) that are available to you. So get to know them.

Creating Detailed ACROSS Lists

There are times when you simply want a listing of the data values in ACROSS columns in the same report. But when you use PRINT or LIST to access the detail, a row is created for every record. With ACROSS, it is unlikely that each record will have data in more than one column.

Look at the details for the report when you use PRINT.

TABLE FILE  WF_RETAIL_LITE
PRINT 
REVENUE_US
BY LOWEST STATE_PROV_NAME
ACROSS LOWEST TIME_DAYNAME AS 'Sales Activity – Day of Week'
ON STATE_PROV_NAME PAGE-BREAK
WHERE STATE_PROV_NAME EQ 'Alabama'
WHERE TIME_YEAR NE 2017
...
END

The following report is produced. This is only the first of many pages.

This is limited to show only one state and the effect is apparent. With a lot of data, this is hardly an optimal display.

There is another SET switch you can use to alleviate this problem.

SET ACROSSPRT =  NORMAL/COMPRESS

If you use the COMPRESS value here, all of the data values in the column will be compressed up to the top of the column, greatly reducing the number of rows that are required to display the data.

This certainly makes it easier to use this list of values.

Be aware that this removes any record-level connection between the data values.

We have just scratched the surface of the development possibilities with ACROSS. If you haven't taken a look at the documentation for the reporting language, see the resources on the next page.

Additional Resources

Recent Posts