Controlling the Display of Sorted Data With Accordion Reports

In this section:

Accordion Reports provide a way to control the amount of sorted data that appears on an HTML report page. You can produce reports with expandable views for each vertical sort field in a request with multiple BY fields.

You can create two types of Accordion Reports:

Two vertical sort fields coded with BY phrases are required when using Accordion Reports. If the command syntax does not contain at least two BY phrases, the Accordion Reports EXPANDABLE or EXPANDBYROW command is ignored, no message is generated, and a standard HTML report is created.

Note: Accordion Reports are only supported for HTML report output.


Top of page

x
Requirements for Accordion Reports

Reference:

The following requirements must be taken into consideration when creating Accordion Reports:



x
Reference: Distributing Accordion Reports With ReportCaster

Distributing Accordion Reports with ReportCaster requires the use of JavaScript components and images located on the WebFOCUS Client. To access the JavaScript components and images from a report distributed by ReportCaster, the scheduled procedure must contain the SET FOCHTMLURL command, which must be set to an absolute URL instead of the default value. For example,

SET FOCHTMLURL = http://hostname[:port]/ibi_apps/ibi_html

where:

hostname[:port]
Is the host name and optional port number (specified only if you are not using the default port number) where the WebFOCUS Web application is deployed.
ibi_apps/ibi_html
ibi_apps is the site-customized web server alias pointing to the WEBFOCUS80/ibi_apps directory (where ibi_apps is the default value). ibi_html is a directory within the path to the JavaScript files that are required to be accessible when viewing an Accordion report

For more information about coding reports for use with ReportCaster, see the Tips and Techniques for Coding a ReportCaster Report appendix in the ReportCaster manual.


Top of page

x
Creating an Accordion By Row Report

How to:

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. By default, the report will present the highest dimension or sort field (BY value) and the aggregated measures associated with each value. 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 HTML report 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.

When an Accordion By Row report uses the PRINT command, the innermost level of the resulting tree contains detail records from the data source. There can be many detail records for each combination of BY fields, so it may be unclear what distinguishes the various detail records within the display. In order to make the report more useful, include at least one field in the report that can be used to distinguish between the detail level rows.

When an Accordion By Row report uses the SUM command, each row, even at the innermost level of the tree, is actually a subtotal row and is completely described by the combination of BY fields in the request. Each level will be presented at the aggregated level, and the data values will represent the aggregation of the lowest level BY.

Styling an Accordion 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 By Row reports display the grand total row as an anchor row below the data. This anchor row displays above both the report and page footings aligned to the left margin of the report. To generate Accordion By Row reports without the grand total anchor row, add ON TABLE NOTOTAL to the request.

Accordion reports can also be created to be opened by column, instead of by row. See Creating an Accordion By Column Report for information on how to create Accordion reports using the SET EXPANDABLE command.



x
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.
  • By default, a blank line is generated before a subtotal on the report output. You can eliminate these automatic blank lines by issuing the SET DROPBLNKLINE=ON command.



Example: Creating an Accordion By Row SUM Report

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

TABLE FILE GGSALES
SUM DOLLARS/D8MC 
UNITS/D8C
BUDDOLLARS/D8MC BUDUNITS/D8C
BY REGION
BY ST 
BY CATEGORY 
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET EXPANDBYROW ON
ON TABLE SET DROPBLNKLINE ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT,
    COLOR=RGB(66 70 73),
    FONT='TREBUCHET MS',
    SIZE=9, 
    SQUEEZE=ON,
    GRID=OFF,
$
TYPE=REPORT,
    GRID=OFF,
    FONT='TREBUCHET MS',
    COLOR=RGB(52 85 64),   
$
TYPE=TITLE,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
    STYLE=-UNDERLINE,
$
TYPE=HEADING,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
$
TYPE=FOOTING,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
$
TYPE=SUBTOTAL,
    BACKCOLOR=RGB(72 118 91),
$
TYPE=SUBTOTAL,
    BY=1,
    COLOR='WHITE',
$
TYPE=SUBTOTAL,
    BY=2,
    COLOR='WHITE',
    BACKCOLOR=RGB(132 159 126),
