Priority: high
Updating: rare

This HowTo describes how we report to categorical site owners about site characterization and water test results. Routine aspects are in an annual report to the cooperator; notable analytical results may trigger out-of-cycle communications.

Change log:

When	Who	Comment
2023 01 13	Sp17	First version partial.
2023 06 04	Sp17	Fleshing out toward DEC review.
2023 06 21	Sp17	Markdown conversion, minor edits.
2023 12 19	Sp17	Revisions to reflect technical process for building 2023 reports using Quarto. Other small edits. Bumped to 50%.
2024 07 10	Sp17	Cumulative update after all 2023 reports are completed and distributed. Bumped to fully completed.

Related HowTos:

1. Objectives of categorical reporting:

Mobilize site level data interpretation.
Inform categorical owners about results, with interpretations, and how we understand their sites.
Trigger information corrections and updates from categorical owners. This includes using the reports as bases of discussion when we visit the sites to sample.
Provide input to annual reports to DEC.

2. Timing of Reports:

Initial reports in early 2023, covering 2022 data before DEC pesticide results are back. We submitted supplemental reports containing pesticide data later.
It is important to get analytical data back to the cooperators to retain their interest in allowing later sampling. In particular, we strive to have a report covering the prior year before we schedule the spring sampling. This may mean that we prepare a partial report if all pesticide analytical data are not back to Cornell before we need to begin the first categorical sampling of the year.

3. Content of routine annual reports

A report covers all sites with the same owner/cooperator. There are several double-site cooperators in the project.

Header with the project title and date.
Cover letter to the owner highlighting pesticide and metabolite detections in the last two years (or cumulatively). Assurance of confidentiality.
Aerial photo map of the owner property and vicinity. This locates and names all sampling points on the property. 2022 reports also included a soil map.
Table of sampling points with who owns them and their attributes such as depth.
Table of analytical results this year. The columns are samples, the rows are analytes sorted into groups field, ions, pesticides, and metabolites. Non-detects are omitted to shorten the table. The tables split sampling points up to fit the available width of pages; thus this “table” may contain more than one group of columns.
Table of analytical results in previous year. Non-detects are omitted.
Table of laboratory detection limits for pesticides and metabolites for past two years.
If the owner was involved in earlier projects, table of prior pesticide or metabolite detections, anions, cations if possible.
If the owner was involved in earlier projects, table of detection limits in earlier projects.
Project summary for all groundwater sites, cumulative. (Both categorical and long term.)
Closing page: atmosphere photos from the project that do not disclose any cooperator’s location or identity. Thanks statement. Confidentiality statement. Contact info for at least one project staff member who is the primary contact for this cooperator. Link to website for the project.

The 2023 reports are 8-10 letter-sized pages long when rendered to PDF or printed.

4. Building the report

For first generation reports about 2022 samples, we made a SQL query from the tabular database containing all analytical results for all sites, and exported the results to one Excel file. We added pivot tables and a pivot chart to the Excel file. Excel’s pivot table interface allows these to be drilled down to individual wells or to all sampling points at a site, or all sampling points at multiple sites if we group sites of the same owner for reporting. We then copied and pasted from the Excel pivot table into a MS Word file.

For 2023 reports we composed using the Quarto framework. Each report, which covers all sites reported to a single individual, has its own .qmd text file that mixes the Markdown language for structured text with chunks of in-line R language code that executes tabulation, mapping, and charting. The structured text can also refer to graphics image files, and include bibliographic items. “Rendering” the report executes the R code chunks and substitutes the execution result where the R language script appeared in the report .qmd file. The R code submits SQL queries to the project’s tabular database, or reads from a geographic data file, and transforms the retrieved data into a textual table or a graphical image that will appear in the rendered version. Rendering from the .qmd file also interprets Markdown directives, such as fonts, headings, and inclusion of graphics files and subsidiary .qmd files to create the whole report layout.

The “rendered” output format starts as HTML for previewing during report crafting, then proceeds to DOCX for subsequent editing in Microsoft Word. Finally, the DOCX is exported as PDF by Microsoft Word.

Each 2023 report began with a skeletal .qmd template from which the individual report was customized to a particular site or sites covered by the eventual site(s) report. The template “includes” (inserts using a Quarto directive) an R functions file, a report header .qmd file, tables explanation .qmd file, report trailer .qmd file, and all-sites summary .qmd file. Besides include directives, the report skeletal template file contains R initialization code, R function calls to generate a sites-specific sampling point map and table, R function calls to generate sites-specific tables of 2023 and 2022 analytical results and detection limits.

The 18 individual 2023 .qmd files will become starting points for the corresponding 2024 and 2025 .qmd files, rather than each site needing to go back to a bare skeleton.

While the DOCX instance is editable, all narrative content should be crafted in the .qmd file. Any editing done in the DOCX version of the report is lost if one returns to editing the .qmd file and re-renders using the Quarto app. The best workflow is to create all report content in the .qmd instance, while editing render it to HTML to bring in maps, tables, and pictures, and finally refine narrative in the .qmd. Editing in the DOCX should generally be restricted to fine tuning of layout after the substantive content is all present.

The DOCX rendered version contains a slightly different layout from the HTML rendered version. For example, page breaks are ignored in HTML, important in DOCX (and PDF).

Once the report layout is finalized as DOCX, it then can be exported to PDF. Thus there are three files per report: .qmd, .docx, and .pdf . There will also be transient files created such as .html.

Note: The reports about categorical sites are all confidential between Cornell and the site contact. Statements of confidentiality originate in the .qmd file thus carry into the .html, .docx, and .pdf unless deleted. It is acceptable for the recipient to share the reports; in such case we hope that the recipient will tell us that they did so because our contact information is in them.

graph TD;
E((Text<br>editor)) --> Q[.qmd file] --> Quarto((Quarto app<br>and<br>R interpreter)) --> R[Preview HTML<br>in browser]
Quarto --> D[DOCX file export] --> W((MS Word)) --> PDF[PDF output<br>for emailing<br>or printing]
P[Site photos<br>and other<br>graphics] --> Quarto
S[Shapefile of<br>all project<br>sampling points] --> Quarto
A[(ESRI public digital<br>aerial photos<br>repository)] --> Quarto
T[(Project<br>tabular<br>database)]  --> Quarto

R --> E

Generally the lead project staff member for the given site should lead the report creation for that site. Their name and contact info must be included in the report.

5. Transmitting reports to categorical owners/representatives

We snail mailed, emailed, or hand carried the 2022 and 2023 reports to each site contact. There were few surprising results thus there was not any cause yet for deep discussion.

Paper has been a preferred medium over electronic. The paper version carried to the first sampling of the following year can be valuable in discussion with the owner about interesting findings. I particular this can prompt an update about what pesticide products were used on the site in the prior year.

The first (2022) reports were split into two parts distributed separately, one containing maps and field+anion+cation results and another containing pesticide results. Around 10 of the 2023 reports had to be split similarly because the first sampling in 2024 preceded the lab’s return of the last batch of analytical results. These reports are better done once in complete form instead of being distributed repeatedly.

The 2023 reports conveyed results of additional pesticide analyte tests that the DEC lab did on all 2022 samples in late 2023. Thus the 2023 reports were effectively cumulative back to the first samples at the cooperator’s property. The 2024 reports to be prepared in 2025 may contain 2023 and 2024 data, or they may be cumulative.