KANA Query Files are text files containing SQL language commands that work in conjunction with reports. The Reports component uses the SQL statements in the Query Files to selectively filter what data gets displayed in the report. Query Files appear in the Reports component as Find Criteria that apply to specific reports.
Query File Format
The query file name consists of the report name followed by the .sql extension. For example, the Action Log report has a query file for finding messages that has the following name: System Performance by Day.sql
Following this file-naming convention is crucial for matching reports to their associated Query Files. The Reports component finds all reports in the Reports directory that end with the RPT extension. When a report is selected, the data from the Query File (with the sql extension) appear as Find Criteria.
The name of the custom merge field as it appears in the SQL statement. The custom merge field should always be surrounded by braces (e.g., {sender_name}).
Not currently supported, but can be used to display default values for the field in the Reports component.
The name of the custom merge field as it appears to the user in the list of attributes in the Custom Field area in Reports.
The Tool Tip description of the field when the mouse pointer is over the field.
Provides control parameters for the display of the item. The only currently supported flag is "required," which requires the report user to supply a value for the field before the report can be run.
The SQL statements that are executed when this field is selected. The SQL statements should always begin with the "{connector}" merge field. The custom field SQL statement is appended to the master SQL statement for the custom query starting at the {where_sql} merge field. The connector merge field is replaced by the value "AND" so that all custom query field statements are joined together using the AND operator. Future versions of Reports will allow the user to select the connector for each field (e.g., "AND," "OR," "NOT").
{where_sql}
merge field for location of all custom field SQL statements (placed at end of the WHERE clause).
{connector}
used to connect custom field SQL statements together (defaults to "AND").
The new VIEW mechanism for field value entry makes it simpler for report users to supply input for custom fields by offering them the complete range of valid input from a dropdown selection box. The data for each selection are populated from the KANA Response database when the report is selected. There are currently 10 available VIEWs, as listed below (note that the names are case- and space-sensitive):
Displays a list of the department names visible to the current report user. The department name resolves to the department's ID when used within the SQL statement.
Displays a list of category names visible to the current user. The result is the category ID of the name selected.
Displays a list of mailbox names visible to the current user. The result is the mailbox/folder ID of the selected mailbox.
Displays a list of all custom field names, and results in the ID of the selected field.
Displays two drop-down menus, which let the user select queue names by department. The first drop-down menu selects the department by name (limited to those that are visible to the user). As each department is selected, the second drop-down list is repopulated with the names of the queues for that department. The result of the selection is the ID of the selected queue.
This also displays a dual set of dropdown menus by department. In this case, the second drop-down list shows the user names for the selected department, and resolves to the user ID.
Displays a box with 3 choices (blank, yes, and no), which are also the results of the selection.
Displays a list of the standard message actions, and resolves to the selected string.
Displays a list of the standard message states, and resolves to the selected string.
Displays a list of the standard message types, and resolves to the selected string.
Learn about creating custom reports.
Is there a list of frequently asked questions?