Report Builder

31min

When you need to answer questions not covered by the standard reports then you can use the Report Builder to make a customer report. Our optional reporting addon gives even more advanced reporting capabilities to your account.
Creating ReportsGo to Reports → New Report to start creating a new report.
Select the data type you want to build your report on. There are over 40 types of data available, like customers, invoices, line items, payments, and more.
Select the visualization type: Table, Chart, or Metric
Select the fields you want to display on your report. The fields available will change depending on the reporting data type that was selected.
Add optional filtering, grouping, and sorting conditions to your report.
You can create more than one section on your report. Each section can use a different reporting data type.
Click the Generate button to create your report. After viewing your generated report you have the ability to save it if you want to reuse it in the future.
New Report in Report Builder
﻿
Report Builder Field Selection
﻿
Multi-Entity ReportsYou can create a multi-entity report by checking the "Multi-Entity" toggle when building your report. This will make data available from all entities which you have reporting access to.
Modifying Standard ReportsYou can modify most standard reports and any custom report using the Report Builder. In order to do this you must generate the report you want to customize and click the Edit button.
Saved ReportsWhen you have a custom report that you want to save you can click the Save button to add it to your Saved Reports list. Once saved you can quickly recall that report.
Scheduled ReportsYou can schedule reports to be generated automatically every week or month and sent to you. Here's how you can schedule a report:
Go to your Saved Reports list. If you have not already you must save the report you want to schedule.
Click on the Schedule button next to the report you want to schedule.
Enter the frequency, day of week/month, and time of day that you want to receive the report.
Click the Schedule button and enter any required report parameters.
The system will send an email with the generated report each week or month.
Receiving scheduled reports does require an Invoiced user account to receive and view the report.
Available FunctionsThe report builder supports the following functions.
ABSReturn the absolute value of the argument.
Arguments: num
AGECalculates the age in days of a date value.
Arguments: date, [as_of_date]
AGE_RANGEReturns the return value if the age of a date is within a range of days.
Arguments: date, return_value, lower_bound, upper_bound, [as_of_date]
AVGReturn the average value of the argument. This function performs an aggregation.
Arguments: num
CASESpecify one or more comparison values where if the value matches a comparison value then the corresponding result is returned. You can also specify a result if the value does not match any of the comparison values.
Arguments: value, compare_value, result, [compare_value, result …], [else_result]
CEILReturn the smallest integer value not less than the argument.
Arguments: num
COUNTReturn a count of the number of rows returned. This function performs an aggregation.
Arguments: [field]
COUNT_DISTINCTReturn the count of a number of different values. This function performs an aggregation.
Arguments: field
DATE_ADDAdd time values (intervals) to a date value.
Arguments: date, interval, unit
DATE_FORMATFormat date as specified. The table below shows the available specifiers.
Arguments: date, format
Specifier
Description
%a
Abbreviated weekday name (Sun..Sat)
%b
Abbreviated month name (Jan..Dec)
%c
Month, numeric (0..12)
%D
Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d
Day of the month, numeric (00..31)
%e
Day of the month, numeric (0..31)
%H
Hour (00..23)
%h
Hour (01..12)
%I
Hour (01..12)
%i
Minutes, numeric (00..59)
%j
Day of year (001..366)
%k
Hour (0..23)
%l
Hour (1..12)
%M
Month name (January..December)
%m
Month, numeric (00..12)
%p
AM or PM
%r
Time, 12-hour (hh:mm:ss followed by AM or PM)
%S
Seconds (00..59)
%s
Seconds (00..59)
%T
Time, 24-hour (hh:mm:ss)
%U
Week (00..53), where Sunday is the first day of the week
%u
Week (00..53), where Monday is the first day of the week
%V
Week (01..53), where Sunday is the first day of the week
%v
Week (01..53), where Monday is the first day of the week
%W
Weekday name (Sunday..Saturday)
%w
Day of the week (0=Sunday..6=Saturday)
%X
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y
Year, numeric, four digits
%y
Year, numeric (two digits)
%%
A literal % character
DATE_SUBSubtract a time value (interval) from a date.
Arguments: date, interval, unit
DAYReturn the date formatted as YYYY-MM-DD.
Arguments: date
FIRST_VALUEValue of argument from first row of window frame.
Arguments: column, parent_reference ,reference, order_by
FLOORReturn the largest integer value not greater than the argument.
Arguments: num
FROM_UNIXTIMEFormat Unix timestamp as a date.
Arguments: date
GREATESTReturn the largest argument.
Arguments: num1, num2, ...
IFReturns the first result if the value is true, or else returns the second result if the value is false.
Arguments: value, result_if_true, result_if_false
IFNULLReturns the alternate value if the value is null.
Arguments: column, alt_value
LAST_VALUEValue of argument from last row of window frame.
Arguments: column, parent_reference ,reference, order_by
LEASTReturn the smallest argument.
Arguments: num1, num2, ...
LOWERReturn the argument in lowercase.
Arguments: str
MAXReturn the maximum value. This function performs an aggregation.
Arguments: num
MINReturn the minimum value. This function performs an aggregation.
Arguments: num
MONTHReturn the date formatted as YYYY-MM.
Arguments: date
QUARTERReturn the date formatted as YYYYQX.
Arguments: date
ROUNDRound the argument.
Arguments: num, [num_decimals]
SUMReturn the sum. This function performs an aggregation.
Arguments: num
UNIX_TIMESTAMPReturn a Unix timestamp.
Arguments: date
UPPERConvert to uppercase.
Arguments: str
WEEKReturn the date formatted as YYYY-WW.
Arguments: date
YEARReturn the date formatted as YYYY.
Arguments: date
LimitsThe reporting addon has these limits in place to preserve system resources. These limits are in addition to the standard reporting limits.
Description
Limit
Minimum number of sections on a report
1
Maximum number of sections on a report
10
Minimum number of columns
1
Maximum number of columns
25
Maximum number of joins
10
Maximum number of filter conditions
10
Maximum number of groupings
2
Maximum number of sort conditions
3
Maximum number of saved reports
100
Maximum number of scheduled reports
50
﻿

Did this page help you?

Yes

Standard Reports

Accounting Sync

Docs powered by archbee

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week
%v	Week (01..53), where Monday is the first day of the week
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character

Description	Limit
Minimum number of sections on a report	1
Maximum number of sections on a report	10
Minimum number of columns	1
Maximum number of columns	25
Maximum number of joins	10
Maximum number of filter conditions	10
Maximum number of groupings	2
Maximum number of sort conditions	3
Maximum number of saved reports	100
Maximum number of scheduled reports	50