Report Builder reference guide

This data sheet will help you to create custom reports in Organise Pro. If you are a developer and make a report which might be useful to others, please let me (Shiela) know and I may be able to link to it from the peacockmedia website.

I've revised this page so that it relates to the new Report Builder app which is free and included in the Organise Pro download from Organise v6.2. If you would like to hand-code then please refer to the previous version of this page which is here.

As a starting point You may like to start with a blank report and build something simple - a tutorial is here. Alternatively you may like to open an existing report, 'Save As...' a different name and then play with it.

Select from

This specifies the table that you want to pull data from. Possible values:

contacts
itemspurchased (accesses the full list of all items appearing on orders. These are entirely separate records from items)
orders
items
actions (this is the time management table)
bills (this is the accounts management 'money out' table)

To select from this table:

fieldname - you will only be able to select a valid field name from the table you are using. See fieldname chart for details.
operator: possible values: 'equals', 'contains', 'does not equal', 'greater than' and 'less than' - these work with strings, numbers and dates)
comparator: value to compare with. Note that strings don't need quotes.

The comparator can be left empty to mean 'empty' equivalent to: where balancepaid = ""
ie. where nothing has been entered in the 'balance paid' field

Columns and fields

Use 'Add' 'Edit' and 'Delete' to manage the fields that you wish to display and the columns and column headings for displaying them. You can resize and re-order the columns by clicking and dragging.

You will only be allowed to choose field names from the table specified in "Select from" above.

Totals

This indicates whether one of the columns should be totalled. If both values are 'none' then no column will be totalled. You can have one column or two columns totalled.

Date selection

If a field is chosen here, then the date selection buttons will be enabled when the report is run. This must match a column containing a date. The user will be able to filter the results of this report by date.

Collation

These boxes determine which column is compared when collating, and which column is summed when items are collated.

An example of a list which isn't collated:
item1   3
item2   2
item1   1

The same list collated looks like this: (column 1 is the match column and column 2 is the qty column)
item1   4
item2   2

The Order record has a special field: 'one' which returns '1'. This can be used with collation for counting items - see the default report 'New vs Returning Customers' for an example.

Graphs

From v6.1, the Report Manager can generate a graph from your data, showing dates on the x axis (weeks or months) and data on the y axis (eg sales). The application installs two graph reports called Weekly sales and Monthly sales. Open up these xml files for reference.

First of all you will need to select data and define columns as described above (we'll call this your data selection). You can have as many columns in this table as you like (for this bit you can copy an existing report) but as a minimum it must have a date column and a data column (eg sales).

You can use datefiltercol for date selection (see Date Selection above).

Second, you will need to define a pivot table. The pivot table has two columns, pivot date and pivot data. These must correspond with a date column and a data column in your data selection.

Type has three possible values -

data
pivot
graph

graph is the one to use if you want to display the graph. data is the default. You can choose pivot if you wish to display the pivot table.

Pivot interval has two possible values -

week
month

<<contents

You are viewing part of the manual / help for Organise for Mac OSX by Peacockmedia.