Reports

The Reports section provides you some useful and ready to use reports, with the possibility to customize them according to your needs. 

You can also create New reports, under the Custom reports folder (see Custom Reports), and import the reports you previously exported in JSON format.

The Reports Section is under the Analytics Menu:

XCALLY Motion provides a number of default reports offering key statistics about the performance of your call center.

You can easily use these reports as they are, or copy and modify them according to your needs.

The default reports are organized in folders and sub-folders under the Motion Reports panel:

When you click on a folder, the list of reports available in that folder are displayed in the right panel. In the picture below, you see for example the list of available reports for the Queues where the Calls are Answered:

There are several reports available for Voice (Queues, Calls, Agents, Cally Square, Recordings), Motion Bull (Queue Campaigns, IVR Campaigns), Chat, Email, OpenChannel, SMS, Fax and Agents.

You can perform the following actions for every default report (click on to show the command list):

Copy Report

Copy Report action enables you to duplicate an existing report to another folder.

To copy a report click on Copy Report, specify the name of the report and select the destination folder. The copied report will be available under the selected folder.

Now your report copy is ready to be modified (using the Edit command) and used.

Preview Report

The Preview Report action will display a preview of the report records (only limited) without considering date and time ranges.

Use Run Report action to filter records by date and to see all the report results.

Run Report

Run report action is used to extract the report in CSV, PDF, Excel (XLS), WEB format by specifying the date and time ranges.

The extracted report will be available under Analytics -> Extracted Reports where you can Download or Delete the file.

Please note that you can also choose the Web format. In this case you can immediately see the resulting rows of the extracted report in a web pop-up:

Export Report JSON

Export Report JSON command is used to download the structure of a report in JSON format.

This can be helpful to replicate the report in another XCALLY Motion server by simply importing the JSON without the need to rebuild the entire report structure.

Custom Reports

In addition to the default reports, XCALLY Motion allows you to create and manage custom reports that are specific to your call center needs. 

Click in the Custom Reports panel and click Create Folder to create a folder (you can then Rename or Remove the folder).

Follow the same procedure on the new folder to create a sub-folder.

In the right panel, click on to create a New Report. Enter the Name, select a table you want to query and click on Add Report. 

It is possible to integrate several tables in a report, but it is important to know the tables that you require with their respective index fields. Knowledge of MySQL is important to note.

You can also import a report in JSON format by clicking on

After you create your custom report, clicking on you can perform the following actions:

Edit Report

After creating or importing a report, you can easily design the query for retrieving data from the database. Click on Edit Report  and you will see the following sections: 

• Settings: to change the report name and description
• Fields:  to specify the fields of the query whose values you want to fetch
• Conditions:  to specify a condition while fetching the data from the table
• Preview: to preview the outcome of the report
• Query: to see the complete query in SQL

Design the query

The fields and conditions sections in the edit view are used for building the query in order to fetch data from the database.

The SELECT statement is used to retrieve data and the basic syntax is as follows:

 SELECT field1,field2,fieldN FROM table_name;

Where field1, field2… are fields whose values you want to fetch. A field can be the table’s column name or a metric.

For example: In the following query, uniqueid and duration are column names in the table report_call, while AVG(duration) is a metric. 

 SELECT uniqueid,duration,AVG(duration) FROM report_call;

Add fields

To add a field in the query, go to fields section and click on and select the type of the field: Select a column, Select an existing metric or Select a custom metric.

Select a column: is used to select a column of the table whose value you want to fetch. For this type of field, you can specify the following parameters:

• Column: column of the table
• Alias: name of column that will be displayed on the report
• Function (sum, count, avg, max, min, group): to apply one of the available functions for processing string or numeric data 
• Format: to change the format of the column
• Group by (True or False): to arrange identical data into groups based on the column
• Order by (No Order, ASC or DESC): is used to sort the data in ascending or descending order, based on the column

Select an existing metric: is used to select a metric predefined in the metrics section. 

• Metric: a predefined metric
• Alias: name of column that will be displayed on the report
• Group by (True or False): to arrange identical data into groups based on the metric
• Order by (No Order, ASC or DESC): is used to sort the data in ascending or descending order, based on the metric

Write a custom metric: is used to to write a custom metric

• Metric: a predefined metric
• Alias: column name that will be displayed on the report
• Function (sum, count, avg, max, min, group): to apply one of the available function for performing processing on string or numeric data. 
• Format: to change the format of the column
• Group by (True or False): to arrange identical data into groups based on the column. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
• Order by (No Order, ASC or DESC): is used to sort the data in ascending or descending order, based on the column
  

Example:  

Adding 3 fields with the following fields configuration:

will result in a 3 column report and the query as shown in the following screens:

Add Conditions

The WHERE clause is used to specify a condition to filter the records and fetch only the necessary data from a table. 

A condition is built using table column, comparison or logical operators like >, <, =, LIKE, NOT and a value.

To add a conditions in the query, go to conditions section and click on to add a single condition or to add a group of conditions. 

