Introduction

Checkout ships with a collection of reports that can display a variety of information about your store. You can expand the standard collection of reports by installing additional reports or by creating your own. This document describes how to create your own Checkout Report.

The standard Checkout Reports are stored inside the Checkout application bundle under “Contents/Resources/Reports”. Any custom reports you install are stored in the “Reports” folder in Checkout's Application Support folder. The easiest way to find that folder is by selecting “Reveal Database” under the “Checkout” menu inside Checkout, you'll find it right next to the “Database.checkout” folder that's highlighted in the Finder: “~/Library/Application Support/Checkout/Reports”. You can also double-click any report to let Checkout install it in the right location.

Anatomy

A Checkout report is a Mac OS X bundle (basically a folder with a special extension) that contains a couple of files. The extension of a Checkout Report is .checkoutreport. To create a new checkout report, just create a folder in the Finder and name it [reportname].checkoutreport and add the files described below. The easiest way to edit files within a Mac OS X bundle, is to drag the entire bundle on a text editor application like for instance BBEdit or TextMate.

• report.py - contains all the code to generate data for the report to display and export.
• template.html - contains the layout information in html and css to display the report in Checkout.
• icon.png (optional) - the report icon to display in the report overview.

Files

The file “report.py”

The report.py is a simple python script with a couple of variables and two functions that get called when needed by Checkout. Python is a relatively easy but powerful scripting language that lets you manipulate the data any way you like. Visit this link for a simple introduction to Python.

Every report.py script must begin by importing the reports api: from api.reports import *. This exposes the variables needed to query the Checkout database.

Table 1: Variables for report.py

Name	Type	Optional	Description	Example
version	string	No	The current version of the report.	'1.0'
description	string	No	A brief description of what the report does.	'All sales in a timeframe.'
icon	string	No	The icon image for the report.	'icon.png'
isExportable	boolean	No	Determines wether the report can be exported. If True it the report needs to implement the exportRows() function.	True
dateSelection	boolean	No	Determines wether the report implements a date range.	True
exportColumnTypes	list of column types	No	Describes the data types for the columns for exporting.	[ExportDateTimeType, ExportStringType, ExportCurrencyType]
exportColumnWidths	list of integers	No	The width for each column for exporting to Excel or Numbers.	[100, 200, 200]

These following two functions need to be implemented in the report.py script for the report to function. The both get a dictionary as input value with the keys begin and end. They contain the begin and end date objects to filter the data with.

Table 2: Functions for report.py

Name	Output	Description
data(dateRange)	dictionary	This is the main function of the script that generates a data structure to render the template with. Typically it would fetch data from the database, sort it and filter it to get a specific result and return the result.
exportRows(dateRange)	list with lists	This function generates a list of lists to export. Each list represents a row and each list in the row represents a column. Typically it takes the result of the data(dateRange) function and converts the needed column data into a list.

Fetching data

The syntax to query the database can be best described with an example. The following code would return a list of all the products in the database:

Database.query(Product).all()

This works for any object in the database like for example: Client, Invoice, Order etc(see table 3). Adittionaly the results can be filtered based on a value:

Database.query(Product).filter(Product.c.has_stock==True).all()

This will return a list of all products with stock. You can also combine filters with the and_ or or_ methods.

Database.query(Invoice).filter(and_(
		Invoice.c.date>=datetime.now() - timedelta(days=30),
		Invoice.c.date<=datetime.now(),
		Invoice.c.id_parent==None))

This will return a list of all invoices within the last 30 days. As you can see you can make pretty powerful search queries like this. For a full database object reference see table 4.

Putting it all together

Combining everything above, we can now create a simple exportable report to show a list of invoices by date range with totals.

from api.reports import *
version = '1.0'
description = 'List of invoices'
icon = 'icon.png'
isExportable = True
dateSelection = True
localizeHeader = True
exportColumnTypes = [constant.ExportStringType, constant.ExportNumericType]
exportColumnWidths = [100, 100]
def data(dateRange):
	# Fetch the invoices within the given date range
	invoices = Database.query(Invoice).filter(and_(
		Invoice.c.date>=dateRange['begin'],
		Invoice.c.date<=dateRange['end'] + timedelta(days=1),
		Invoice.c.id_parent==None))
	return {'invoices': invoices}
def exportRows(dateRange):
	# Add the column headers
	rows = [['number', 'total']]
	# Create a new row for each invoice
	for invoice in data(dateRange)['invoices']:
		rows.append([invoice.formattedNumber(), invoice.totalWithTax()])
	return rows

