The ss (spreadsheet) connective allows printing out AQL reports in spreadsheet format. This is achieved by adding the ss connective to a sort sentence and defining the desired range parameters.
Syntax
ss attr.name1 {{beg.date} {end.date}} attr.name2 {(g} |
Parameter(s)
attr.name1 |
Attribute-defining item containing a date that limits the tabulation. The output form of this attribute determines the column headings. The output form of attr.name1 does not have to be the same as the values included in beg.date and end.date. For example, attr.name1 may display the month, while beg.date and end.date must be in the form "mm/dd/yy" enclosed in double quotation marks (" "). |
beg.date |
Beginning date range to be included. If not specified, beg.date is determined by the number of columns that physically fit on the display or printout. |
end.date |
Ending date range to be included. If not specified, the current system date is used as the default. |
attr.name2 |
Attribute that contains the values to be columnar and cross (row) totaled. |
g |
Suppresses the row and column totals. |
Description
The ss modifier produces columnar and cross totals on rows of designated attributes within a given range of dates. If the width of the report exceeds the width of the output device, the extra columns are truncated. If two or more ss connectives are used in the same AQL sentence, a single report is generated, with columns for each subsequent ss connective following the totals column for the previous connective.
Column headings are created for each possible value produced by attr.name1 within the beginning date and ending date range. Output-conversions are processed before producing the heading. Correlatives are not processed. The format of the column is determined by attr.name2. Each cell in the listing contains the total value of attr.name2 for the date specified in that column heading.
The roll-on command can be used in conjunction with ss to produce subtotals by specified categories. Each rolled attribute value produces a row in the output. If no roll-on is specified, only a total line is produced.
The granularity of the date display is determined by the output-conversion of the attr.name1. The date granularity is the value of the last multiply in the output-conversion. If the following output-conversion is used to provide week ending dates, the report has a granularity of 7 days and the dates are the Saturday after the date in the item. In both of the following examples, the date is stored in attribute 4.
a4/’7’*’7’+’6’ d2/ |
whereas the following output-correlative makes a granularity of 3 days:
a4/’3’*’3’ d2/ |
The number of dates to display is based upon the display width of the attr.name2 attribute, but the width of the columns on the report is based upon the greater of the attr.name1 display width and the attr.name2 display width.
Example(s)
The example below produces a report with subtotals for each code and totals to each category within the code for the quarters 1-04, 2-04, 3-04, and 4-04.
list invoices with code "c" "d" and with amount ne "" by code by category ss quarter "01/01/04" "12/31/04" amount roll-on code roll-on category "’d’" det-supp id-supp |
The beginning date range is calculated based on the attribute’s width, the output device’s width, and the ending date of 06/30/04.
list invoices by customer ss quarter "06/30/04" amount roll-on customer det-supp id-supp |
See Also