Controlling the Display of Sorted Data With Accordion 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,
$
ENDSTYLE
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=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
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.

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,
$
ENDSTYLE
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.

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=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
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=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
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.