$
TYPE=SUBTOTAL,
    BY=3,
    COLOR='WHITE',
    BACKCOLOR=RGB(158 184 153),
$
TYPE=GRANDTOTAL,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
    STYLE=BOLD,
$
ENDSTYLE
END

The initial output shows only the top level BY field (REGION), as shown in the following image.

Clicking the plus sign (+) next to the Midwest region opens the rows that show the states associated with that region, as shown in the following image.

Clicking the plus sign (+) next to the state IL opens the rows that show the categories associated with that state, as shown in the following image.

Clicking the plus sign (+) next to the Coffee category shows the products associated with that category, as shown in the following image. This is the lowest level of the Accordion By Row report.



Example: Creating an Accordion By Row PRINT Report

The following request against the EMPLOYEE data source has two sort fields, DEPARTMENT and YEAR. It uses the PRINT display command.

SET EXPANDBYROW = ALL
DEFINE FILE EMPLOYEE
YEAR/YY = HIRE_DATE;
YEARMO/YYM = HIRE_DATE;
END
TABLE FILE EMPLOYEE
PRINT LAST_NAME AS 'Last,Name' FIRST_NAME AS 'First,Name'
CURR_SAL AS 'Current,Salary' ED_HRS AS 'Education,Hours'
BY DEPARTMENT BY YEAR
WHERE YEAR GT 1980
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,
    COLOR=RGB(66 70 73),
    FONT='TREBUCHET MS',
    SIZE=9,
    SQUEEZE=ON,
    GRID=OFF,
$
TYPE=TITLE,
    BACKCOLOR=RGB(102 102 102),
    COLOR=RGB(255 255 255),
    STYLE=-UNDERLINE+BOLD,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
$
TYPE=TITLE,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
    STYLE=-UNDERLINE,
$
TYPE=HEADING,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
$
TYPE=FOOTING,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
$
TYPE=SUBTOTAL,
    BACKCOLOR=RGB(72 118 91),
$
TYPE=SUBTOTAL,
    BY=1,
    COLOR='WHITE',
$
TYPE=SUBTOTAL,
    BY=2,
    COLOR='WHITE',
    BACKCOLOR=RGB(132 159 126),
$
TYPE=SUBTOTAL,
    BY=3,
    COLOR='WHITE',
    BACKCOLOR=RGB(158 184 153),
$
TYPE=GRANDTOTAL,
    COLOR='WHITE',
    BACKCOLOR=RGB(52 85 64),
    STYLE=BOLD,
$
ENDSTYLE
END

Including the fields LAST_NAME and FIRST_NAME in the report output distinguishes each detail line. However, those fields do not apply to the summary lines, so they are blank on the summary lines.

The output is:


Top of page

x
Accordion By Row Tooltips

By default, EXPANDBYROW reports display field information in tooltips activated when you hover the mouse over the values at each level of the tree. Since the column titles are not displayed above the tree control columns, as they would be in a standard HTML report, the field list for the tree is presented in the tooltip in the top-left corner of the report.

Pop-up field descriptions can also be enabled in Accordion By Row reports to present the field descriptions maintained within the Master File or DEFINE associated with the fields.

Titles can be customized by defining an AS name. To remove pop-up field descriptions from the expanding tree, define a blank AS name for the column title. In an Accordion By Row report, pop-up text boxes that display on mouse over present additional information about the fields and columns within the report. In standard Accordion reports, these pop-up text boxes display the column title or AS name for all of the BY values in the expandable tree.

As with standard HTML reports, the POPUPDESC parameter can be set ON to display field descriptions in these pop-up text boxes for all verb columns. Additionally, turning POPUPDESC ON will cause the BY field pop-up text to present the description value, if available.

The table below represents the order of precedence for descriptions displayed in tooltips when the EXPANDBYROW setting is on.

Existing Field Information

Pop-Up Description Off

Pop-Up Description On

Description

 

1

AS Name

1

2

Column Title

2

3

Field Name

3

4

The color and size presentation of the tooltips and pop-up descriptions have been standardized for a uniform look throughout all reports.



