Analyse (basis, sourceFile, searchExpression, breakDownFields, outputFields, from, to [, kind] [, filterFunc])
Result Type: table of tab/newline delimited text
Definition: The Analyse function performs a transaction or job sheet analysis using the parameters you specify. If possible, the result will be table-formatted (i.e. second breakdown level in columns), otherwise it will be list-formatted. The format is simplified to better allow programmatic manipulation of the result (especially using Head, Tail, Slice, etc). The first line of the returned table contains series names; and the first column contains categories/keys
basis: "Transaction" or "JobSheet"
sourceFile: a file name (e.g. "Product", "Name", "Transaction")
searchExpression: a search expression to be applied to the source file to find records to analyse.
breakdownFields: text containing a comma-delimited list of the field names to analyse by, e.g. “Name.Category1,Name.Code". You may specify a breakdown calculation inside brackets instead of a field name. E.g. "Name.Category1,[Left(Name.Code, 2)]"
outputFields: text containing a comma-delimited list of output values, where 1 = count, 2 = qty, 3 = Net, 4 = GST, 5 = Gross etc. (see the output value popups in the Analysis editor). e.g. "2,3" will give you Qty and Net columns. For a single output field, you can follow the field number with a "#", which denotes that the resulting table should include a total column.
from: a start period or start date for the range of transactions or job sheet entries to analyse.
to: an end period or end date
kind: (optional) character codes denoting what kind of analysis. e.g. "IEU" denotes analyse Income and Expenses, including unposted.
I = Income; E = Expense; B = Billed; U = Unbilled (or Unposted)
Default is "IEUB"
filterFunc: (optional) a filter function to filter out detail lines or job sheet entries that you don't want to analyse.
analysis = Analyse ("Transaction", "Name", "customertype <> 0", "Transaction.NameCode,Transaction.Period", "3#", PeriodOffset(CurrentPer, -7), PeriodOffset(CurrentPer, -1))
Analyses transactions for Names. SearchExpression yields customers. Breaks down by NameCode and Period. Only one output field (Net), so will get table format with Periods as column headings. Analyses the last 7 periods. The result can be further refined:
top = head(sort(analysis, -1, 1, 1, 1), 6, "\n")
Gets the top 5 Names of the Analysis by total value over all periods (total is rightmost column, hence sorting by column -1). A descending, numeric sort which skips the first (heading) line brings the top 5 customers. If we want to chart the result (in the Chart part of a custom report) with a period series for each name, we would need to transpose it, and get rid of the total column (last line after transposing)
tochart = head(transpose(top), -1, "\n")