All core HostBill reports are in fact SQL queries on HostBill database. After executing query Report module take care of displaying results in one of admin-defined output: HTML, PDF, CSV, JSON, SQL or Plain Text
If you or your staff members are familiar with SQL you can easily customize existing HostBill reports or build your own from scratch.
Building custom reports
To create custom report visit Extras->Statistics & Reports->Reports section in your HostBill adminarea. You can then either create report, or choose to customize current one.
Best practice is to choose current report that use data similar to what you're trying to achieve, and use "Customize" button next to it, allowing you to modify SQL query for this report.
Getting columns appear in Available columns
To have your columns appear in Available columns, make sure to add alias to each column using SQL keyword AS like
SELECT id AS `Account ID` FROM hb_accounts
Columns without aliases will not have option to export
Query editor with SQL hints
When editing SQL query using Reports section you'll be using editor with syntax highlighting and hints. Second feature is worth describing:
Listing database tables
In editor use CTRL+Space combination - you'll be presented with list of all tables in your HostBill database - this way you wont be required to memorize all of them!
Listing table fields
After entering table name and "." dot symbol in SQL you're refering to table field - use CTRL+Space to see all available fields for this table
HostBill SQL functions
Adding to regular MySQL functions HostBill provides two custom functions you can call from your SQL queries:
- hb_currency(amount,currency_id,rate) - this function will format amount field to display currency prefix/suffix as configured. currency_id and rate are optional - if those are missing HostBill will use its main currency, otherwise it will display amount field with currency_id (see hb_currencies table) and rate conversion rate
- hb_date(date_field) - this function will return date field formatted with current HostBill date display settings
SELECT hb_currency(total) AS `invoice total`, hb_date(date) AS `invoice date` FROM hb_invoices
Please note - those functions work from reports scope only - calling them on DB directly will not work.