Example: Creating an Accordion By Row Report Without Pop-Up Field Descriptions

The following example demonstrates how pop-up text will display for the standard Accordion report in the default presentation, which means pop-up descriptions are not turned on.

DEFINE FILE GGSALES.
UNITS/D12C DESCRIPTION ''=UNITS;
TOTSALES/D12CM DESCRIPTION 'DOLLARS*UNITS'=DOLLARS*UNITS;
END
TABLE FILE GGSALES
SUM DOLLARS UNITS AS 'Units'
TOTSALES AS 'Total Sales'
BY REGION 
BY CATEGORY AS ''
BY PRODUCT AS 'Product AS Name'
ON TABLE SET EXPANDBYROW ALL
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET STYLE *
TYPE=REPORT,
    COLOR=RGB(66 70 73),
    FONT='TREBUCHET MS',
    SIZE=9,
    SQUEEZE=ON,
    GRID=OFF,
$
TYPE=TITLE,
    BACKCOLOR=RGB(102 102 102),
    COLOR=RGB(255 255 255),
    STYLE=-UNDERLINE+BOLD,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
$
TYPE=SUBTOTAL,
    BACKCOLOR=RGB(200 200 200),
    STYLE=BOLD,
 $
TYPE=GRANDTOTAL,
    BACKCOLOR=RGB(66 70 73),
    COLOR=RGB(255 255 255),
    STYLE=BOLD,
$
END

Fields as defined in the Master File:

FIELD=CATEGORY, ALIAS=E02, FORMAT=A11, INDEX=I, TITLE='Category',
   DESC='Product category',$
FIELD=PRODUCT, ALIAS=E04, FORMAT=A16, TITLE='Product', DESC='Product name',$
 FIELD=REGION, ALIAS=E05, FORMAT=A11, INDEX=I, TITLE='Region',
   DESC='Region code',$
FIELD=UNITS, ALIAS=E10, FORMAT=I08, TITLE='Unit Sales',
   DESC='Number of units sold',$

The following image shows the pop-up description for the tree control, located at the top-left corner of the table, displaying the list of column titles or AS name for the given BY column within the underlying tree control.

The following image shows the pop-up text that will display when the mouse hovers over any of the top level BY values that do not have an AS name, but do have a defined description and title. In this case, the column titles will display.

The pop-up text for a top level BY value that has an AS name and a defined description will display the AS name.



Example: Creating an Accordion By Row Report With Pop-Up Field Descriptions

The following example demonstrates how pop-up text displays with pop-up descriptions turned on.

SET POPUPDESC = ON
DEFINE FILE GGSALES.
UNITS/D12C DESCRIPTION ''=UNITS;
TOTSALES/D12CM DESCRIPTION 'DOLLARS*UNITS'=DOLLARS*UNITS;
END
TABLE FILE GGSALES
SUM DOLLARS UNITS AS 'Units'
TOTSALES AS 'Total Sales'
BY REGION 
BY CATEGORY AS ''
BY PRODUCT AS 'Product AS Name'
ON TABLE SET EXPANDBYROW ALL
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,$
END

As with all HTML reports, setting POPUPDESC=ON will activate a text box that displays the field descriptions for each of the verb column titles.

Additionally, with POPUPDESC set ON, the field description will be presented for the BY elements within the tree. If no field description is defined, the column title or AS name will display.

With POPUPDESC=ON, the defined description will display in the pop-up text, as shown in the following image.

The image that shows the description for the BY value appears in the pop-up text even though an AS name has been given to this field.

For additional information on pop-up field descriptions with HTML reports, see Displaying Report Data.


Top of page

x
Accordion By Row With NOPRINT

Hidden, NOPRINT BY fields, can be used in Accordion by Row reports. They allow the calculation of values for, and the sorting of data by, fields which are hidden. NOPRINT sort fields are included in the internal matrix and affect the sorting and aggregation of data in the Accordion report, even though they are not displayed in the report. These NOPRINT sort fields are defined using the BY sortfield NOPRINT phrase.



Example: Creating an Accordion By Row Report With an Explicit NOPRINT

