Report Generator 3.X For WHMCS
Contents |
About Report Generator For WHMCS
Report Generator For WHMCS allows you to generate and manage any kind of reports using inbuilt drag & drop WYSIWYG editor. Module lets you to adjust reports to your needs through using various filters, conditions and even your own PHP code. You can display ready reports in tables, graphs or charts. |
- Admin Area Features:
✔ Drag & Drop WYSIWYG Report Builder |
✔ Dynamic Reports Editing |
✔ Report Types (Data Table, Pie Chart, Area Chart, Column Chart) |
✔ Predefined Reports |
✔ User Created Reports |
✔ Multi-Sections Of Dynamically Generated Reports |
✔ Dynamic Filters |
✔ Predefined SQL Expressions At The Report Builder |
✔ Multiple Joins At The Report Builder |
✔ Two View Types Of Report Pages (Simple And Widget Alike) |
✔ Print Reports With A Print Preview |
✔ Schedule Automatic Reports Sending To Chosen Staff Members With Specified Conditions |
✔ Export To CSV, XML and XML Structure |
✔ Export To CSV From Report View - Allows You To Set Up Undefault Value Of Filter |
✔ Import From The XML Structure |
✔ Generate PDF - Both Manually And By Cron Job |
✔ Translation Of Database Columns And Tables From The Settings Tab |
✔ Enable Unicode Font For Generated PDF |
- General Info:
✔ Multi-Language Support |
✔ Supports WHMCS V5 and Later |
✔ User-Friendly Interface |
✔ Migration Tool |
- User Created Reports:
✔ 1. Section 1: Orders Of Services In Selected Time Period (Column Chart) |
✔ 2. Section 1: Orders Of Services In Time (Column Chart) |
✔ 3. Section 1: Orders Over The Weekdays (Pie Chart) |
✔ 4. Section 1: Most Valuable Clients (Data Table) |
✔ 5. Section 1: User Accounts Created In Selected Time Period (Data Table) |
Section 2: Amount Of User Accounts Created In Selected Time Period (Column Chart) |
✔ 6. Section 1: Tickets Replied By Admin (Data Table) |
Section 2: Tickets Replied By Admin (Data Table) |
Section 3: Tickets Replied By Admin (Pie Chart) |
✔ 7. Section 1: Customers Per Country (Pie Chart) |
- Predefined Reports:
✔ 1. Section 1: Open Tickets For The Time Period (Data Table) |
Section 2: New Invoices For The Time Period (Data Table) |
Section 3: Invoice Reminders For The Time Period (Data Table) |
Section 4: Hosting Renewals (Data Table) |
Section 5: Suspended Services For The Time Period (Data Table) |
Section 6: Domain Renewals For The Time Period (Data Table) |
✔ 2. Section 1: Closed/Answered Status Tickets By The Staff Member (Data Table) |
Section 2: Tickets Stats For The Staff Over Time (Area Chart) |
Section 3: Closed To-Do List Tasks Owned By The Staff Member (Data Table) |
Section 4: To-Do List Tasks Owned By The Staff Member Grouped By Status (Pie Chart) |
Section 5: Orders Summary (Area Chart) |
✔ 3. Section 1: Opened Tickets Stats For This Staff Over Time (Area Chart) |
Section 2: To-Do List Tasks Owned By The Staff Member Grouped By Status (Pie Chart) |
Section 3: Closed To-Do List Tasks Owned By The Staff Member (Data Table) |
Installation
This tutorial will show you how to successfully install and configure Report Generator For WHMCS. We will guide you step by step through the whole installation and configuration process. |
1. Log in to your client area and download Report Generator For WHMCS. |
2. Upload and extract the module into the main WHMCS directory. Files in your WHMCS directory should look like this. |
3. Remove all files from 'templates_c' folder. |
4. When you install Report Generator for the first time you have to rename 'license_RENAME.php' file. File is located in 'modules/addons/reportgenerator/license_RENAME.php' . Rename it from 'license_RENAME.php' to 'license.php' . |
5. In order to configure your license key, you have to edit a previously renamed 'license.php' file.' Enter your license key between quotation marks as presented on the following screen. You can find your license key in your client area → 'My Products' . |
6. Now you have to create 3 folders in a 'storage' folder and set chmod '755' or '777' to them. 'storage' folder is located in 'your_whmcs/modules/addons/reportgenerator/' . |
7. Now you have to activate the module in your WHMCS system. Log in to your WHMCS admin area. Go to 'Setup' → 'Addon Modules' . Afterwards, find 'Report Generator' and press 'Activate' button. |
8. In the next step you need to permit access to this module. To do so, click on 'Configure' button, tick 'Full Administrator' and press 'Save Changes' . |
9. The last step is setting up cron jobs. You can find them at your module 'Addons' → 'Report Generator' → 'Cron Jobs' → 'Cron Setup' . |
10. You have just successfully installed Report Generator! You can access the module at 'Addons' → 'Report Generator' . |
Configuration and Management
Report Generator For WHMCS allows your to create custom reports using drag & drop WYSIWYG builder. Module also offers additional useful features like predefined reports, import/export of reports, generating reports in PDF, ability to send then to admins and much more. |
Reports List
At the 'Reports Lists' tab you can view all predefined reports as well as those created by you. Here you can manage your reports by viewing (1), editing (2) and removing them (8). |
For predefined reports you can view report, generate PDF file and export report to CSV and XML. |
To start creation of report press 'Create New Report' . More information about creating new reports will be presented in the Creating Report section. |
Report View
In the report view you can see all tables and charts generated by the report. You can display report in a simple or widget format. To switch between them press one of the buttons marked on the screen below. |
PDF Report View
You can also view a PDF version of report which can be sent to your staff memebers or saved to PDF file on your disk. |
Creating Report
In this section we will show you how to prepare a few examplary reports. If you wish to create your own report, we strongly recommend creating reports shown below firstly in order to get some practice. |
First Report - User Accounts Created
In the beginning there is only one section in your report. Each section generates one chart on the final report. In order to create additional charts on your report you need to add a new section. |
Now, select desired chart type and table you want to start with. As you can see, default columns have been loaded. For purposes of this report we will remove them through pressing 'Remove All The Columns' . |
Adding columns to report is quite easy owing to an autocomplete box. Type table name and list of all available columns will show up. Now, select desired column. |
As you may have noticed earlier, each change made affects report preview displayed below. In some situations preview may be replaced with error, which will contain information about lacking or miss-configured settings of report. |
To set up time period for report, go to 'Conditions' tab and mark 'Filter results by date' . |
Afterwards, select 'Clients Table : datecreated' and 'Monthly' from marked dropdown menus. Now, report will be generated using only accounts created during the last 30 days. |
You can set up ordering records by specific column. To do that, go to 'Groups & Limits' tab. We will order records by client last name. Choose 'Clients Table : lastname' and 'Ascending' as shown on the following screen. |
Let's view that report through pressing 'View' button. As you can see, column names are names of table columns. |
Second Report - Orders Per User
This report will be counting orders made by clients this year. Due to that, we will need to use group by command to count those products. As previously, start by defining report name and description. Additionally, choose report type and starting table. |
Join orders table to clients table. We will need it to display columns from both tables here. Press 'Join Another Table' to proceed. |
It is time to think about how we want to connect these two tables. We want to display a client even if he/she has no orders, therefore from 'Please Specify A Join Type' we will choose: |
Now select two tables to join. For purposes of this report, these will be tables 'Clients' and 'Orders' . It is time to decide which columns will be used to join chosen columns. |
We want to display clients details in one column and quantity of orders in another one. The first column will be displayed by: concat(tblclients.id, '# ', tblclients.firstname, ' ', tblclients.lastname) as Client Concat will join provided columns while 'Client' is alias for this column. Alias will be used for generated report. COUNT(*) as Orders Count along with grouping by allow us to count records for each client. Orders Table : ID as Any_Order_Exist This column was added to handle clients who do not have any orders. |
We want to group records by clients, therefore fill 'Group This Section By' with 'Clients Table : ID' which is a unique key at the client table. Additionally, let's set up sorting clients by quantity of orders, we can use column alias for this. |
At 'Your Own Code' tab you can modify received output. Code has to be placed in delimiters as shown below. <?php // your custom code here ?> The '$result' variable is passed by reference and contains just iterated element of the result array. <?php foreach($result as $element) // iterate over all records storing current row in $element variable { if($element->Any_Order_Exist=="") // check if Any_Order_Exist is empty { $element->Orders=0; //assign 0 to Orders if Any_Order_Exist is empty $element->Any_Order_Exist='No'; //assign 'No' to Any_Order_Exist if it is empty } else // if previous if was false, commands stored here will be ordered { $element->Any_Order_Exist='Yes'; //assign 'Yes' to Any_Order_Exist } } ?> |
Your code is ready, press 'Generate Report' to save it. |
Third Report - Unpaid Invoices
In the third report we will show you how to prepare report displaying clients with the most unpaid invoices. We will also add second section displaying unpaid invoices which can be filtered by client first and last name. |
Now, join invoice table using 'Clients Table : ID' and 'Invoices Table : userid' and set it up to return tables only if there is a match in the invoice table. |
Add 'COUNT(*) as Sum' column and proceed to the 'Conditions' tab. |
Press 'Create New Filter' button to define new condition for your report. |
Select column you want to define condition for, select filter operand, define default value of filter and filter friendly name. Conditions can be changed at the report view, they will be displayed under friendly name defined here. |
Fill 'concat(tblclients.id, tblclients.lastname, tblclients.firstname)' into 'Group This Section By' field. Owing to previously introduced concat you can both make sure you will be counting invoices properly and display string generated by concat under summary column in table. |
In this section we want to display list of all unpaid invoices of selected client. Select date table and start of client table. |
Now, add desired columns. As you can see on the following screen, we have used concat to display clients details in the first column. Proceed to condition tab and press 'Add New Condition' button. |
There are two conditions to add, their default values are left blank so default report will omit these conditions. Nonetheless, you will be able to easily filter for desired client in report view. |
Proceed to 'Groups & Limits' tab and set up ordering by ID. You can also increase limits of record displayed on the report. |
Let's proceed to 'Your Own Code' section to replace 'No Company Assigned' with blank field. Copy and paste code placed below: <?php foreach($result as $element) if($element->companyname=='No Company Assigned') { $element->companyname=''; } ?> Your report is ready, press 'Generate Report' to finish. |
Modifying Report
All reports can be easily altered. Press 'Edit' next to report that you wish to modify. |
Edit screen is the same as generate screen. Therefore you are allowed to modify and even entirely change report according to your needs. We will add filtering results by clients' nationality to this report. |
Proceed to 'Conditions' tab and press 'Add New Filter' . Filter set up shown on the following screen will filter report output depending on client's country. |
Email Configuration
At 'Email Configuration' you can set up emails with PDF reports sent to administrators. Start by selecting admin or admins to which email with report should be sent. |
As you can see on the following screen, all relations are listed at the bottom of 'Email Configuration' page. Each of those relations can be removed at any time through pressing 'Delete' button. |
Import
To import report from the XML file into your Report Generator, go to 'Import' tab. Press 'Browse' button and select your report XML file. |
Imported report should be renamed afterwards. Press 'Update Report' as soon as you finish. |
Now you can go to 'Reports List' to see that your report has been successfully imported and it is ready to use. |
Default Reports Settings
At 'Settings' → 'Default Reports Settings' tab you are allowed to define a few behaviors of our module. 1. Protects generating reports from data lost during refreshing. |
Tables And Columns Definitions
Our module allows you to set up custom table and column names. It is very simple. Go to 'Report Generator' → 'Settings' → 'Advanced Builder Settings' . As you can see, there are three text fields. |
Migration
Due to changes in the module and its structure, upgrading it from version 1.0 requires migration tool. It can be accessed at 'Migration' tab of our module and allows you to migrate reports from the previous version of Report Generator For WHMCS. |
Afterwards, press 'Update Report' button. Your report is now available at 'Reports List' → 'User Generated Reports' . |
Tips
1. In order to work properly, module requires PHP with a PDO support. |
2. There is workaround which enables you to copy report. Simply export report to XML structure and import report from a received file. |
Common Problems
1. When you have problems with connection, check whether your SELinux or firewall does not block ports. |
2. If you encounter any troubles with our module it is possible that you have not installed PDO. |