2022-4Q: [REPT] SIMPLer Reporting Tool / Creating custom reports

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

  2. Go to Tools > Create Report 

    3. How to create a report:

a. Give the Report a name e.g. 'Revenue Per Site - Residential (Last Month)'

b. create a direct query to retrieve data

c. give column headers

d. It is now also possible to configure xlsx column type, where as before all columns were 'text' type, by default.

For each report, when defining the fields you have the option to select one of the following:

• text - default option, this is what it was all the time

• number - this will make sure that the field is categorized as a number (eg. Customer ID, Site ID etc)

• amount - this will make sure that the field is categorized as an amount (with decimals and thousand separator)

see Fig 3 as an example of a direct 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. 

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:

Format:

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

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

Updated Date:  22 Jan 2025

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.