spreadsheet.article Article/Article

spreadsheet.article

Command spreadsheet.article Article/Article
Applicable release versions:
Category Article (24)
Description discusses using the spreadsheet connective.

contributed by Chris Alvarez. Original article ran in PickWorld Magazine.

One great advantage to spreadsheet programs is the ability to represent reporting data in columns and rows. While the Access query language provided with Advanced Pick does not have spreadsheet functionality, the new 'ss' connective gives the user the ability to create reports similar to popular spreadsheet programs.

The Spreadsheet connective gives Access the ability to build reports with column and row headings based upon date 'buckets'. For example, if the database contains a sales history file, it is possible to build a report with sales totals for each month of the year across the column headings and one line per dealer on the row heading. Without the spreadsheet connective, this report would only be possible by creating 12 separate dictionaries for each of the months.

With the 'ss' connective, this can be accomplished with any current date dictionary attribute.

In order to produce the above sales report, it is first necessary to construct a date attribute with an output conversion for our bucket headings. For example, the database contains a sales history file called 'SALES' which contains the order date in attribute 4. The following attribute called 'MONTH' will serve as our bucket headings:


MONTH
001 A
002 4
003
004
005
006
007 A4(DMA)["1","3"]
008
009 L
010 8


The a-correlative on the output conversion (line 7) will convert the internal date found in attribute 4 to the alphabetic month name and then extract the first 3 characters. This will serve as the headings to the columns for our report.

The next step is to build a dictionary to display the total dolla mount for each order. This attribute will be the value 'spreadsheet' across the column buckets. The 'SALES' file holds the total for each order in attribute 20:


ORDER.TOTAL
001 A
002 20
003
004
005
006
007 MR02,
008
009 R
010 10


The final dictionary required for our example is the attribute to display the dealer name. The 'SALES' file holds the customer number in attribute one and the following dictionary uses that number to translate to the customer file to pick up the customer name held in attribute 1 of the customer file:


DEALER
001 A
002 1
003
004
005
006
007
008 TCUSTOMER;X;;1
009 L
010 30


The syntax of the spreadsheet connective is:

SS beg.date end.date attribute

where the 'ss' is the connective, the beg.date and end.date is the beginning and ending date range for this report in external format and delimited with quotes, and the attribute is the dictionary attribute to be spreadsheet across the columns. Using the 3 attributes from above, the following Access statement would produce the sales report for our example:


SORT SALES BY DEALER BREAK-ON DEALER SS "01/01/92" "12/31/92" ORDER.TOTAL DET-SUPP


The column width used for each of the monthly columns is the current column width for the ORDER.TOTAL attribute, in this case 10. Also, please keep in mind that the date conversion in the date attribute, in this case MONTH, must always be on the output conversion for the spreadsheet connective to work properly.

When experimenting with this example, another new connective exists to easily limit the amount of data used for the sort. Simply add 'SAMPLING 10' to the end of the above statement and the system will find 10 items that match the specified selection criteria and print the report using only those 10 items.
Syntax
Options
Example
Purpose
Related access.ss