Report Field Definitions - Clients

Modified on Wed, 27 May at 4:00 PM

Alright — this one’s all about clients. If you’re trying to understand what your clients are buying, how much they’re worth, what they owe you, what pricing they’re on, or what’s still open — this is your playground.

These reports are mostly pivot-style reports. That just means you can slice and dice the data however you like — by client, by project, by month, by region, by supplier — etc.

TABLE OF CONTENTS

Client Activity Lines Pivot Report
Client Specific Pricing Pivot Report
Open Transactions by Client Pivot Report
Open Transaction Lines by Client Pivot Report
Client Details Pivot Report

Client Activity Lines Pivot Report

This is your big-picture activity report.

It pulls together everything that generated revenue or incurred cost in a selected period — Projects, Stock Sales, and Invoices — and lays it out in one pivot grid. What you get is a consolidated view of revenue and costs for whatever timeframe you care about.

Field	Notes / Expected Values	Description
BILLINGCLIENT	text	billing client name
BILLINGCLIENTCODE	numerical value	billing client code
CODE	text	item SKU
CREDITDAYS	numerical value	credit terms in days
DATE	DD/MM/YYYY	date that activity (e.g. material usage) occurred
DAY	numerical value	day that activity (e.g. material usage) occurred
DAYOFWEEK	text	The day name of the activity date
DESCRIPTION	text	line item description
DOCUMENT	numerical value	source transaction (e.g. project) number
GP	dollar value	line item gross profit value
ITEM	text	line item name
OWNERORMANAGER	text	source transaction (e.g. project) owner or manager
PHASE	text	line item project phase (if applicable)
PRODUCTCATEGORY	text	line item product category
PROFIT	dollar value	line item profit (Revenue minus Cost)
QTR	month-month abbreviated	financial year quarter
QUANTITY	numerical value	line item quantity
REGION	text	client region
SOURCE	Project, Purchase Order, etc	transaction source type
SOURCEDOCNAME	text	source transaction name (e.g. project name)
SOURCEDOCNO	numerical value	source transaction number (e.g. Invoice Number, Credit Note Number)
SOURCESTATUS	Completed, Approved, etc	source transaction status
SUPPLIER	text	supplier name (for supplier-related transactions)
UNITCOST	dollar value	line item unit cost
UNITPRICE	dollar value	line item unit price
REVENUE (sum)	dollar value	line item total revenue (Quantity multiplied Unit Price)
COST (sum)	dollar value	total cost for the line (Quantity x Unit Cost)
YEAR	numerical value	activity date year
MONTH	text	activity date month
CLIENT	text	client name
PROJECT	numerical value + text	project number followed by project name

Client Specific Pricing Pivot Report

This one’s all about special pricing agreements.

If you’ve negotiated specific prices for certain clients (instead of using your default sell price), this report summarises all those pricing lines in one place.

Field	Notes / Expected Values	Description
DESCRIPTION	text	line item description
COSTPRICE (sum)	dollar value	line item cost price from supplier
SELLPRICE (sum)	dollar value	line item sell price
CLIENTSELLPRICE (sum)	dollar value	line item client sell price
MARGIN (sum)	dollar value	line item dollar margin (difference between sell price and cost price)
CLIENTMARGIN (sum)	dollar value	line item dollar margin (difference between client sell price and cost price)
MARGINPERCENT (sum)	percentage	line item percentage margin/mark up (difference between sell price and cost price)
CLIENTMARGINPERCENT (sum)	percentage	line item percentage margin/mark up (difference between client sell price and cost price)
CLIENT	text	client name
SKU	text	line item SKU
NAME	text	line item name
SUPPLIER	text	supplier name

Open Transactions by Client Pivot Report

Think of this as the summary version of open work.

Where the Open Transaction Lines report drills into individual lines, this one gives you a cleaner overview of all open projects and stock sales that started or were issued in your selected period. So when you filter by month, quarter, or year, it’s applying to those relevant dates behind the scenes.

Field	Notes / Expected Values	Description
CLIENTPO	text	client PO number
DATE	DD/MM/YYYY	transaction date (e.g. project)
DAY	numerical value	transaction date day (e.g. project)
DAYOFWEEK	text	transaction date day name (e.g. project)
DESCRIPTION	text	transaction description (e.g. project)
DUEDATE	DD/MM/YYYY	transaction due date (e.g. project)
MONTH	text	transaction date month (e.g. project)
OWNER	text	transaction owner / manager
STARTORISSUEDATE	DD/MM/YYYY	transaction start date or issue date
STATUS	text	transaction current status
VALUE (sum)	dollar value	transaction total value
INVOICED (sum)	dollar value	amount invoiced against transaction
YEAR	numerical value	transaction date year
QTR	month-month abbreviated	financial year quarter
CLIENT	text	transaction client name
BILLINGCLIENT	text	transaction billing client name
TYPE	Project, Stock Sale, etc	transaction type
NUMBER	numerical value	transaction number (e.g. project)

Open Transaction Lines by Client Pivot Report

This report focuses on what’s still open.

It brings together all lines for open transactions that were started or issued within the selected periods.

Important note:

For Projects, date fields apply to the due date
For Stock Sales, date fields apply to expected date (or issued date if none saved)

Field	Notes / Expected Values	Description
CLIENTPO	text	client PO number
DATE	DD/MM/YYYY	transaction date (e.g. project)
DAY	numerical value	transaction date day (e.g. project)
DAYOFWEEK	text	transaction date day name
DESCRIPTION	text	line item description
DUEDATE	DD/MM/YYYY	transaction due date (e.g. project)
MONTH	text	transaction date month (e.g. project)
SOURCEDESCRIPTION	text	transaction description (e.g. project)
STARTORISSUEDATE	DD/MM/YYYY	transaction start date or issue date
STATUS	text	transaction current status
VALUE (sum)	dollar value	line item total value
INVOICED (sum)	dollar value	amount invoice against line item
YEAR	numerical value	transaction date year
QTR	month-month abbreviated	financial year quarter
CLIENT	text	transaction client name
BILLINGCLIENT	text	transaction billing client name
TYPE	text	transaction type
SOURCENUMBER	numerical value	source transaction number (e.g. project)
NAME	text + text / text	line item SKU + name / task name

Client Details Pivot Report

This one is more account-focused.

It pulls in all client details along with their account balance before the selected date.

Field	Notes / Expected Values	Description
ABN	text	client ABN
ADDRESS	text	client address
CONTACTEMAIL	text	client contact email
CONTACTNAME	text	client contact name
CONTACTNOTES	text	client contact notes
CONTACTPHONE1	text	client contact primary phone number
CONTACTPHONE2	text	client contact secondary phone number
CONTACTPOSITION	text	client contact position
CREDITDAYS	numerical value	client credit limit days
CREDITLIMIT	dollar value	client credit limit
CURRENCY	text	client currency
DATE	DD/MM/YYYY	client creation / last edited date
DAY	numerical value	client creation / last edited date day
DAYOFWEEK	text	client creation / last edited date day name
EMAIL	text	client email
FAX	text	client fax
ISACTIVE	text	client active status
MONTH	text	client creation / last edited date month
PHONE	numerical value	client phone number
QTR	month-month abbreviated	client creation / last edited date quarter
RECORDTYPE	text	defines if the report line is for a client or a client contact
WEBSITE	text	client website URL
YEAR	numerical value	client creation / last edited date year
ACCOUNTBALANCE (sum)	dollar value	client outstanding balance (Total Invoiced minus Payments and Credits)
CLIENT	text	client name