OTISS provides a range of pre-defined Excel and PDF reports to provide summary charts and detailed information about the surveyed trees. In addition, you can customise your own Excel Report Templates (styles, colours, columns, charts, etc.) and OTISS will fill in the data. You can then save the Excel spreadsheet as a PDF document, or copy the Excel charts and data tables into your final report.

On the web site Furniture spreadsheet page, use the Site/Survey drop down boxes and the Search button to select what data to appear in the report. Then press the Reports button to select which report to create.

Selecting, Filtering and Sorting Data

Before you generate any of the Excel, PDF, CAD or GIS reports, you must first select what data to include in the report.

  1. The Site and Survey drop down boxes provide the simplest means of selecting the data.
  2. Optionally, you can also apply Search filters to the various columns on the table. Press the Search button at the top of the table. Setup rules that will filter the data shown in the table. You can setup search filters on several columns at once by adding new search rules.  For example: “Only show Signs that have condition of not Good”. Press the Search button to apply the search. Once you are happy that the data in the table looks correct, press the Close button.

  3. Finally, you can sort the table. See the column names along the top of the table; click on a column name to sort the data by that field. A little triangle symbol appears beside the name. Click again to swap between ascending or descending order. Only a single column can be sorted at a time.

The site/survey selection, filters and sorting you have setup on the table will all be used when generating the PDF, CAD and Excel reports.

PDF Reports

A few example PDF reports are available from the Furniture spreadsheet page on the OTISS web site. In the Reports-> PDF Reports dialog you will find the following:-

  • Summary: A set of useful summary charts and tables.
  • Report: A basic report listing all the trees surveyed.
  • Recommendations: A prioritised list of the recommendations that were made as part of the survey – ordered by urgency/priority. Using a checkbox you can include or exclude the “No action needed” and completed work items.
    • A full recommendations report will have the ‘completed’ and No Action recommendations at the end of the list.
    • A Work Schedule for the contractor team is best done by excluding all the No Action and completed work items.

These PDF reports cannot be customised directly. It is better if you create an Excel report (see below), customise it, and then use Excel File-> SaveAs and set the “Save as type” to PDF (*.pdf). This approach gives you more flexibility to achieve the required layout and format.

CAD Reports

The survey data can be imported into CAD drawings. Read more…

GIS Reports

The survey data can be downloaded in a variety of GIS formats. Read more…

Excel Reports

In the Reports dialog you will find the following pre-defined Excel reports.

  • Report (with/without photos): A basic report listing all the items surveyed.
  • Recommendations (with/without photos): A prioritised list of the recommendations that were made as part of the survey – ordered by urgency/priority. Using a checkbox you can include or exclude the “No action needed” and completed work items.
    • A full recommendations report will have the ‘completed’ and No Action recommendations at the end of the list.
    • A Work Schedule for the contractor team is best done by excluding all the No Action and completed work items.

For the reports with photos, the most recent photo for each item is included (i.e. highest Pnnn number). You can select which photo to use for an item 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.

  • Complete report: A multi-page spreadsheet showing all possible columns and charts. This is intended as a report that you would cut down and extract just the bits you need.
  • Raw data: A spreadsheet of the raw data that was collected as part of the survey. This report is intended as a means for you to download and archive the survey data.

To generate one of these pre-defined reports:-

  1. Select the Site and Survey, and setup the column filters and sorting to show the data you wish to appear in the report (as described above).
  2. Under the Reports menu, select the required report. Save the file on your computer.
  3. Open the report in Excel and adjust style and colours, remove un-wanted columns, etc.
  4. For PDFs, use Excel File-> SaveAs and set the “Save as type” to PDF (*.pdf).
  5. Or, 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.

Copy Excel tables to a Word document

Once you have generated the Excel report, you may wish to copy some or all of a data table to a Word document. Laying out lots of data in a document is never easy. You may wish to consult the help manuals and tutorials about Word to learn more ways to deal with tables. The simple way to get started is as follows.

  • In Excel you can select and Copy the columns you need.
  • Open the Word document and Paste them into the appropriate part of the document. This will create a Word table containing the data.
  • Do a mouse click-right over the inserted table and you should see a menu with some table options. There are several “AutoFit” options; the option “AutoFit to Contents” is often a good place to start.

Excel Template Reports

This feature allows you to create a customised PDF or Excel report. 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!

Please read the information at Excel Report Templates to understand how to prepare and use the Excel Templates.

Furniture Excel Data

The table below provides a list of all the possible data that you can put into the Excel reports. Consult this table while you are preparing your Excel Report Templates.

  • The Field Id is the keyword/name that you MUST put into the Excel template table header rows. OTISS will be looking for these Field Ids so it knows what data to put into which columns.
  • Most of the Field Ids listed below are exactly the same fields that you used on the survey form. The data is the values you entered during the survey and the Field Id in the header row will be replaced with the Field Name that is configured on the Customise Survey page.
  • In addition, there are several other Field Ids listed below that provide useful combinations or especially formatted variations.
  • Some of the survey fields can also be used in the Summary tables – see the Excel Complete reports for the summary data.
Field IdExample OutputNotes
Ref
Reference
T103Any of these Field Ids can be used.
TypeBench
CategorySub Type
StructureGroup
Height11
Width50
Length7.5
Num Items2
MeasurementsHeight: -3896.582
Length: -9963.718
Width: -6044.801Custom Number 1: 6511.736
Custom Number 2: 4887.466
Custom Number 3: -5089.758
Colour: 1234567890
Construction: 7
Custom Text 3:
Combine all the measurements. Does not include any fields marked as ‘hidden’.
Photo

