Excel report templates
With Excel report templates, you can use an SQL query to save the data you want to an HTML file, and then make that file available for further processing in Excel. Since selecting the right data requires in-depth knowledge of our data structures, only system administrators and (in Employee Management) users with the “Analysis” role have access to the analytics pages. You specify which data and fields should appear in the individual reports. The actual SQL queries are usually prepared for you by a umantis employee.
In the table, you will find five columns with symbols at the right. In the first column , you can view the current data extract from the report. Clicking on the icon in the second column displays the existing Excel analytics for your report. You can edit or delete them there. Clicking on the icon in the third column starts the SQL query for this report, and you can see in the fifth column whether the query was valid. The symbol in the fourth column leads to this report’s history.
If you want to update a previously created Excel file without waiting for the specified interval, you can use the symbol to select the report you want, then click on the name of the Excel file to open the file. Click in a filled-out field in the Excel file to activate it. Then go to Data and select Refresh data. This will display the most recent data in the specified form.
If you want the data to be refreshed automatically when the file is opened, go to “Data range properties” under Data — External data. In the “External data range properties” window, check the box for “Refresh data on file open”.
- 1 Technical notes
- 2 Security of your data
- 3 Automatically refresh data in Excel
- 4 Open Excel file
- 5 Allow comment fields for Excel import
- 6 Important note
- 7 Actions
- The individual Excel report templates are stored as CronJobs which are executed at specific intervals. As part of each CronJob’s execution, the SQL query is run and the result is stored in a new HTML file in the database. The HTML is only created when the report is saved after the SQL query runs without error.
- The file can also be created manually via the symbol. This HTML file is then accessed by the query stored in the Excel file that was originally configured by umantis, and updates the data contained in the existing Excel report template.
- To prevent certain types of errors and misuse through the execution of SQL statements, the statement is filtered and blocked if certain syntax is found.
Commands like ALTER, DELETE, INSERT etc. cannot be executed.
- In addition, the “Analytics” module role must be assigned to at least one user on the customer’s side, since a user login must be specified for the call from inside the Excel file. The login information can either be hardcoded into the web request, or you can require that it be re-entered every time the Excel data are updated.
Note: If an SQL error occurs while your report is running (this also applies to diagrams and automatic exports), you must first correct the SQL and save it again, since the saved error prevents it from being executed again. Diagrams are an exception to this, as they are not subsequently blocked.
Security of your data
Note that your data are password-protected, and no one without access to the solution (i.e. a login) can view the data.
However, Excel report templates are NOT appropriate for use in analyzing sensitive data. Any employee can call up this report. It is not possible to limit access to the data to people with particular roles or access settings.
There is a secure alternative, however: report templates (Analytics > Report templates).
As a workaround, Excel sheets can also read data that can be provided via diagrams. In this case, umantis login-specific data can be prepared and entered in Excel. The templates for these reports can of course be saved with the Excel report templates. Make sure that you save the templates with no data in them, however. We cannot control data security if Excel files containing data are shared with other people. When in doubt, contact our Customer Service team for answers and support.
Automatically refresh data in Excel
One key factor here is the structure of the .iqy file that controls how connections are established from Excel to the umantis web solution. The correct structure and sequence of parameters for Applicant Management and Employee Management are listed below. All you need to modify are:
- Your customer ID
- The ID of the Excel checklist template
- Your username and password
IMPORTANT: Never save the data as “UTF-8 with BOM” (often referred to as simply “UTF-8”); if you do, Excel will not be able to process the file. The Windows-standard ANSI format is sufficient.
You can download an example file here: Example Excel report templates
Web query in Applicant Management (Stable) through 12/17/2012
save as webquery.iqy
Web query in Applicant Management
Web query in Employee Management
save as webquery.iqy
Open Excel file
- Data => Connections
- Click “Add” button
- Click “Browse for More...” button and add .iqy file
- Once the iqy has been added, click “Properties” in the window that is already open, then activate “Refresh on file open”
- Place the cursor at the location in the Excel file where you want to insert the query data
- Data => Open Existing Connections
- Double-click on the name - displayed with [Empty] - of the connection file in the window that appears
- Specify the cell (by default where the cursor is), username and password
- Load data
CAUTION: To refresh the data, always close Excel and then reopen it.
Allow comment fields for Excel import
To allow multi-line comment fields from umantis to Excel in a single field, you can do the following:
'=CONCATENATE("' || REPLACE(REPLACE(REPLACE("Title"."Comments",'"',E''''),'.','.";"'),E'\n','";CHAR(10);"') || '";)' AS "Goal of the action",
- Replace \n with CHAR(10) => break into lines
- Replace . with “;” => so that the maximum length of 255 is not exceeded when concatenating (long texts without \n cause problems otherwise)
- Replace " with ‘ => causes problems with the concatenation formula otherwise
- To display everything correctly, the field must be recognized as a formula, which is why we use =CONCATENATE("....";CHAR(10);"....")
Analytics on Excel report templates do not react to restrictions on permissions for individual users of the data, because the raw data are pre-calculated overnight, and it is not possible to know at that time who might want to retrieve those data the following day. If you want to read permissions-specific analytics into Excel, you can use a diagram template as a data source and use the diagram to generate HTML output that contains only the data that the person logging in has permission to view. In some versions, however, Excels show a behavior in which it remembers the first successful login on the computer, and then sends the old successful login information after the next login attempt. The result is that the same data are always delivered, because umantis receives the login information for the FIRST login again, and then (correctly) sends the data associated with that login.
Example web query in Employee Management via diagram template (requires special XSLT)
save as webquery.iqy... the IDs 1/1/1 can also be submitted as special SQL control IDs, and might contain e.g. the desired period.