877.245.5629
sales@billmax.com
|
BillMax Billing Solutions 877.245.5629 sales@billmax.com |
| Chapter 10. Memorized Reports | ||
|---|---|---|
 |
Serving Customers Using the BillMax Staff Interface | 
|
Table of Contents
In addition to the "Custom SQL" reporting described previously, BillMax offers a mechanism for creating memorized customized reports. These are reports that may be run many times and may prompt for variable data.
In addition to the data that comes from the BillMax tables, memorized reports may also access data stored in the BillMax Reports database. See "BillMax for Accountants" for more detail on the BillMax Reports database.
In order to create a memorized report, click on "Setting" on the Navigation Bar and then then "Create Memorized Reports" button. This displays the following:
Figure 10.1. Create Memorized Report
| (1) |
Click on |
| (2) |
Click on |
| (3) |
Click on |
| (4) |
Click on |
| (5) | The description of the query. |
| (6) | The actual query. |
| (7) | The Permissions set that a Authorized BillMax User's Permissions set must intersect in order to view and run the query generating the report. |
Clicking the "TEST" displays the following:
Figure 10.2. Testing a Memorized Report
| (1) | A drop down allowing the selection of any report to test. |
| (2) | A link back to the page for defining the report. |
| (3) | If checked, the report may be downloaded in a CSV format. |
| (4) |
Click on |
A memorized report may prompt the Authorized BillMax User for data. This is done by using the "$" character.
Figure 10.5. Testing a Memorized Report with Variable Input
| (1) | The input field for the data supplied by the Customer Service Representative. Note that the data supplied must match the type expected. For example, if a date is requested, the Customer Service Representative must supply a date in the form YYYY-MM-DD or the report will be incorrect. |
A memorized report may also be used to access detail data generated by certain financial reports. When these reports are run, the detail data may be optionally stored in the BillMax Reports database. The reports that store data in the BillMax Reports database and their variable reference are:
| Report Type | Memorized Report Variable |
|---|---|
| Sales Report | $accrualsales |
| Deferred Revenue | $accrualsales |
| Invoice Aging | $invoiceaging |
| Aging | $aging |
When one of these financial reports is run with the option selected to create a detail table in the BillMax Reports database, the name of the table is printed at the bottom of the report.
| (1) | The name of the table. The format of the name is reportname_datetime_systemid. |
The data from one of the tables may be used in a memorized report. The table is accessed using the "Memorized Report Variable" listed in the table above.
Figure 10.7. Define Report of Non-Taxable Recurring Sales by Zip Code
| (1) | Example using a stored financial report table. |
Figure 10.8. Testing a Memorized Report with a Financial Table
| (1) | The table name from the BillMax Reports database. |
| (2) | Click on the link to select a table from the BillMax Reports database to use. |
| (3) | Lists the options that were selected when running the report. |
This section lists the type of tables and the associated fields in the BillMax Reports database. These may be used in generating custom reports. Note that these tables contain processed results from the BillMax database and the same values may not exist in the BillMax database.
Tables that are created by running either the Sales report or the Deferred Revenue report begin with the string accrualsales. The following fields are available for each record in the table.
System Identifier for a database row. In most cases automatically generated. Allowable range is 1-2147483647.
The taxable amount of a subscription Sale.
A non-taxable amount of a subscription Sale.
The taxable amount of a onetime Sale.
The non-taxable amount of a onetime Sale.
The taxable amount of a subscription Store Credit.
The non-taxable amount of a subscription Store Credit.
The Void of the taxable amount of a subscription Store Credit.
The Void of the non-taxable amount of a subscription Store Credit.
The taxable amount of a onetime Store Credit.
The non-taxable amount of a onetime Store Credit.
The Void of the taxable amount of a onetime Store Credit.
The Void of the non-taxable amount of a onetime Store Credit.
The amount of tax assessed on Sales and Voids of Sales. This is only valid if the Sales report does not exclude deferred revenue. In other words, tax is not split up amongst months like a Sale may be.
The amount of tax assessed on Store Credits and Voids of Sales. This is only valid if the Sales report does not exclude deferred revenue. In other words, tax is not split up amongst months like a Sale may be.
The Account number (account.number) of the transaction.
The transaction number (payhist.number).
The Service Definition number (servdef.number) if the transaction is for a subscription.
Whether or not the transaction is considered a Wash Transaction.
1 - The transaction is considered a Wash Transaction
0 - The transaction is not considered a Wash Transaction
The charge type (payhist.chargetype, chargetypes list) of the transaction.
Tables that are created by running either the Aging (Accounts Receivable) report string aging. The following fields are available for each record in the table.
System Identifier for a database row. In most cases automatically generated. Allowable range is 1-2147483647.
The Account number (account.number).
The overall balance of the Account.
The part of the balance that is not overdue.
The part of the balance that is overdue 1 to 30 days.
The part of the balance that is overdue 31 to 60 days.
The part of the balance that is overdue 61 to 90 days.
The part of the balance that is overdue 91+ days.
Tables that are created by running either the Aging (Accounts Receivable) report string aging. The following fields are available for each record in the table.
System Identifier for a database row. In most cases automatically generated. Allowable range is 1-2147483647.
The Account number (account.number).
The total amount owed on a Sale, a Deposit Charge, or an underfunded Refund.
The number of days the amount owed is overdue.
The amount of the total that has been paid.
The amount of the total that has been paid in cash.
The amount of the total that has been paid in Store Credit.
If a Deposit Charge, the amount of the Deposit Charge paid.
|
|
 |
|
| WAITING |  |
Accessing a Memorized Report |
