Note: This documentation is outdated, for latest, up-to date documentation please visit: https://hostbill.atlassian.net/

Custom Reports

From HostBill
Jump to: navigation, search

Contents

Introduction

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!

Hints 2.png


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

Hints 1.png

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

Example

 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.

Personal tools