Overview of Report Output in Excel
If you have a security daily report or other report information in .xlsx format, you can output
the report information (patrol date and time, type, results, etc.) registered in ugo Portal
automatically stored in the security daily report (.xlsx format sheet).
To use this function, you must first create a "template."
The "template" is a setting for each cell in advance to store the data that can be output from ugo Portal based on your .xlsx format daily report sheet.
Create a template
(1) Preparation: Preparing a book for creating a template
First, prepare a workbook that contains your daily report sheet and
add an empty sheet named "rawdata".
If you want to check the function first, please use this sample data.
|
Sample data or your daily report sheet (name is up to you) |
An empty sheet named "rawdata" |
Click "Report Management" from the global menu on the far left of the ugo Portal, and
select "Export" in the top right corner of the "Report List" screen.
Expand "Excel Template List" and click the New icon in the upper right corner.
When the "Template Registration" screen appears, enter the necessary information and
upload the book file you prepared earlier.
|
For "Building", select the building from which the report is generated. |
There is no problem if you leave "Spanning days" off. |
Finally, click "Register" to complete the template registration.
(2) Temporary output of report - Setting functions in the temporary template
Use the registered temporary template to export to Excel.
From "Export" in the top right of the report list screen, select "Excel Export".
In "Template" in the red frame in the image below, select the template you just registered, and
specify the "Start Date" and "End Date" within the range in which the report actually exists.
Finally, click "Excel Export" to start the download.
After the download is complete, open the xlsx file in your preferred application.
When you check the "rawdata" sheet of the output workbook, you
will see a list of data output from reports recorded in ugo Portal.
Use the necessary elements from this data and set it up so that it is reflected in your daily report sheet in function format.
Here, we will show you an example of the functions to use, based on the sample data distributed in the (1) Preparation section .
=rawdata!B2
→ Store the target date of the report (rawdata sheet: B2) in that cell.
=rawdata!D2
→ Store the target building name of the report (rawdata sheet: D2) in that cell.
=IF(COUNTIFS(rawdata!C:C, "10*",rawdata!H:H, "Patrol") >0, "✔️","")
→ If a report with "Patrol type" was recorded in the "10 o'clock hour", store "✔️" in that cell.
(3) Registering the completed template ~ Outputting a report using the template
Once you have finished setting the functions, re-register the book as an official template.
As before, expand the "Excel Template List" and click the New icon in the upper right corner.
When the "Template Registration" screen appears, set the various information and
upload the workbook file that contains the daily report sheet and rawdata sheet for which you set the function earlier.
*If there is a time period such as 25:00 that would be the next day if the original time is expressed,
enable "Template spanning days".
This will output the report for the current day and the day before and after as a file for the specified day.
|
For "Building", select the building from which the report is generated. |
Please be careful with "spanning days" settings |
Finally, click "Register" to complete the template registration.
Use that template to output to Excel.
From "Export" in the top right of the report list screen, select "Excel Output".
In "Template" in the red frame in the image below, select the template you just registered, and
specify the "Start Date" and "End Date" within the range in which the report actually exists.
Finally, click "Excel Output" to start the download.
After the download is complete, open the xlsx file in any application. From now on, you can continue outputting using the same template by simply changing the output period.