This topic explains how to manage report templates for scheduled events. Reports are created from scratch and may use both queries from the Organization, Risk, and Workflow modules, as well as SQL queries, as data sources. These blank templates can then be exported for editing, allowing the report to be assembled using data from these queries. Note that you can only edit report templates of which you are the author or an editor.
1. Access the Events solution.
2. Select Manage Templates from the Reports option on the menu.
The system displays the list of report templates available in the system grouped by language. The Template Type column indicates the type of report, the Status column indicates whether the template is available for use or not, while the Origin column indicates if the template was customized or if it was provided by Modulo.
3. To write a SQL query that can later be included as a data source in a report template, click Manage SQL Queries (see figure below).
The system displays a screen where new SQL queries can be created, and where all those previously registered can be edited or deleted.
4. To create a new SQL query, click Create SQL Query (see figure below).
The system displays a page where you can create a SQL query.
5. In the Name field, enter a name to identify the SQL query using up to 400 characters.
6. In the Database field, select the data source for the query. The options available include system database, the Dashboard module database, an external MS SQL Server database, or an external Oracle database.
7. If a MS SQL Server or Oracle database is selected, enter the connection string to connect to the database in the Connection String field and click Test Connection to ensure that the connection string is valid. The connection string is encrypted in the database and will not be displayed when the query is edited. The connection string fields are listed below. For more information on access data, please contact your system administrator.
Note: If the MS SQL Server database is selected, the connection string for the SQL Server can be for a standard SQL Server or for an instance. For example:
Connection string for a standard server: Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword;
Connection string for a server instance: Server=myServerName\myInstanceName; Database=myDataBase; User ID=myUsername; Password=myPassword;
If the Oracle database is selected, the Oracle connection string can be the one that does not use the tnsnames.ora file or the alternative. For example:
Connection string without tnsnames.ora: SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername; pwd=myPassword;
Alternative connection string omitting tnsnames.ora: Data Source=(DESCRIPTION=(ADDRESS=PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); User Id=myUsername; Password=myPassword;
8. In the SQL Query field, enter the SQL query.
Note: Databases cannot be edited through SQL queries and, therefore, the INSERT, DELETE, DROP, TRUNCATE, EXEC, ALTER, SHUTDOWN, COMMIT, EXECUTE, and CREATE commands are not accepted. Should any of these words be included in the query, when executing or saving it, an error message will be displayed indicating that the SQL query is invalid.
9. Click Validate and Execute to validate the SQL query and view its results.
If no errors were found in the SQL query, the system runs the query and displays the results obtained (see figure below).
10. To save the SQL query, click Save. If you want to quit the operation, click Cancel.
The system displays a success message. All saved SQL queries are available for inclusion as data sources in any report template. If a SQL query used in a report template is edited, it will have to be inserted in the template again through Report Designer and the report template must imported to the system once more for its results to be displayed correctly.
11. Click List SQL Queries in the upper right corner to return to the list of queries.
12. To delete a SQL query, mark the checkbox next to its name and click Delete SQL Query (see figure below).
The system requests confirmation to delete the SQL query selected (see figure below).
13. Click Delete SQL Query to confirm. If you want to quit the operation, click Cancel.
The system displays a success message.
14. In the Manage Templates section, click Create Template to generate a new template for a report (see figure below).
The system displays a screen where the new template can be created and linked to one or more data sources (see figure below).
15. In the Name field, enter a name to identify the new template using a maximum of 80 characters. Note that you cannot create a template with a name that is already registered in the system.
16. In the Language field, choose the language of the template by marking one of the available options.
17. To add queries to the template, click Add Query in the Queries tab in the Data Sources section.
18. Select the queries to be added to the template in the window that appears and click Add Query (see figure below). Only queries that you have created will be available for selection. Also note that if a query used in a report template is edited, it will have to be inserted in the report again through Report Designer and imported to the system once more for its results to be displayed correctly. If you want to quit the operation, click Cancel.
19. Once a query is added, the Combine Filters checkbox next to it will be marked by default to combine the filters set for this query with any filters selected when generating the report based on this template. If you want to use only the filters configured for the query, unmark the Combine Filters checkbox.
Note: If filters for queries and reports are set, make sure that the filters do not conflict with each other, which can generate a report with empty results. For example, if the Combine Filters checkbox is marked, a filter for the technology asset type is set for a risk query in the Queries section of the Risk module, and a filter for assets is set for reports, in the Generate Reports section of the Organization module, only technology assets will be displayed in the report. If none of the assets selected in the filters for the report are technology assets, the report will display no results.
20. To add SQL queries as data sources in the template, click Add SQL Queries in the SQL Queries tab.
21. Select the queries to be added to the template in the window that appears and click Add SQL Query (see figure below). If you want to quit the operation, click Cancel.
22. To remove a query from the list, click Remove Query. To save the template, click Save. If you want to quit the operation, click Cancel.
The system displays a success message.
23. You can
include columns from SQL queries as filter options in the report template, which
will be available when generating the report. To select which columns will be
available as filter options, click Select Filters (
)
next to the SQL queries you have included in the template (see figure
below).
24. In the pop-up window that appears, mark the checkboxes next to the columns you want to add as filter options in the report and click Select Filters. If you want to quit the operation, click Cancel (see figure below).
The names of the columns you have selected will be displayed next to the SQL query in the Filter Options column.
25. To generate a report, click Edit next to the generic report template you want to generate and then select Generate Report from the dropdown list that appears (see figure below). Note that only templates that were previously enabled can be generated. Also note that only template authors and editors can generate reports in this section.
26. The system loads a wizard where you can set filters and generate the report. For more information on how to set filters and generate generic reports, see Chapter 14: Events -> Scheduled Event Reports -> How to Generate a Report for Scheduled Events.
27. Click Manage Templates from the Reports option on the menu to return to the Manage Templates section.
28. To make any changes to the template, click Edit next to the report template you want to edit and select the Edit option from the dropdown list that appears. Note that the template can only be edited by its author or by any people or groups assigned as its editors.
29. After editing the fields, click Save. If you want to quit the operation, click Cancel.
The system displays a success message.
30. To include people or groups in the audience of a report template, click Edit and choose the Select Audience option (see figure below). The people and groups added will receive permission to generate reports based on this template in the Generate Reports section.
31. Click Add Audience and select People or Groups of People (see figure below).
32. In the window that appears, select the people or groups of people you want to include in the audience of the report template and click Add (see figure below).
The system displays a success message.
33. To remove a person or group from the audience, mark the checkboxes next to their names and click Remove Audience (see figure below).
The system displays a success message.
34. To assign people or groups of people as editors of a report template, click Edit next to the template and then click Select Editors (see figure below).
35. Click Add Editors and select People or Groups of People (see figure below).
36. In the window that appears, select the people or groups of people that you want to assign as editors of the template and click Add (see figure below). These people and groups will receive permission to edit all properties of the template and generate reports based on it in this section.
The system displays a success message.
37. To remove people or groups from the list of template editors, mark the checkboxes next to their names and click Remove Editors (see figure below).
The system displays a success message.
Note: If the author of a report template is deleted from the system, the report template will also be deleted if no audience members or editors are assigned. Otherwise, the template will not be deleted and the word "(deleted)" will be displayed next to the name of the author.
38. To export a template so that you can begin editing it, click Edit next to the template you want to export and select Export Template from the dropdown list that appears.
39. In the dialog box that appears, click Save to export the report and save it to your computer so that it can be edited using Report Designer.
40. Open the exported template in Report Designer, make the necessary changes, and save the file to your computer. For details on obtaining, installing, and using the main features provided by Report Designer, see Report Designer -> Report Designer – Quick Guide.
41. To import the edited template back to the system so that it can be used, click Import Template.
The system displays the section where you can import edited templates.
42. Click Browse to locate the edited template.
43. In the window that appears, select the template to be imported and click Open.
44. Once selected, click Import Template to import the file (see figure below).
The system requests confirmation to import the template selected (see figure below).
45. To confirm that you want to import the edited template and overwrite the older version, click Import and Overwrite Template. The original template will always be overwritten by the imported template, even if the name of the file is changed. If you want to quit the operation, click Cancel.
The system displays a success message confirming that the template was imported to the system, as well as a validation message and alerts, if any were found.
46. Click List Templates in the upper right corner of the screen to return to the Manage Templates section.
47. To copy a template, click Edit next to the template you want to copy and select Copy Template from the dropdown list.
48. In the window that appears, enter a name for the new template using a maximum of 80 characters and click Copy Template to save the new version. If you want to quit the operation, click Cancel (see figure below). Note that you cannot create a template with a name that is already registered in the system.
The system displays a success message.
49. You can rename a template by clicking Edit next to it and selecting Rename Template from the dropdown list.
50. Make the
necessary changes and click Save (
).
If you want to quit the operation, click Cancel (
).
The system displays a success message.
51. To enable a template for use in the system, click Edit next to it and select Enable Template.
The system displays a success message.
52. To disable a template for use in the system, click Edit and select Disable Template from the dropdown list. It will no longer be available in the Generate Reports section of this solution.
The system displays a success message.
53. To delete a custom report template, click Delete next to it (see figure below).
The system requests confirmation of the deletion (see figure below).
54. To confirm deletion, click Delete Template. If you want to quit the operation, click Cancel.
The system displays a success message.