This topic explains how to manage report templates provided by Modulo with the system installation. As with other content provided by Modulo, report templates cannot be edited directly or deleted. To adapt them to your organization’s needs, you must first create a copy before you can edit one. Edited reports can be tested before being enabled for use. You can also create SQL queries for the system database or for external Oracle or MS SQL Server databases. These queries can then be used when editing reports through Report Designer, a report editor provided by Modulo.
1. Access the Compliance module.
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 (Compliance Analysis Report), the Status column indicates whether the template is available for use or not, while the Origin column indicates if the template has been customized or if it was provided by Modulo.
3. In the Manage Templates section, click Edit next to the report template you want to edit and select the Copy Template option from the dropdown list that appears (see figure below). Note that this is only necessary for reports provided by Modulo. Other reports can be exported directly for editing.
The system displays a window where you can enter a name to identify the new template that will be copied (see figure below).
4. 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.
The system displays a success message.
5. You can rename the template you just created by clicking Edit next to the new template and selecting Rename Template from the dropdown list (see figure below). Note that only custom report templates can be renamed.
6.
Enter a new name for the template using a maximum of 80 characters and click
Save (
). If you want to quit the operation,
click Cancel (
) (see figure below).
The system displays a success message.
7. If you want to include a SQL query in a report, 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.
8. To create a new query, click Create SQL Query (see figure below).
9. In the Name field, enter a name to help identify the SQL query.
10. In the Database field, select the data source for the query. The options available include the system database, the Dashboard module database, an external MS SQL Server database, an external Oracle database, and the Intelligence Data database.
Note: SQL queries are run when their reports are generated. An exception is if the data source selected for the query is the Dashboard module or the Intelligence solution databases, which contain the same data as the system only structured specifically to facilitate use in reports. SQL queries using the Dashboard module as a data source will be run according to the schedule set for the integration tasks with the module, while queries using the Intelligence solution database will be run according to the schedule set for the tasks to extract and process intelligence data. Another exception are query results exported to SQL tables through an integration task, as these will also be run on the schedule set for the task. For details on integration tasks, see Chapter 17: Administration -> Integrations -> Integration Tasks.
11. 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 database 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: DataSource=(DESCRIPTION=(ADDRESS=PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); User Id=myUsername; Password=myPassword;
12. In the SQL Query field, enter the SQL query.
13. Click Validate and Execute to validate the SQL query and view the results obtained.
If no errors were found in the SQL query, the system runs the query and displays the results obtained (see figure below).
14. To save the SQL query, click Save. If you want to quit the operation, click Cancel. All saved SQL queries are available for use when customizing reports through Report Designer every time a report is exported. If a SQL query used in a report template is changed, 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.
The system displays a success message.
15. Click the List SQL Queries link to return to the list of queries.
16. To edit a SQL query, click Edit next to the query and make any necessary changes.
17. To save your changes, click Save. If you want to quit the operation, click Cancel.
The system displays a success message.
18. Click the List SQL Queries link to return to the list of queries.
19. 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).
20. Click Delete SQL Query to confirm. If you want to quit the operation, click Cancel.
The system displays a success message.
21. Click the List Report Templates link at the upper right corner to return to the list of report templates.
22. To export a template so that you can begin editing it, click Edit next to the template you want to edit and select Export Template from the dropdown list that appears (see figure below).
23. 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.
24. 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.
25. To import the edited template back to the system so that it can be used, click Import Template (see figure below).
The system displays the section where you can import edited templates.
26. Click Browse to locate the edited template.
27. In the window that appears, select the template to be imported and click Open.
28. Once selected, click Import Template so that the system can check the file and, if no errors are detected, import it (see figure below).
The system requests confirmation to import the template you edited and overwrite the existing one (see figure below).
29. 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.
If no errors are detected with the file selected, the system imports the template and displays a success message.
30. Click the List Templates link in the upper right corner of the screen to return to Manage Templates section.
31. To enable a template for use in the system, click Edit next to it and select Enable Template from the dropdown list that appears.
The system displays a success message.
32. To disable a template for use in the system, click Edit and select the Disable Template option from the dropdown list. If all the report templates are disabled, other users will not be able to generate any reports until at least one template is enabled.
The system displays a success message.
Note: Reports can be generated for either enabled or disabled templates by clicking Edit next to the template and selecting the Generate Report option from the dropdown list. If you want to check that the modifications you made to a certain template are being displayed properly, you can generate a report based on a disabled template, check the report, and only then enable it for use. For details on the report and how to browse it, see Chapter 6: Compliance -> Compliance Projects -> Compliance: The Analysis Phase -> Overview of the Compliance Analysis Report.
33. To delete a custom report template, click Delete next to it (see figure below).
The system requests confirmation of the deletion (see figure below).
34. To confirm deletion, click Delete Template. If you want to quit the operation, click Cancel.
The system displays a success message.