Report Builder
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.
- Go 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.
You 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.
You 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.
When 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.
You 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.
The report builder supports the following functions.
Return the absolute value of the argument.
Arguments: num
Calculates the age in days of a date value.
Arguments: date, [as_of_date]
Returns 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]
Return the average value of the argument. This function performs an aggregation.
Arguments: num
Specify 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]
Return the smallest integer value not less than the argument.
Arguments: num
Return a count of the number of rows returned. This function performs an aggregation.
Arguments: [field]
Return the count of a number of different values. This function performs an aggregation.
Arguments: field
Add time values (intervals) to a date value.
Arguments: date, interval, unit
Format 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 |
Subtract a time value (interval) from a date.
Arguments: date, interval, unit
Return the date formatted as YYYY-MM-DD.
Arguments: date
Value of argument from first row of window frame.
Arguments: column, parent_reference ,reference, order_by
Return the largest integer value not greater than the argument.
Arguments: num
Format Unix timestamp as a date.
Arguments: date
Return the largest argument.
Arguments: num1, num2, ...
Returns 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
Returns the alternate value if the value is null.
Arguments: column, alt_value
Value of argument from last row of window frame.
Arguments: column, parent_reference ,reference, order_by
Return the smallest argument.
Arguments: num1, num2, ...
Return the argument in lowercase.
Arguments: str
Return the maximum value. This function performs an aggregation.
Arguments: num
Return the minimum value. This function performs an aggregation.
Arguments: num
Return the date formatted as YYYY-MM.
Arguments: date
Return the date formatted as YYYYQX.
Arguments: date
Round the argument.
Arguments: num, [num_decimals]
Return the sum. This function performs an aggregation.
Arguments: num
Return a Unix timestamp.
Arguments: date
Convert to uppercase.
Arguments: str
Return the date formatted as YYYY-WW.
Arguments: date
Return the date formatted as YYYY.
Arguments: date
The 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 |