The file “template.html”

The template.html file is responsible to display the data in Checkout. Typically this is a table that draws a row for each element in the data. As the format is based on css/html you can display it in any way you like. You can use the default Checkout report style by including a standard css file we provide. Column sorting is implemented in javascript and can be customized any way you like. To include the standard design in your template add this to the head tag:

<script src="file://$corePath/sorttable.js" type="text/javascript"
charset="utf-8"></script>
<link rel="stylesheet" href="file://$corePath/report.css?version=$randomValue"
type="text/css" charset="utf-8">
<link rel="stylesheet" href="file://$corePath/print.css?version=$randomValue"
media="print" type="text/css" charset="utf-8">

To make the template actually display the data, some template logic is required. We use the Cheetah template engine to parse our template before displaying. All the variables from the data(dateRange) are available, and the variables we supply to include the default design and format variables.

Loading and testing a Report

To load a report in Checkout, simply double-click it in the Finder. You can have only one custom report with the same name installed at the same time. If a report with the same name already exists, Checkout asks you if you want to replace it.

Custom reports are installed locally on your computer, so they are not automatically available to all computers that are logged in to the same store. To see where your custom reports are stored and to edit or delete them, choose “Reveal Database” from the “Checkout” menu. The reports are stored next to the Database folder in a folder called “Reports”. You can edit reports in this folder directly, to quickly see the effects, control+click in the reports manager window, and choose 'Reload' from the context menu that appears.

More Examples

In the Finder, control+click on the Checkout application and choose “Show Package Contents” from the contextual menu that appears. Then navigate to “Resources” -> “Reports”. All of Checkout's standard reports are created using the same API as described here. The only difference is that the standard reports have an additional “name” attribute that is used instead of their file name in the Reports Gallery.

Table 3: Template rendering

Variable	Description	Example
Logic
$variable	A variable, to be replaced by the value	$invoice, $product.name, $order.client.md.email
#for x in x ... #end for	A for loop to loop through a collection	#for $invoice in $invoices ... #end for
$sum(collection)	The sum of a list of numbers	$sum([$invoice.totalEx for $invoice in $invoices])
$len(collection)	The count of a collection	$len([$invoices])
Defaults
$report.name	The report name	$report.name -> Invoice report
$report.dateRange.begin	The date range begin	[date Object]
$report.dateRange.end	The date range end	[date Object]
$currentDate	The current date and time	[date Object]
Formatting
$format.string(string)	Tries to translate a string from English to the current language	$format.string('Hello') -> Bonjour
$format.currency(number)	Formats a number as a currency value, for totals	$format.currency(12) -> $ 12.00
$format.quantity(number)	Formats a number as a quantity value, for stock amounts	$format.quantity(12.00) -> 12
$format.round(number, decimals)	Rounds a number to the given amount of decimals	$format.round(12.003676, 3) -> 12.004
$format.longDate(date)	Formats a date object to a long date and time string	$format.longDate($currentDate) -> Sunday, January 4, 2025 2:30 PM
$format.shortDate(date)	Formats a date object to a short date and time string	$format.shortDate($dateRange.end) -> Jan 4, 2025 2:30 PM
$format.onlyDate(date)	Formats a date object to display a formatted date	$format.onlyDate($currentDate) -> Sunday, January 4, 2025
$format.onlyTime(date)	Calculates the days from now	$format.onlyTime($currentDate) -> 12

By using all above we can create a simple template file for the script we built above:

<html>
<head>
    <script src="file://$corePath/sorttable.js" type="text/javascript"
charset="utf-8"></script>
    <link rel="stylesheet" href="file://$corePath/report.css?version=$randomValue"
type="text/css" charset="utf-8">
    <link rel="stylesheet" href="file://$corePath/print.css?version=$randomValue"
media="print" type="text/css" charset="utf-8">
</head>
<body>
    <div id="container">
        <table cellspacing="0" cellpadding="0" class="sortable">
            <thead>
                <tr>
                    <th>$format.string('Date')</th>
                    <th>$format.string('Number')</th>
                    <th>$format.string('Total')</th>
                </tr>
            </thead>
            <tbody>
                #for $invoice in $invoices
                <tr>
                    <td>$format.longDate($invoice.date)</td>
                    <td>$invoice.formattedNumber</td>
                    <td>$format.currency($invoice.totalPriceIn)</td>
                </tr>
                #end for
            </tbody>
        </table>
    </div>