The logical operators AND/OR define the operator to be applied between multiple conditions. 

For example, two conditions with an AND operator as shown in the picture will result in the following Where clause:

                WHERE (type = ‘inbound’ AND source = 1001)

It's mandatory to put the value inside single quotes ('value') for a string value.

Using group of conditions as shown in the picture will result in the following Where clause:

                WHERE (source = 1005 AND (type = ‘outbound’ OR type = ‘inbound’))

Join Tables

The Join function allows to build queries joining tables and retrieving data belonging to the sum of them.

You can use this function and join XCALLY Motion DB Tables , if you have enough experience about DB structure and data queries.

The following is the list of tables in the database for the preparation of reports through the report generator:

Table type	Table	Index fields
Staff	report_agent report_member	(id, uniqueid, destuniqueid) (id, membername, intereface)
Contact Manager	cm_contacts  cm_lists  cm_custom_fields cm_companies	(id, CompanyId, ListId, phone) (id, name) (id, ListId) (id, name)
Voice	report_queue report_call report_dial report_call_transfer voice_recordings	(id, uniqueid, calleridnum) (id, UserId, ContactId,uniqueid) (id, uniqueid) (id) (id, UserId, uniqueid)
Motion Bull Dialer	cm_hopper cm_hopper_final cm_hopper_history	(id, ContactId, ListId, VoiceQueueId, ListId, UserId, uniqueid, CampaignID,OwnerId,scheduledat,phone,priority) (id,UserId,VoiceQueueId,ContactId,CampaignID,ListId,calleridnum,uniqueId) (id,UserId,VoiceQueueId,ContactId,CampaignID,ListId,endtime, uniqueId)
Cally Square (IVR)	report_square report_square_details	(id,UserId, uniqueId, project_name, leaveAt) (id)
Chat	report_chat_queue chat_interactions chat_messages chat_offline_messages chat_websites	(id, ChatWebsiteId, ListId, ContactId, ChatInteractionId, userId, ChatQueueId, uniqueId). (id, userId, ChatWebsiteId, ContactId) (id, ChatWebsiteId, ChatInteractioId, userId, ContactId,AttachmentId, body) (id, ContactId, ChatWebsiteId) (id, address, ListId,IntervalId, mandatoryDispositionPauseId)
Email	report_mail_queue mail_interactions mail_messages mail_accounts	(id, MailAccountId, ListId, contactId, MailInteractionId, MailMessageId, UserId, MailQueueId, uniqueid) (id, UserId MailInteractionId, ContactId, subject, to) (id, MailAccountId, MailInteractionId, UserId, MessageId, plainBody, Subject) (id, ListId, TemplateId, mandatoryDispositionPauseId)
SMS	report_SMS_queue sms_messages SMS_Accounts	(id, SmsAccountId, ListId, ContactId, SmsInteractionId, SmsMessageId, UserId, SmsQueueId, uniqueid) (id, SmsAccountId, SmsInteractionId, UserId, ContactId, messageId, body) (id, name, ListId, mandatoryDispositionPauseId)
Open Channel	report_openchannel_queue openchannel_interactions openchannel_messages Openchannel_Accounts	(id, OpenchannelAccountId, ListId, ContactId, OpenchannelInteractionId, OpenchannelMessageId, UserId, OpenchannelQueueId, uniqueid) (id, UserId, OpenchannelAccountId, ContactId) (id, OpenchannelAccountId, OpenchannelInteractionId, UserId, ContactId, AttachmentId, body) (id, name, ListId, mandatoryDispositionPauseId)
Fax	report_fax_queue fax_interactions fax_messages Fax_Accounts	(id, FaxAccountId, ListId, ContactId, FaxInteractionId, FaxmessagesId, UserId, FaxQueueId, Uniqueid) (id, UserId, FaxAccountId, ContactId, Uniqueid) (id, FaxAccountId, FaxInteractionId, ContactId, AttachmentId, body) (id, localstationid, TrunkId, ListId, mandatoryDispositionPauseId)
Jscripty	report_jscripty_answers report_jscripty_questions report_jscripty_sessions	(id, sessionId, PorjectId) (id, sessionId, PorjectId) (id, VoiceQueueId,ContactId,ProjectId)

Click on  to create a New Report. Enter the Name, select a table you want to query (from the list) and click on the button Select the table to join:

Now you must select the DB table field to join:

where:

• Parent Key is the first chosen table field
• Foreign Key is the second chosen table field
• Join type (select from the list)

• JOIN: Returns all rows when there is at least one match in both tables.
• LEFT JOIN: Returns all the rows in the table on the left, and the matching rows in the table on the right.
• RIGHT JOIN: Returns all the rows in the table on the right, and the matching rows in the table on the left.
• OUTER JOIN: Returns all the rows of the two tables, the left and the right. Also called FULL OUTER JOIN.
• CROSS JOIN: Returns all the records of all the tables involved in the join, returns the Cartesian product. It is not widely used.

Save the report and then Edit it in order to add Fields (the system will show all the fields available for each table joined), Conditions and to preview the query results.