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 data columns 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 as several recommendations, then there will be several rows in the table.
Table Header Row and Table Format Row
- The ‘Table‘ keyword in the left hand column tells OTISS where to look for the column names – this is the Table Header Row.
- 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 column names (see the relevant Help page for each survey type). When matching the column names, 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 column names will be replaced by the labels that you have configured, e.g. the ‘customNum1’ column name will be replaced by “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.
- The row immediately after the Table Header Row is called the Table 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 – its 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 columns that you can use – e.g. ‘Measurements’, ‘RPA’, etc.
- When Excel opens the finished report, with will automatcially adjust the height of each row to the contents.
Photos can be added to Data Tables using the column name ‘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 column name ‘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.
Using Formulas in Tables
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 Table 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.
- 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, Age Class, Risk Category etc. Once again, there are two types.
- Summary Table of Survey Data: All the selected trees/items are included in the summary table, or
- Summary Table of Recommendations Data: The summary is made of all the recommendations by placing 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.
The keyword ‘Summary‘ in a cell tells OTISS that a summary table is required. The next cell on the right must contain a suitable column name. Not all the column names used in the main data table 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, 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 Excel template, you can place a total row below a summary table and setup a suitable formula to SUM(…) the data values.
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.
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.
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 ‘columnLabels’ and ‘columnSeries’ (the counts) – where column is the column name 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!columnnameSeries’ 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.