OTISS provides a powerful system that allows you to create customised PDF and Excel reports. You can create a set of Excel Report Templates to layout your company style, fonts, colours, column widths, border, formatting, etc. Select which survey data fields to include in the tables and add summary tables to provide some key statistics: e.g. condition, species, etc. So rather than post-processing the report, you can do most (if not all) the work beforehand! You can then generate and save the Excel report as a PDF document, or copy the Excel charts and data tables into your other report documents.
This feature is available on all survey modules – TreeSafety, BS5837, Estate Management, etc.
Excel Report Templates
Under the Reports-> Excel Reports-> Report templates menu, OTISS provides several Excel templates with example tables for you to customise.
- On the “Excel Templates” dialog, click on one of the template links (e.g. xxx_report.xlsx), to download a report template and save it on your computer.
- Use Micosoft Excel to edit the template.
- Customise the fonts, colours, styles, column widths, border, formatting, etc. Re-arrange the columns and delete the ones you don’t need.
- Select the summary tables to provide some key statistics: e.g. condition, species, etc. Delete the ones you don’t need.
- Save your configured templates in a safe place, and use them over and over again.
- On the “Excel Templates” dialog, press the Browse button and select your prepared template.
- When you press Download Report, the template is uploaded to the website and a report spreadsheet is immediately returned to you with the requested data.
- Open the report and either (a) use Excel File-> SaveAs and set the “Save as type” to PDF (*.pdf), or (b) cut-and-paste the charts and tables into your final report.
Note: your web browser may block the report and will be required to “Allow Popups” for the www.otiss.co.uk website.
Note: The reports are generated in a separate browser tab/window. This may take some time for larger reports or reports with photos. While it is being generated, you can continue to work on the OTISS website.
Editing the Excel Templates
Save the template to your local computer and open it with Excel.
- First you should Unhide column A in the Excel template. This column contains some important keywords that OTISS needs to process the template.
- The templates contain one or more worksheets for you to customise. OTISS will fill in data tables on all the worksheets (not just the first one).
- You set up all the fonts, colours, styles, column widths, border, formatting, etc. OTISS does not change any of these – it simply adds the data to the spreadsheet.
- On each worksheet, setup some Summary Tables (optional) and a single Data table (see below). Anything else you add to the template will be copied directly and unchanged into the report.
- For each Data Table, add, move or change the columns as required.
- Setup the Page Layout as required, i.e. portrait/landscape, page headers/footers, etc.
The Data Tables contain all the survey data that you collected. There are two types of data table.
- Survey Table: The survey data is presented with a single row for each tree/item selected, or
- Recommendations Table: A table of recommendations can be generated by placing the word ‘Recommendations‘ in the A1 cell. In this case, only trees with recommendations are listed. If a tree has several recommendations, then there will be several rows in the table.
Data Table – Table Header Row
- The ‘Table‘ keyword in the left hand column tells OTISS where to look for the survey data Field Ids – this is the Table Header Row. These Field Ids tell OTISS what data to place in which Excel columns.
- On the Table Header Row, you setup the fonts, colours, styles, column widths, borders, formatting, etc. Re-arrange the columns and delete the ones you don’t want.
- You must use the correct OTISS Field Ids (see the relevant Help page for each survey type). When matching the Field Ids, OTISS will ignore any spaces and does not mind if it is upper or lowercase. For those columns that are configurable on the Configure Survey page, the Field Ids will be replaced by the Field Names that you have configured, e.g. the ‘customNum1’ field will be shown as “Budget Est. £”.
- OTISS will delete everything below the header row and replace it with the data. This means that you can only have one Data Table per worksheet. But you can have multiple worksheets within the Excel spreadsheet with a different Data Table on each one.
Data Table – Data Format Row
- The row immediately after the Table Header Row is called the Data Format Row, and it is also part of the template.
- On this row you can setup the fonts, colours, styles, formatting, etc. for the data rows. These styles and formating will be retained and applied to all the data rows.
- Any text, numbers or data in this row is just here so that you can see what it will look like in the finished report. This data will be ignored – it is the style and formatting that is important.
- In most cases, the data to be placed in the columns is the values that you entered on the survey form. In addition, OTISS provides a set of pre-formatted Field Ids that you can use – e.g. ‘Measurements’, ‘RPA’, etc.
- When Excel opens the finished report, it will automatically adjust the height of each row to the contents.
Data Table – Adding Photos
Photos can be added to Data Tables using the Field Id ‘Photo’.
In the Excel template, you use the row height and column width to define the maximum size of the photo within the table. Excel has a maximum row height of 409 – which allows 2 or 3 rows to a page. The photos will be scaled to fit into the cell size that you setup.
The most recent photo for each tree is included (i.e. highest Pnnn number). You can select which photo to use for a tree by setting the required photo’s Category to ‘Report’. If there are several photos with a Category of ‘Report’, then the most recent of these is included.
You can have multiple columns of photos in the table – use the same Field Id of ‘Photo’ for each one. The leftmost ‘Photo’ column contains the photo with category=Report and with the highest Pnnn number. Any subsequent ‘Photo’ columns use lower Pnnn photos.
Note: adding photos to reports means that they can take quite a lot longer to generate.
Data table – Using Formulas
Excel has a very flexible system for creating formulas in one cell that use ‘raw’ data values from other cells. You can use this feature to format the survey data in practically any way you like.
You can include formulas in the Data Format Row (as shown above). This way you can create a new column to perform some calculation on the survey data, and automatically include it within your report. This could be a mathematic calculation, or creating a new descriptive column that combines and formats other columns. OTISS will copy this formula for each row of data added to the table.
The source data cells for the formula must also be listed in the table header so that OTISS places the data in the table and then Excel can use it in the formula. If you don’t want these ‘raw’ data columns to appear in the report, you can use Excel to ‘Hide’ the columns in the template, or place them on the right hand end of the table (leaving a gap of a few columns if you want). When you download the report, OTISS will fill all the visible and hidden columns, and when Excel opens the spreadsheet, it will automatically calculate the formulas and show the results. Note: You may have to press the ‘Enable Editing’ button before Excel does the calculations.
Data Table – coloured cells based on contents
A really useful feature is to be able to colour the cell background based on the cell’s value. For example, the Condition, ISA TRAQ or QTRA Rating or Timescale columns could be coloured red, orange, green, etc. to highlight the risky trees or the ones in most urgent need of attention. We have added this feature to make it far easier for you to setup – although Excel experts could achieve similar results using the Excel Conditional Formating feature once the report is generated.
As described above, the cells in the Data Format Row (i.e. the row immediately below the Table Header Row) is where you setup the fonts, colours, styles, formatting, etc. for the data rows. Now the clever bit! For a particular column, if you setup further rows immediately below the Data Format Row, these will the used as extra Data Format Cells whenever their contents match the actual survey data. If no values match, then the style in the main Data Format Row is used (as you would expect). Note: the value in the Data Format Row is always ignored – the matching values are the ones below the Data Format Row. Some of the templates already have these coloured configurations setup which you can use or copy.
Technically speaking, a case-insensitive, textual comparison is made and if the actual data value ‘contains’ the text found in the template cell value, then that template style is applied to the report cell. For example, a template value of “high” will match data values of “High”, “Very HIGH”, “Moderately High”, “Highly possible”, etc. The matching starts from the top and works down the template column of values – stopping when it gets its first match.
Data Table – Troubleshooting
- Excel is not perfect! Sometimes when it calculates a formula, it does not automatically adjust the row height correctly. If this happens, you will only see part of the result. To resolve this, select the rows and use the Excel command Home-> Format-> Autofit Row Height.
- I’m not sure if it’s a bug or a feature, but Excel will use the data in any hidden cells when determining the autofit row height. This means if the hidden data cells contains lots of lines of text, then entire row hight will expand to accomadate this – even though this column is hidden! This feature can be used to our advantage to get the correct layout for each row in the visible table. We can control how this hidden data is formatted in the Table Format Row for those hidden columns:- (a) by adjusting the column widths and (b) by setting the Wrap Text feature on/off.
- Remember, when using Excel to edit your templates, UnHide all columns, then Hide them again (as required) before using the template with OTISS.
Summary tables provide some statistics and counts for the different survey fields, e.g. Condition, Species, Age Class, Risk Category etc. Once you have a summary table, then you can get Excel to create charts and tables to include in your report. See the ‘complete’ templates for examples.
There are three types of summary table – per-tree, per-inspection or per-recommendation. Depending on the data and what you want to show, you have to select which is the most appropriate table to use.
- Per-Tree Summary Table: These tables show a summary of the data on a ‘per tree’ basis. The number of trees in each group or hedge are included in the statistics. For example, if a group or hedge has 10 trees and has been given a condition of ‘poor’, then the Per-Tree Condition Summary table will show 10. These are the most useful tables.
- Per-Inspection Summary Table: These tables show a summary of the data on a ‘per inspection’ basis. For example, if a group or hedge has 10 trees and has been given a condition of ‘poor’, then the Per-Inspection Condition table will only show 1 (for the inspection).
- Per-Recommendation Summary Table: These tables show a summary of the data on a ‘per recommendation’ basis. For these tables, you need to place the word ‘Recommendations‘ in the A1 cell – only trees with recommendations are included. If a tree as several recommendations, then it will be counted several times. For example, if a tree has 2 separate recommendations and has been given a condition of ‘poor’, then the Condition table will show 2 (for both recommendations). Only on rare occasions are these tables useful. Usually better to use the ‘per tree’ or ‘per inspection’ statistics.
OTISS uses the keywords ‘No. trees‘, ‘No. inspections‘ and ‘No. recommendations‘ in a cell to know that a summary table is required. The previous cell on the left must contain a suitable OTISS ‘Field Id’. Not all the survey fields used in the main data tables above are suitable for these summary tables. Depending on the data you are collecting, sumarizing many of these fields may not be useful. So carefully select which fields are useful for each situation.
The two header cells (shown in green above), and the two cells immediately below them are part of the template. Use Excel to customise the fonts, colours, styles, column widths, borders, formatting, etc. OTISS will copy the data cell styles to all the data it adds to the table. OTISS does not change any of these styles, it simply adds the data to the spreadsheet.
In the example above you can see the difference between per-tree and per-inspection statistics – all the trees in the groups are included in the per-tree table.
In the Excel template, you can place a total row below a summary table and setup a suitable formula to SUM(…) the data values. But always make sure there are enough blank rows below the data format row. If you don’t leave enough blank rows, OTISS will stop the list with an ‘Others…’ value to show that their were more values, but not enough room to show them all. OTISS will not overwrite any other cells in the template. Whereas with the main data tables described above, you can only have one table per worksheet, for these Summary tables you can have lots on the same sheet – that is why OTISS expects there to be blank cells under the data format cells.
Summary Tables – Excel Charts – simple approach
The easiest way to create Excel charts is to get OTISS to generate a pre-defined ‘Complete’ report (as described above), or use the Summary Tables provided by a template-driven report.
Once you have the report:-
- Open the report with Excel, select the Insert tab of commands.
- Select the summary table (as shown) and use the Excel commands to create a Line, Pie or Bar chart as required.
- Use the standard Excel features to adjust the styles and colours on the chart.
- Select the chart and copy it to the clipboard.
- Paste the chart into your document or emails.
Summary Tables – Excel Charts – template configured
You may have noticed that the template already has some pre-defined charts that are automatically updated when you generate the data. Setting up these requires some Excel expertise – so we recommend the ‘simple approach’ described above for non Excel experts.
The complication arrises because we don’t know beforehand how many rows there are for the chart. If we set up a chart in the template, then we would have to guess how many rows there will be – which may be wrong. The solution is to use the Excel feature called ‘Named Ranges’. Its a bit tricky – but this is the best solution we have found.
For those Excel experts, here is what you need to do:-
- In the template, setup some trial summary data and create a chart in the normal way.
- Use the Excel-> Formula tab -> Name Manager to create two ranges, called ‘fieldIdLabels’ and ‘fieldIdSeries’ (the counts) – where fieldId is the Field Id for the summary table. Each should include all the data cells for the labels or series respectively – but not the header cells.
- Right click on the chart and then ‘Select Data…’ from the menu. Edit the Axis Labels and Series values to use the named range. Use ‘=spreadsheetname.xlsx!fieldIdSeries’ rather than a conventional range e.g. ‘=C2:C9’.
- When the chart looks OK, you need to delete your trial data – leave only the first row of data for the syles.
- When you generate the report from the template, OTISS will update the Named Ranges that you created with the actual ranges for that table. Excel should display the chart correctly when you open the report.
- This can be tricky! So study the existing charts in the template – and you may have to play around a bit. Good Luck.
When editing an Excel Template you can simply delete the “Your logo here” image that is already there and insert your own company logo image and place/scale it where-ever you like – but must be above any data tables. OTISS will simply copy it into the final report with all the other contents. This is the recommended approach.
Alternatively, you can use the “Your logo here” image template already in the template. Your logo will be aligned left, right or centred ‘within’ the space/bounds of this image template – i.e. it will always be scaled so that its width or height is the same as the image template. Select the “Your logo here” picture to adjust the size/shape, and use the Excel name box (top left of spreadsheet) to ‘name’ this picture to be either ‘logo left’, ‘logo centre’ or ‘logo right’.
Extra Information Fields
There are a few extra fields that can be inserted into the report template. These are related to the client, estate or site – rather than data about each tree/item.
OTISS scans the template for the keywords below (including the ‘#’ character) and replaces them with the specified text. These can be placed anywhere in the template – but must be above any data tables. As before, you set up all the fonts, colours, styles, column widths, border, formatting, etc. OTISS does not change any of these – it simply adds the data to the spreadsheet. See the examples in the above screenshot.
|#estate name||The name of this client or estate.|
|#site name||The site name for this report. If the report covers all sites in the estate, then this will say “All Sites”.|
|#risk zone||The Risk Zone associated with the single selected site. This cell is left blank when the report is for “All Sites”.|
|#survey ref||When all the data is part of a single survey, this shows the survey reference for that survey, otherwise it says “All Survey”.|