The following request against the EMPLOYEE data source shows salary data for employees, grouped in categories. The output is sorted by the virtual field NAME_SORT, which concatenates the LAST_NAME and FIRST_NAME fields. The NAME_SORT field is hidden using NOPRINT on the sort phrase. To display employee names, the NAME_DISPLAY virtual field is created, which concatenates the FIRST_NAME field and the LAST_NAME field.

DEFINE FILE EMPLOYEE
NAME_SORT/A50=EMPLOYEE.EMPINFO.LAST_NAME || ( ', ' | EMPLOYEE.EMPINFO.FIRST_NAME ); 
NAME_DISPLAY/A57=EMPLOYEE.EMPINFO.FIRST_NAME | EMPLOYEE.EMPINFO.LAST_NAME;
NAME_CODE/A1=EDIT(LAST_NAME, '9'); 
NAME_GROUP/A10=IF NAME_CODE LE 'G' THEN 'A-G' ELSE IF NAME_CODE LE 'P'
 THEN 'H-P' ELSE 'Q-Z';
END
TABLE FILE EMPLOYEE
SUM
EMPLOYEE.EMPINFO.CURR_SAL AS 'Current Salary'
BY NAME_GROUP AS 'Alphabetical Group'
BY LOWEST NAME_SORT NOPRINT
BY LOWEST NAME_DISPLAY AS 'Employee Name'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET EXPANDBYROW 2
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET DROPBLNKLINE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,
    COLOR=RGB(66 70 73),
    FONT='TREBUCHET MS',
    SIZE=9,
    SQUEEZE=ON,
    GRID=OFF,
$
TYPE=TITLE,
    BACKCOLOR=RGB(102 102 102),
    COLOR=RGB(255 255 255),
    STYLE=-UNDERLINE+BOLD,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
$
TYPE=SUBTOTAL
    BY=1,
    BACKCOLOR=RGB(200 200 200),
    STYLE=BOLD,
$
TYPE=SUBTOTAL,
    BY=2,
    BACKCOLOR=RGB(200 220 220),
    STYLE=BOLD,
$
TYPE=SUBTOTAL,
    BY=3,
    BACKCOLOR=RGB(220 220 200),
    STYLE=BOLD,
$
TYPE=GRANDTOTAL,
    BACKCOLOR=RGB(66 70 73),
    COLOR=RGB(255 255 255),
    STYLE=BOLD,
$
END

If you hover the mouse over any value in the NAME_DISPLAY column, the tooltip will display the AS name, Employee Name, as shown in the following image.


Top of page

x
Differences Between Reformatted and Redefined BY Fields

Reference:

When a sort field is dynamically reformatted, both the original and reformatted fields are placed in the internal matrix. The original field is not displayed, but is used to sort or aggregate values.

When using a redefined field, the new column is used to display, sort, or aggregate values.



Example: Creating an Accordion By Row Report With Dynamically Reformatted BY Fields

The following request against the EMPLOYEE data source shows employees and salaries by year of hire. The display fields, HIRE_DATE and CURR_SAL, are sorted by HIRE_DATE reformatted with the format, YY, and by the virtual field NAME_DISPLAY (employee name).

DEFINE FILE EMPLOYEE
NAME_SORT/A50=EMPLOYEE.EMPINFO.LAST_NAME || ( ', ' |
 EMPLOYEE.EMPINFO.FIRST_NAME ); NAME_DISPLAY/A57=EMPLOYEE.EMPINFO.FIRST_NAME | EMPLOYEE.EMPINFO.LAST_NAME;NAME_CODE/A1=EDIT(LAST_NAME, '9'); NAME_GROUP/A10=IF NAME_CODE LE 'G'
 THEN 'A-G' ELSE IF NAME_CODE LE  'P' THEN 'H-P' ELSE 'Q-Z'; 
END 
TABLE FILE EMPLOYEE
SUM
HIRE_DATE
EMPLOYEE.EMPINFO.CURR_SAL
BY HIRE_DATE/YY
BY LOWEST NAME_DISPLAY AS 'Employee Name'
ON TABLE SET PAGE-NUM NOLEAD
WHERE HIRE_DATE LT '820101';
ON TABLE SET EXPANDBYROW ALL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET DROPBLNKLINE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,$
END