Photo NoCaption
Photo NoCompress
The most recent photo is shown (i.e. highest Pnnn number). You can select which photo to use 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.

Use the column name ‘Photo NoCaption’ if you don’t want the caption to be shown.

We usually place a lower resolution image to fit the Excel cell size. Use the column name ‘Photo NoCompress’ if you want to have the actual photos – but the spreadsheet can get very large! It may be better to download a zip file of the actual photos from the Photos webpage.
Photos

Photos NoCaption
Photos NoCompress
The fieldId “Photos” will include all the photos for each tree. They are each included in separate columns – so this should always appear as the right-most column in the table.
DescriptionDescription of location or purpose.
InscriptionInscription on a sign or bench, etc.
Survey NotesNotes on the condition.
ConditionGood
LocationPOINT (-0.0000004885501829 50.99999994079486)Location shown as WKT with latitude and longitude.
Easting540434.126 digit O.S. Easting
Northing824193.876 digit O.S. Northing
Grid RefSX 12345.67 45678.90A grid reference format with centimetres.
Grid Ref 10SX 12345 45667A 10 digit grid reference. Accurate to 1 metre.
Grid Ref 8SX 1234 4567An 8 digit grid reference. Accurate to 10 metres.
Grid Ref 6SX 234 543A 6 digit grid reference. Accurate to 100 metres.
Google Maps https://www.google.com/maps/search/?api=1&query=51.4,0.1 This web link (URL) will open the Google Maps web page and place a marker on the position of the item.
This is most useful when used in an Excel formula to combine the item reference and location into a clickable link – and works well when the spreadsheet is saved as a PDF. For example use, formula =HYPERLINK(X4,B4) where cell X4 is this Google Maps link and cell B4 is the Reference (or any other text).
What 3 Words ///split.splash.splot The ///what3words address of this tree. Note: adding this column will slow down the report generation a little (because the addresses have to be calculated) – allow 30 seconds extra per 1000 items.
Area22.23Calculated area of polygon in square metres.
Boundary7.45Calculated boundary length of polygon in metres.
Line Length57.45Calculated length of line in metres.
SiteParklandsSite Name.
SitesSite Name1; Site Name5; Site Name7;List of all the sites where this item is geographically within the site boundaries.
SurveyorPaul MoranUser’s name
Inspected11 July 2014Inspected Date
Inspected Time11 July 2014 14:35Inspected Date and Time.
Inspect Period2 Years
Inspection Due11 July 2024When the next inspection is due, as calculated from ‘Inspected’ and ‘Inspect Period’.
customNum11
customNum2150
customNum31
customText1Good
customText2High
customText3option a
RecommendationsRecommendations 1:
first one
Timescale: 1 Month

Recommendations 2:
second one (if present)
Work Completed on 12-Oct-26
Work Notes: all work done except…

Long term Recommendations:
third one (if present)
Timescale: 1 Year
Combined text showing all the recommendations. Does not include any fields marked as ‘hidden’.
Recommendation1Whatever text was entered for the recommendation.
Timescale13 months
Work Due111-Jul-14Recommendated date for completion, as calculated from the ‘Inspected’ date and ‘Timescale’.
Work Timescale111-Jul-14 (3 Months)
Work Completed on 12-Aug-15
Combined WorkDue and Timescale, or the Work Completed on date.
Work Date1
Work DateTime1
12-Aug-15
12-Aug-15 12:43
The date or date/time when the work was marked as completed.
Work Name1Peter JoulesThe name of the OTISS user that marked the work as completed.
Work Done1 Whatever text was entered for the works notes.
Recommendation2
Timescale2
Work Due2
Work Timescale2
Work Date2
Work DateTime2
Work Name2
Work Done2
As above… As above for the recommendation2 fields…
Recommendation3
Timescale3
Work Due3
Work Timescale3
Work Date3
Work DateTime3
Work Name3
Work Done3
As above… As above for the recommendation3 fields…
Recommendation
Timescale
Work Due
Work Timescale
Work Date
Work DateTime
Work Name
Work Done
As above… When doing Recomendation-only data tables, use the column name ‘Recommendation’, ‘Timescale’ rather than ‘RecommendationX’, ‘TimescaleX’, etc.
SiteParklandsSite Name
Site DescriptionParkland around the main house.The description from the Site information.
Risk ZoneRisk ZoneThe Tree Safety Risk Zone from the Site information.
Address10 Parkway
Burlington
North Shire
AA1 2BB
The address of the site on multiple lines.
Address110 Parkway, Burlington, North Shire, AA1 2BBThe address of the site on a single line.
PostcodeAA1 2BBThe site’s postcode.
Contact  Fred Jones
01234 123 342
+(44)7712 123 123
fred@housing-assoc.co.uk
The site’s contact name, telephone and email on multiple lines.
Contact1  Fred Jones, 01234 123 342, +(44)7712 123 123, fred@housing-assoc.co.ukThe site’s contact name, telephone and email on a single line.
Telephone1  01234 123 342Site Contact telephone1
Telephone2+(44)7712 123 123Site Contact telephone2
Survey  Initial tree survey.From the Survey record information.
Survey DescriptionA 2019 baseline survey, focusing on the …From the Survey record information.
Survey ReportWeather,… Constraints,…From the Survey record information.
Survey StatusActive, Work, Closed, etc.From the Survey record information.