Creating New Query Reports

  1. Click on the Reports tab. This opens the All Reports page.

  2. Click New Query Report button. This opens the Query Editor page.

  3. Select the Table Schema from the combo box say,  requests, timespent and so on. Click Get to view the table schema for the selected option.

  4. Specify the title of the report in the Report Title field. This is a mandatory field.

  5. Specify the query to be executed for getting reports in the Query field. This is a mandatory field.

  6. The logs will display all error messages on providing any wrong query.

  7. Click Run Report to run query report.

Tips

  1. Date Formulae: DATE_FORMAT (FROM_UNIXTIME (COLUMN_NAME/1000),'%d-%m-%Y %k: %i') 'Column Alias'.

  2. Minutes Formulae: ROUND ((((COLUMN_NAME/1000)/60)) % 60) 'Minutes'.

  3. Hours Formulae: ROUND (((COLUMN_NAME/1000)/3600)) 'Hours'.

  4. Compare Date: COLUMN_NAME >= (UNIX_TIMESTAMP (DATE ('2006-07-24 00:00:00')) * 1000).

  5. Convert Memory in GB: ((((MEMORY_COLUMN)/1024)/1024)/1024)

  6. Default Value For Null Data: COALESCE (COLUMN_NAME, 'Unassigned')

  7. Group by: Query statement will be ends with order by <column_index>

Additional Field tables

  1. Request Additional Fields - WorkOrder_Fields

  2. Requester Additional Fields - Requester_Fields

  3. Technician Additional Fields - Technician_Fields

  4. Asset Additional Fields - Asset_Fields

  5. Workstation Additional Fields - Workstation_Fields

  6. Problem Additional Fields - Problem_Fields

  7. Change Additional Fields - Change_Fields

Query Report via API

While executing query reports via API, users have the option to display columns with null values in the report.

 

To do this, update the paramvalue in the query given below:

UPDATE ReportModuleConfiguration SET PARAMVALUE = 'true' WHERE PARAMETER LIKE 'Display_Null_Columns' AND CATEGORY LIKE 'Query_Reports_API'; 

 

True - Null value columns are displayed in the report.

 

 

False - Null value columns are not displayed in the report.