 |
|
|
CrossTab() Creates a database table containing consolidated totals. Syntax CrossTab(<cTable>, <cRowField>, <aColumns>, [<cFilter>], [<xStart>], [<cEnd>]) --> nResult Arguments <cTable> --> The name of the database table to create. Do not specify a path. The table will be created and placed in the default company folder. If the table already exists, it will be erased. Do not specify a table name that would conflict with any table that already exists in your company folder. <cRowField> --> The field name of the table from which the consolidated table will use to base its totals on. For example, if you wished to produce a cross-tabulated table of your sales (INVOICE.DBF), based on the total number in each state, the cRowField would contain the field name "STATE". Of if you wanted to produce a cross-tabulated total of sales by customer, the field name would contain the value "ACCCODE", which is the field used to designate the account code of the records of the INVOICE.DBF table. <aColumns> --> This is a series of array elements containing the structure described in the following table. There must be at least one array of this type within aColumns.
The following script defines several aColumn elements: Declare aColumns Type Array aColumns := {} AADD(aColumns, { "COUNT", 06, 0, "1" } ) AADD(aColumns, { "CARTONS", 06, 0, "Cartons" } ) AADD(aColumns, { "SALES", 18, 2, "Invtot" } ) <cFilter> --> An optional single line CBS expression used for filtering records for the consolidation. If the expression evaluates to TRUE, the record is not included in the cross-tabulated totals. <xStart> --> A value of character, numeric or date type that is used to locate the first matching record in the source table to be consolidated. If supplied, it is assumed that the source table has already been set to the appropriate matching index, using the OrderTable() function. For example, if you were intending to consolidate totals by salesperson between two dates, you would first specify that the index order of the source table be set to its date based ordering. xStart would then contain the first date string to match, typically either dYourDate or DTOS(dYourDate). If the value of xStart does not exactly match a value contained in your source database, CrossTab() will start at the closest matching record. <cEnd> --> An optional expression used to terminate the cross-tabulation process. cEnd is a valid single line CBS expression. If it evaluates to TRUE, the cross-tabulation process ends. For cEnd to be useful, the source table should be set to an indexed order, and cEnd should be evaluating a field value matching this order. For example, if you were intending to consolidate totals by salesperson between two dates, you would specify the last date you intended to include in the consolidation table within the cEnd expression. For sales records this might be "Invoice->Date > dEndDate". (The variable dEndDate is assumed to have been previously defined and that it will contain a last date value.) Returns CrossTab() will build the requested table and return a value of zero if the operation completed successfully. If a problem occurred, an error message is returned or an error code will be returned as a negative number. The following table describes all return error codes that do not display error messages.
Description This function scans the contents of a source table, such as a invoice sales file, customer file, supplier file, job file, etc., and will create a table containing a set of consolidated records with accumulated total fields within it. The first field of the returned table is the row field, which is used as the basis for the consolidation. The remaining fields will hold one or more totals based on the consolidation field. The consolidation table will be sorted by the cRowField. Example #1 Summarise sales by Origin code. The first column is a counter, so it only returns '1'. The second field is the sales total. Declare Answer1 Type Date Declare Answer2 Type Date Declare aColumns Type Array Answer1 := Today() Answer2 := Today() aColumns := {} Invoice->(OrderTable("DATE")) AADD(aColumns, { "COUNT", 06, 0, "1" } ) AADD(aColumns, { "SALES", 18, 2, "INVTOT" } ) Invoice->(CrossTab("ORGSUM", "ORIGIN", aColumns,, DTOS(Answer1), "Date > Answer2")) * Display the result LookUp("ORGSUM") Example #2 Summaries sales by customer account code. Declare Answer1 Type Date Declare Answer2 Type Date Declare aColumns Type Array Answer1 := Today() Answer2 := Today() aColumns := {} Invoice->(OrderTable("DATE")) AADD(aColumns, { "SALES", 18, 2, "INVTOT" } ) Invoice->(CrossTab("ASALES", "ACCCODE", aColumns, ".NOT. IsPayment()", DTOS(Answer1), "Date > Answer2")) * Display the result LookUp("ASALES") Example #3 This script produces a cross-tabulated file broken into sales and credits, by salesperson. Declare Answer1 Type Date Declare Answer2 Type Date Declare aColumns Type Array Answer1 := Today() Answer2 := Today() aColumns := {} Invoice->(OrderTable("DATE")) AADD(aColumns, { "DSALES", 18, 2, "IF(INVTOT > 0, INVTOT, 0)" } ) AADD(aColumns, { "CSALES", 18, 2, "IF(INVTOT < 0, INVTOT, 0)" } ) Invoice->(CrossTab("SALESUM", "SALESMAN", aColumns,, DTOS(Answer1), "Date > Answer2")) * Display the result LookUp("SALESUM") ____________________________ Related Topics |
Most file errors are caused by the table already being
opened or accessed by another application or process.