</body>
</html>

That's it! We've built a simple exportable report that now displays in Checkout too. From here you should be able to write your own more complicated templates

Full API Reference

Table 4: Data Object Reference

Property	Filterable	Description	Example
Client, Employee, Supplier
name()	No	Full name	Allison Abigail Wood
formattedName()	No	Name and company name	MYOB US (Allison Abigail Wood)
md['firstname']	No	First (given) name	Allison
md['middlename']	No	Middle name	Abigail
md['lastname']	No	Last name	Wood
md['company']	No	Company name	MYOB US
md['street']	No	Street	523 Chestnut Street
md['zipcode']	No	Zip code	10101
md['state']	No	State	KY
md['city']	No	City	Louisville
md['country']	No	Country	USA
md['phone']	No	Phone number	1 800 2983
md['email']	No	Email address	allison@example.com
Product
name()	No	Name	iPod 40GB
md['code']	No	Code/SKU	MA8664N/A
md['brand']	No	Brand	Apple
md['barcode']	No	Barcode	B03498475694
md['description']	No	Description	Portable audio player
priceEx	No	Price excluding tax	20.00
priceIn	No	Price including tax	25.00
stock.quantity	No	Amount of items in stock	200
stock.ordered	No	Amount of items currently on order	20
stock.allocated	No	Amount of items currently allocated on orders (reserved)	10
Parent Order, Invoice
parent	Yes	Always None	None
children	No	A collection of ordered products	[[Order Object], [Order Object]]
client (or i)	Yes (as i)	Client, None if anonymous order	[Client Object] or None
md[value]	No	Metadata value, any of a client object at the moment of order creation	name, street, email, etc.
formattedNumber	No	Order number with formatting	2009-001
date	Yes	Creation date	[datetime Object]
employee	Yes	Employee who created the order	[Employee Object]
totalDiscountEx()	No	Total discount for order	20.00
totalPriceEx()	No	Price excluding tax	20.00
totalTax()	No	Total amount of tax	5.00
totalPriceIn()	No	Price including tax	25.00
isPaid()	No	Is the order paid in full	False
totalPaid()	No	The total paid amount for this order	20.00
due()	No	The total paid due	10.00
payments	No	List of payments for this order	[[Payment Object], [Payment Object]]
terminal	Yes	Terminal used to create order	[Terminal Object]
Child Order, Invoice
parent	Yes	The parent order object	[Order Object]
product (or i)	Yes (as i)	Product object	[Product Object]
date	Yes	Creation date	[datetime Object]
md[value]	No	Metadata value, any of a product object at the moment of order creation	name, code, brand, description
quantity	Yes (as _quantity)	The amount of products	5
allocation	No	The amount of allocated products (order only)	5
totalDiscountEx()	No	Total discount for product	20.00
totalPriceEx()	No	Price excluding tax	20.00
totalTax()	No	Total amount of tax	5.00
totalPriceIn()	No	Price including tax	25.00
taxgroups()	No	List of TaxGroup objects	[[TaxGroup Object], [TaxGroup Object]]
terminal	Yes	Terminal used to create order	[Terminal Object]
TaxGroup
name	No	Name	State Tax
code	No	Code	STATE-12
rate	No	Rate	12.00
OnlineCreditCardPayment, CreditCardPayment, DebitCardPayment, ChequePayment, CashPayment, TransferPayment, ChangePayment
client	Yes	The client that made the payment	[Client Object]
amount	Yes	The payed amount	20.00
Terminal
md['name']	No	Name	Frontdesk Computer
md['number']	No	A unique terminal number	5

Below is a list of all the possible column types that you can supply in your report's exportColumnTypes attribute. These are used to format the data when your report is exported to Excel or Numbers file formats. Using “ExportLocalizedStringType” makes Checkout attempt to translate data prior to exporting, in all other cases the data is exported as is, the formatting options are added in the spreadsheet's native format.

Table 5: Export Column Types

Name	Usage
ExportStringType	Normal textual data
ExportLocalizedStringType	Normal textual data, translated if possible
ExportNumericType	Floating point numbers
ExportCurrencyType	Monetary amounts, with two decimals
ExportIntegerType	Whole numbers
ExportDateType	Dates
ExportDateTimeType	Date and time
ExportTimeType	Just time
ExportHeaderType	Textual data, emphasized