Creating Accordion By Row Reports

Accordion By Row reports are HTML reports that offer an interactive interface to data aggregated at multiple levels by presenting the sort fields within an expandable tree. The highest dimension or sort field (BY value) and the aggregated measures for each value display at the top of the report. The tree control can be used to open or close each dimension and view the associated aggregated values. Clicking the plus sign (+) next to a sort field value opens new rows that display the next lower level sort field values and subtotals. The lowest level sort field, when expanded, displays the aggregated data values.

Using the SET EXPANDBYROW command and turning HTMLCSS ON enables any single verb SUM (WRITE, ADD) or COUNT report that has at least two BY fields to be turned into an Accordion By Row request. EXPANDBYROW automatically invokes the SET SUBTOTALS=ABOVE command, which moves the subtotal rows above the subheading and data rows. A SUB-TOTAL command is automatically added for the next-to-last BY field. Each level will be presented at the aggregated level, and the data values will represent the aggregation of the lowest level BY.

Pop-up field descriptions are supported for the fields within the BY tree and will present the field descriptions maintained within the Master File or defined associated with the fields.

Styling an Accordian By Row report can be done using standard HTML report techniques, but it is important to keep the report structure in mind. All rows except the lowest level are actually SUBTOTAL rows and the lowest level contains the report DATA.

Accordion reports created with the SET EXPANDABLE command open by column, not row.

Syntax: How to Create Accordion Reports That Expand by Row

SET EXPANDBYROW = {OFF|ON|n}

ON TABLE SET EXPANDBYROW {OFF|ON|n}

where:

OFF: Does not create an accordion report. OFF is the default value.
ON: Creates an accordion report which initially displays only the highest sort field level. To see rows on lower levels, click the plus sign (+) next to one of the displayed sort field values.
ALL: Creates an accordion report in which all sort field levels are initially expanded. To roll up a sort field level, click the minus sign (-) next to one of the sort field values on that level.
n: Creates an accordion report in which n sort field levels are initially expanded. To roll up an expanded sort field level, click the minus sign (-) next to one of the sort field values on that level.

Note: Accordion By Row reports require that the HTMLCSS parameter be set to ON and that the request have at least two BY fields.

Top of page

Example: Creating an Accordion Report That Expands by Row

The following request against the GGSALES data source has four sort fields, REGION, ST, CATEGORY, and PRODUCT:

SET EXPANDBYROW=ON
TABLE FILE GGSALES
SUM BUDUNITS UNITS BUDDOLLARS DOLLARS 
BY REGION 
BY ST 
BY CATEGORY 
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF,FONT=ARIAL,$
END

The initial output shows only the top level BY field (REGION):

Clicking the plus sign (+) next to the Midwest region opens the rows that show the states associated with that region:

Clicking the plus sign (+) next to the state IL opens the rows that show the categories associated with that state:

Clicking the plus sign (+) next to the Coffee category shows the products associated with that category. This is the lowest level of the Accordion By Row report: