This topic explains how to create a routine to export the results of queries to SQL tables in the system database. This information can then be used to create custom charts in the Dashboard module, for example.
The system allows queries from the ERM solution and the Organization, Risk, Compliance, Workflow and Administration modules to be exported according to the schedule set. Information on the queries that are being exported is saved in a master table in the database named “RM_ExportedQueries” with the following columns:
•IntegrationTaskOid
•IntegrationTaskName
•QueryOid
•QueryName
•QueryDescription
•QueryModule
•QueryAuthor
•CreatedTableName
•UpdatedOn
Separate tables are created for each of the exported queries named “RM_{module of origin}_{query author}_{queryname}” with the same columns selected when the query was saved. The names of the tables created for each exported query will be available in the history of the integration task. This name is used to identify the SQL table of the query when creating custom charts in the Dashboard module or when editing report templates in Report Designer, and it must be entered exactly as listed in the task’s history.
You can choose to save previous query results. If this option is selected, the query results will be added to the previous query results once the task is executed, for as many times as it is specified. Otherwise, the latest results of these queries will overwrite existing information already exported once the task is executed.
The history of the task will indicate whether queries were exported successfully or if any errors occurred. If a query is deleted from the system or if you remove a query from the list of queries to be exported when editing the integration task, the corresponding table in the database will remain but will no longer be updated. Also note that if the author of a query is deleted, the query will remain available for exporting through this task.