Creating New Query Reports

Click on the Reports tab. This opens the All Reports page.
Only Administrator can see New Query Report button.
Click New Query Report button. This opens the Query Editor page.
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.
Specify the title of the report in the Report Title field. This is a mandatory field.
Specify the query to be executed for getting reports in the Query field. This is a mandatory field.
The logs will display all error messages on providing any wrong query.
Click Run Report to run query report.

Tips

Date Formulae: DATE_FORMAT (FROM_UNIXTIME (COLUMN_NAME/1000),'%d-%m-%Y %k: %i') 'Column Alias'.
Minutes Formulae: ROUND ((((COLUMN_NAME/1000)/60)) % 60) 'Minutes'.
Hours Formulae: ROUND (((COLUMN_NAME/1000)/3600)) 'Hours'.
Compare Date: COLUMN_NAME >= (UNIX_TIMESTAMP (DATE ('2006-07-24 00:00:00')) * 1000).
Convert Memory in GB: ((((MEMORY_COLUMN)/1024)/1024)/1024)
Default Value For Null Data: COALESCE (COLUMN_NAME, 'Unassigned')
Group by: Query statement will be ends with order by <column_index>
$LoggedInUser : The $LoggedInUser variable used in the query will be replaced with the display name of the currently logged-in technician during query execution. Example: Select * from aaauser where first_name=$LoggedInUser;
$Account : You can use $Account as a template variable in your query. This will be replaced with the current Account ID configured in the Account Combo Box during query execution. Example: Select * from accountdefinition where org_id=$Account;

Additional Field tables