OTISS provides you with the ‘building blocks‘ for your final report. We do lots of the hard work creating maps, data tables and charts, and then you put them together – applying your own company style and tailoring the report for each customer. Here are some different approaches:
- Use your own Microsoft Word template to write the summary and main body of the report. At various points in the report you could insert; screenshots from the OTISS Maps page, photos of specific issues, pie charts and short tables from the OTISS Excel reports. Finally, use the File-> SaveAs to create a PDF version to send to the customer.
- Use the OTISS Excel Report features (described below) to generate an spreadsheet of the report data. Then either (a) copy-n-paste the table(s) into your report, or (b) use the Excel File-> SaveAs to create a PDF to be sent along with the main report.
- Use the OTISS Maps-> Print webpage to create a PDF to be sent along with or inserted into the main report.
- Use CAD to do the final layout of the tree protection plans and create a PDF to be sent along with or inserted into the main report.
- If you have separate documents for the main report, data tables, drawings and maps, you can use Adobe Acrobat DC Pro (or similar) to merge them all into a single document.
Excel, PDF, CAD and GIS Reports
OTISS provides a range of pre-defined Excel, PDF CAD and GIS 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 BS5837 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.
- The Site and Survey drop down boxes provide the simplest means of selecting the data.
- 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 Ash trees that have life stage of Over Mature”. Press the Search button to apply the search. Once you are happy that the data in the table looks correct, press the Close button.
- 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, search 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 PDF reports are available from the BS5837 spreadsheet page on the OTISS web site. In the Reports-> PDF Reports dialog you will find the following:-
- Report: A basic report listing all the trees surveyed.
- Recommendations: A prioritised list of the recommendations made as part of the survey.
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 trees surveyed.
- Recommendations (with/without photos): A prioritised list of the recommendations made as part of the survey. The recommendations are listed in order of urgency/priority – those with timescale=NoAction will appear at the end.
For the reports with photos, 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.
- 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:-
- 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).
- In the Reports dialog, select the required report. Save the file on your computer.
- Open the report in Excel and adjust style and colours, remove un-wanted columns, etc.
- For PDFs, use Excel File-> SaveAs and set the “Save as type” to PDF (*.pdf).
- 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.
BS5837 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 Field Id in the spreadsheet header row will be replaced with the Field Name that is configured on the Customise Survey page. The Excel columns underneath the Field Id will be filled with the data values that you entered during the survey.
- 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 Id | Example Output | Notes |
|---|---|---|
| Ref Reference Tree No. | T103 | Any of these Field Ids can be used. |
| Reference2 | DBC-203 | |
| Species | Oak ‘variety’ (Quercus sp.) | Combine common, botanical name and caregory/variety. Groups will list all the tree species. |
| Common Common Name | Oak Ash, Common x7 | Groups will list all the tree species. |
| Botanical Botanical Name | Quercus sp. Prunus avium x3 | Groups will list all the tree species. |
| Variety | variety | |
| Structure | Group | |
| Num Stems | 4 | As recorded for multi-stemmed trees, or the total NumTrees recorded in the group. |
| Full Structure | Group (23 trees) | Combines structure and number of stems/trees. |
| Height | 11# | Estimates shown with a # symbol. |
| Stem Diameters | 300, 200, 150, 140 | The single or multiple stem diameters. Estimates shown with a # symbol. |
| Stem Diameters2 | 400 4 stems: 300, 200, 150, 140 3 stems 350 (avg) | A useful formatted presentation of either (a) single stem, (b) multiple stems measured or (c) multiple stems averaged. |
| Combined Stem Diam | 512 | Combined stem diam calculated as defined in BS 5837:2012 |
| Stem Diam 1 Stem Diam 2 … Stem Diam 10 | 350 | Rather than combin all the stem diameters into one cell, these fieldIds allow you to place each stem diameter in a separate column. |
| Life Stage | Mature | |
| Life Expectancy | 30+ | |
| Measurements | Height(m): 11 Crown Radius(m): 1 Stem Diam(mm): 150 Life Stage: Newly planted Life Exp.: 9+ Years | Combine all the tree based 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. | |
| Comments | Comments | |
| Survey Notes | Whatever text was entered for the survey notes. | Combined text showing all three survey notes fields. Does not include any fields marked as ‘hidden’. |
| Survey Notes1 Survey Notes2 Survey Notes3 | Whatever text was entered for the survey notes. | The three, multi-line survey notes fields. |
| Retention Category | A1,2 | |
| North | 5 | Estimates shown with a # symbol. |
| South | 6# | Estimates shown with a # symbol. |
| East | 7 | Estimates shown with a # symbol. |
| West | 8# | Estimates shown with a # symbol. |
| Spread | N:5 S:6# E:7 W:8# | Combined spread measurements |
| Crown Clearance | 4# | Estimates shown with a # symbol. |
| Lowest Branch Height | 12 | Estimates shown with a # symbol. |
| Lowest Branch Direction | W | |
| Lowest Branch | 12(W) | Combines lowest branch height and direction. |
| RPA Radius | 8 | RPA Radius calulated as per BS5837:2012 |
| RPA Area | 1.56 | RPA Area calulated as per BS5837:2012 |
| RPA | Radius: 1.6m. Area: 8 sq m. | Combines RPA Radius and Area |
| Location | POINT (-0.0000004885501829 50.99999994079486) | Location shown as WKT with latitude and longitude. |
| Easting | 540434.12 | 6 digit O.S. Easting |
| Northing | 824193.87 | 6 digit O.S. Northing |
| Grid Ref | SX 12345.67 45678.90 | A grid reference format with centimetres. |
| Grid Ref 10 | SX 12345 45667 | A 10 digit grid reference. Accurate to 1 metre. |
| 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 tree. This is most useful when used in an Excel formula to combine the tree 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 trees. |
| Area | 22.23 | Calculated area of polygon in square metres. For Groups, woodlands, etc |
| Boundary | 7.45 | Calculated boundary length of polygon in metres. For Groups, woodlands, etc |
| Site | Parklands | Site Name. |
| Surveyor | Paul Moran | User’s name |
| Inspected | 11 July 2014 | Date of the inspection. |
| Inspected Time | 11 July 2014 14:35 | Date and time of the inspection. |
| Inspect Period | 2 Years | |
| Inspection Due | 11 July 2024 | When the next inspection is due, as calculated from ‘Inspected’ and ‘Inspect Period’. |
| pCondition | Good | |
| sCondition | Collapsing | |
| customNum1 | 1 | |
| customNum2 | 150 | |
| customNum3 | 1 | |
| customText1 | Good | This is usually the Physiological Condition. |
| customText2 | Poor | This is usually the Structural Condition. |
| customText3 | option a | |
| Measurements2 | Physiological Condition: Good Structural Condition: Fair Other Reference: 1 Distance to main target: 1 Budget £ (est.): 150 You customise: 1 Amenity Value: Good Wildlife Value: High You customise: option a | Combine all the CustomText and CustomNum fields and values. Does not include any fields marked as ‘hidden’. |
| Recommendations | Pre construction: first one Timescale: 1 MonthDuring construction: second one (if present) Timescale: 1 MonthPost construction: third one (if present) Timescale: 1 Year | Combined text showing all the recommendations. Does not include any fields marked as ‘hidden’. |
| Recommendation1 | Pre construction recommendations | |
| Recommendation2 | During construction recommendations | |
| Recommendation3 | Post construction recommendations | |
| Recommendation | Text entered for recommendations | When doing Reccomendation-only data tables, use the column name ‘Recommendation’ rather than ‘RecommendationX’. |
| Timescale | Pre construction | When doing Reccomendation-only data tables, use the column name ‘Timescale’ to show whether the recommendation is Pre, During or Post. |
| CAVAT Value | £19,822 | The result (£) of the CAVAT valuation. |
| TEMPO Decision | Definitely merits TPO(16+) Does not merit TPO(7-11) | The Decision Guide result of a TEMPO valuation. |
| Site | Parklands | Site Name |
| Site Description | Parkland around the main house. | The description from the Site information. |
| Risk Zone | Risk Zone | The Tree Safety Risk Zone from the Site information. |
| Address | 10 Parkway Burlington North Shire AA1 2BB | The address of the site on multiple lines. |
| Address1 | 10 Parkway, Burlington, North Shire, AA1 2BB | The address of the site on a single line. |
| Postcode | AA1 2BB | The site 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.uk | The site’s contact name, telephone and email on a single line. |
| Telephone1 | 01234 123 342 | Site Contact telephone1 |
| Telephone2 | +(44)7712 123 123 | Site Contact telephone2 |
| Survey | Initial tree survey. | From the Survey record information. |
| Survey Description | A 2019 baseline survey, focusing on the … | From the Survey record information. |
| Survey Report | Weather,… Constraints,… | From the Survey record information. |
| Survey Status | Active, Work, Closed, etc. | From the Survey record information. |