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:
The use of horizontal sort fields coded with ACROSS phrases is supported with Accordion Reports. The ACROSS sort headings that appear above vertical sort headings in a standard HTML report do not display in an Accordion Report until at least one sorted data value has been manually expanded in each expandable sort column.
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.
Reference: |
The following requirements must be taken into consideration when creating Accordion Reports:
However, for distribution of reports using ReportCaster, see the following Reference topic to ensure that the report is delivered correctly as an e-mail attachment or as an archived report in the Report Library.
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:
For more information about coding reports for use with ReportCaster, see the Tips and Techniques for Coding a ReportCaster Report appendix in your ReportCaster manual.
How to: Reference: |
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.
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.
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.
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.
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. 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. Column 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
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 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.
SET EXPANDBYROW = {OFF|ON|n}
ON TABLE SET EXPANDBYROW {OFF|ON|n}
where:
Note: Accordion By Row reports require that the HTMLCSS parameter be set to ON.
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 * INCLUDE= ENDEFLT.STY,$ ENDSTYLE 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:
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 * INCLUDE=ENDEFLT.STY,$ 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:
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.
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.
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.
To enable Accordion By Column Reports, specify the following
ON TABLE SET EXPANDABLE = {ON|OFF}
where:
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 |