...
| Option | Description |
|---|
| Name | The name of your custom report. |
| Query | The SQL query to be executed when your report is requested. | Info |
|---|
Fields can be used within a query using the colon syntax. For example: | Code Block |
|---|
| SELECT `id` FROM `users` WHERE `username`=:username; |
Where a field exists with the name username. |
|
| Fields |
| Option | Description |
|---|
| Label | The label shown for this field when generating this report. | | Name | The name of the field for use in query substitution. | | Type | The type of field. | Option | Description |
|---|
| Text | A text input field. | | Select | A select menu, with the provided options. | | Date | A date picker. |
| | Required | Whether or not the field is required. | Options | Description |
|---|
| No | The field is not required. | | Yes | The field is required to have something specified. For select field types, one of the specified options must be selected. | | Custom Regex | The 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].
...
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 Name | Report 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. | Query | Fields |
|---|
| Code Block |
|---|
| 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 |
|
| Label | Name | Type | Required |
|---|
| Currency | currency | Text | Yes |
|
Example Output "credit","client_id"
"155.0000","2"
|