...
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"
|