About Reports

Reports can contain information on just about anything. Blesta includes a few helpful reports by default. You can access these under [Billing] > [Reports].

Default Reports

NameDescriptionFormatsSince Version
Aging InvoicesUnpaid invoices over the last 30, 60, and 90+ days.CSV, JSON
Client Data PortabilityAll non-sensitive information pertaining to a client.JSON4.3
Custom ReportOne of the various custom reports added to the system.CSV, JSON
Invoice CreationInvoices based on the date they were generated.CSV, JSON
Tax LiabilityTaxes collected.CSV, JSON
Transactions AppliedPayments applied to invoices.CSV, JSON
Transactions ReceivedPayments received.CSV, JSON
Package RevenueCalculates revenue from a currency for each package by totaling the paid invoice line items associated with the package.CSV, JSON4.9

Custom Reports

Blesta includes some really helpful default reports, but some organizations require detailed custom reports. Custom reports can be created under [Billing] > [Reports] > [Customize], and allow you to specify any number of fields you wish to collect from a user that generates the custom report.

Adding a Custom Report

To add a custom report, click the Add Report button under [Billing] > [Reports] > [Customize].

OptionDescription
NameThe name of your custom report.
Query

The SQL query to be executed when your report is requested.

Fields can be used within a query using the colon syntax. For example:

SELECT `id` FROM `users` WHERE `username`=:username;

Where a field exists with the name username.

Fields
OptionDescription
LabelThe label shown for this field when generating this report.
NameThe name of the field for use in query substitution.
Type

The type of field.

OptionDescription
TextA text input field.
SelectA select menu, with the provided options.
DateA date picker.
Required

Whether or not the field is required.

OptionsDescription
NoThe field is not required.
YesThe field is required to have something specified. For select field types, one of the specified options must be selected.
Custom RegexThe value for the field must pass this regular expression.

Editing a Custom Report

To edit a custom report, click the Edit link next to the report you wish to update under [Billing] > [Reports] > [Customize].

Deleting a Custom Report

To delete a custom report, click the Delete link next to the report you wish to remove under [Billing] > [Reports] > [Customize].

Some Example Custom Reports

Report NameReport Information
Client Credits

This will generate a report containing all clients that have credits in the selected currency, and include the amount of the credit and their client ID.

QueryFields
SELECT SUM(temp.credit) AS credit,  temp.client_id
FROM (
    SELECT SUM(IFNULL(transaction_applied.amount, 0)) AS applied_amount,
        transactions.amount AS approved_transaction_amount,
        (transactions.amount - SUM(IFNULL(transaction_applied.amount, 0))) AS credit,
        transactions.client_id,
        transactions.amount,
        transactions.gateway_id,
        transactions.transaction_id,
        transactions.status,
        transactions.date_added
    FROM transactions 
    LEFT JOIN transaction_applied
        ON transaction_applied.transaction_id = transactions.id
    WHERE transactions.status = 'approved' AND transactions.currency = :currency
    GROUP BY transactions.id
    HAVING applied_amount < approved_transaction_amount
) as temp
GROUP BY temp.client_id
ORDER BY credit DESC
LabelNameTypeRequired
CurrencycurrencyTextYes

Example Output

"credit","client_id"
"155.0000","2"

  • No labels