2022-4Q: [REPT] SIMPLer Reporting Tool
A new feature is added to enhance the reporting functionality. It allows for an operator to create reports arranged in a desired order and which contain only desired data. This tool can create any report with data that is derived directly from the database.
It is a quite powerful report because whenever an additional column should be added it can be done in an easy way without affecting other operators.
Creating the report does require some knowledge of database structure (and sql), Azotel support can assist here.
Operator can contact support@azotel.com for a copy of the database schema, which contains the database tables/ column names.
To make use of this feature:
1. User Right. To make user of this feature one need to have the user right in order to create the report. It is disabled by default for everybody and should only be given to a minimum set of people. See Fig.1
Fig 1: User Right
2. Go to Tools > Create Report
Fig 2: menu Tools > Create Report
3. How to create a report:
a. Give the Report a name e.g. Marketing Report
b. create a direct query to retrieve data
c. give column headers
see Fig 3 as an example of a direct query
Fig 3: Example of a direct Report Query
4. Once that is done the created report shows up under Tools > Reports and can be set as a cronjob. The reports will show the results based on the query created.
Fig 4: Customised Report under menu Tools > Reports
Using custom INPUT Settings
It's possible to create custom inputs when building a report, this will allow you to insert values from inputs into your query
Input Types:
datescrolling_listtextareatextfieldFormat:
The different field options are separated using 3 colon :::, if you do not use an option then you leave it empty (example of not using an option ::::::).
type:::name:::label:::auto_option:::query:::values:::default:::multiselect
type: The Input type you are using (available types: date, scrolling_list, textarea, textfield)name: The name of the field, this is also used as a parameter in your query like %name%label: This is the label of your input that will display on the UI.auto_option: ?query: You can insert any sql query and it will populate the input field with the result, useful for scrolling lists mainly.default: Default value used on the input box, for textinput you can use a string or for lists the positional number.multiselect: set this to 1 if you would like to use multi selection with scrolling_list, this wont do nothing if your type is not scrolling_list
Note: If you want to insert two inputs you will need to separate the options with 3 /// example below of two date fields being used.
date:::DATE_to:::Date To///date:::DATE_from:::Date from
Inserting the value from the INPUT into your query:
To insert the result of the input into your query you can add in the option name enclosed with two percent signs: %name%
Some option examples:
date INPUT
date:::DATE_to:::Date To
(value to use in your query %DATE_to% )
Scrolling list
scrolling_list:::LIST1:::My List:::::::::option1,option2,option3:::
(value to use in your query %LIST1% )
Scrolling list with data result from a query (subscription custom fields)
scrolling_list:::LIST1:::My List::::::SELECT id,label FROM custom_fields WHERE availability='subscriptions':::option1,option2,option3:::
(value to use in your query %LIST1% )
Scrolling list with data result from a query (customer status) using multiselect
scrolling_list:::LIST1:::Customer Status::::::SELECT val::text,val::text FROM enum_customerstatus order by val:::option1,option2,option3::::::1:::
(value to use in your query %LIST1% )
Text input
textfield:::field:::my field
(value to use in your query %field% )
Text input with a default value of hello world
textfield:::field:::my field::::::::::::hello world
(value to use in your query %field% )
Text area input
textarea:::area1:::my text area
(value to use in your query %area1% )
Example Reports
Database Query:
select customerstatus[1],count(*) from customers where init_date>=date(case when '%DATE_FROM%'='' then '1970-09-01' else '%DATE_FROM%' end) AND init_date<=date(case when '%DATE_TO%'='' then '2100-09-01' else '%DATE_TO%' end) group by customerstatus[1] union select 'Total',count(*) from customers where init_date>=date(case when '%DATE_FROM%'='' then '1970-09-01' else '%DATE_FROM%' end) AND init_date<=date(case when '%DATE_TO%'='' then '2100-09-01' else '%DATE_TO%' end) order by 2 asc
Report Column Headers:
Month,Customer Status, Count
Settings:
date:::DATE_FROM:::Date From///date:::DATE_TO:::Date To
Database Query:
SELECT * FROM maintenance RIGHT JOIN customers ON maintenance.customerid = customers.customerid WHERE customers.customerstatus [1] LIKE 'current' AND maintenance.status = 'open' AND maintenance.type IN ('installation', 'Installation-Type')
Report Column Headers:
Ticket ID,Customer ID
Database Query:
SELECT customerid,name,nickname,type,customer_tracking,address,installaddress,installationdate FROM customers WHERE customers.customerstatus[1] = 'current' AND customer_tracking LIKE '%BEA%' ORDER BY type
Report Column Headers:
customerid,name,nickname,type,customer_tracking,address,installaddress,installdate
Database Query:
SELECT subscription.customerid,customer_custom_fields.VALUE,subscription.price,subscription.description,customers.customerstatus[1] FROM subscription JOIN customer_custom_fields ON customer_custom_fields.customerid = subscription.customerid JOIN customers ON subscription.customerid = customers.customerid WHERE customer_custom_fields.field_id = '10' AND subscription.description LIKE '%FTTH%' AND subscription.description NOT LIKE '%Credit%' ORDER BY description
Report Column Headers:
customerid,value,price,description,customerstatus
Database Query:
SELECT name,email,customerstatus[1],installaddress[1],installaddress[2],installaddress[3],installaddress[4],installaddress[5],installation_area FROM customers;
Report Column Headers:
Name,Email,Status,Install Street 1, Install Street 2, Install Town, Install County,Install Post Code,Installation Area
Database Query:
SELECT issuenumber,customerid,type,resolution,status,statuscomment,m_type, subtype, datereported, priority, ownership FROM maintenance Where ownership is not null and type like 'Trouble';
Report Column Headers:
Issue Num, Cust ID, Type, Resolution, Status, Status Comment, Maintenance Type, SubType, Date Reported, Priority, Owner
Published Date: 11 October 2022
Engineer: PD
Contact Azotel Support:
Need more help? Save time by creating a maintenance ticket to Azotel through your instance or email support@azotel.com.