In the report generated, there are multiple expandable groups or nodes for the same year, 1981. This occurs because the reformatted values are used for display, but the original values are still used for sorting and aggregating. In this report, 1981 is the common value used to represent two different dates, 81/07/01 and 81/11/02. The sorting takes place on the date, not on the year.



Example: Creating an Accordion By Row Report With Redefined BY Fields

To sort your data on the reformatted field values instead of the original field values, create a virtual field containing the BY value with the new format applied. This will allow you to display, sort, and aggregate on the new redefined BY value.

DEFINE FILE EMPLOYEE
NAME_SORT/A50=EMPLOYEE.EMPINFO.LAST_NAME || ( ', ' | EMPLOYEE.EMPINFO.FIRST_NAME );
NAME_DISPLAY/A57=EMPLOYEE.EMPINFO.FIRST_NAME | EMPLOYEE.EMPINFO.LAST_NAME;
NAME_CODE/A1=EDIT(LAST_NAME, '9');
NAME_GROUP/A10=IF NAME_CODE LE 'G' THEN 'A-G' ELSE IF NAME_CODE LE 'P'
 THEN 'H-P' ELSE 'Q-Z';
DATE_HIRED/YY=HIRE_DATE;
END
TABLE FILE EMPLOYEE
SUM EMPLOYEE.EMPINFO.CURR_SAL
BY DATE_HIRED
BY LOWEST NAME_DISPLAY AS 'Employee Name'
ON TABLE SET PAGE-NUM NOLEAD
WHERE HIRE_DATE LT '820101';
ON TABLE SET EXPANDBYROW ALL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET DROPBLNKLINE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,$
END

In the above request, the verb fields, HIRE_DATE and CURR_SAL are sorted by the DEFINE field, DATE_HIRED. The result is a report sorted and aggregated by the redefined date values, as shown in the following image.



x
Reference: Usage for SET EXPANDBYROW

The following features are not supported with Accordion By Row reports:

In certain scenarios, a blank line is generated before a subtotal on the report output. You can eliminate these automatic blank lines by issuing the SET DROPBLNKLINE=ON command.


Top of page

x
Creating an Accordion By Column Report

How to:

Reference:

Accordion By Column reports do not automatically display entire report rows. A plus sign appears to the left of each data value in the column under the highest-level sort heading. For data associated with lower-level sort fields, a plus sign is placed to the left of each data value, but the data does not appear unless manually expanded. Data values of the lowest-level sort field are not expandable. To expand your view of data for any expandable sort field, click a plus sign and all data associated with the next lower-level sort field appears. When you expand a data value under the next to lowest sort heading, all of the remaining associated data values in the report appear. This type of Accordion Report is generated using the SET EXPANDABLE command.



x
Reference: Support for Accordion By Column Reports

The following commands are not supported when using Accordion Reports:

BORDER, COLUMN, FOR, IN, OVER, PAGE-NUM, ROW-TOTAL, TOTAL

Data Visualization, HTML BYTOC, OLAP, On Demand Paging (WebFOCUS Viewer), column freezing, and the ReportCaster burst feature are also not supported with Accordion Reports.



x
Syntax: How to Create Accordion by Column Reports

To enable Accordion By Column Reports, specify the following

ON TABLE SET EXPANDABLE = {ON|OFF}

where:

ON
Enables Accordion By Column Reports.
OFF
Disables Accordion By Column Reports. OFF is the default value.


Example: Creating an Accordion By Column Report

This example shows how to use an EXPANDABLE command to create an Accordion By Column Report.

TABLE FILE GGSALES
SUM UNITS DOLLARS
BY REGION BY ST BY CITY BY CATEGORY
ON TABLE SET EXPANDABLE ON
END

The following image shows an Accordion by Column Report which displays all data associated with the first-level sort field, Region, by default. The expanded data values you see are the result of a report user clicking plus signs to the left of specific first, second- and third-level sort fields after the report is generated.


WebFOCUS