AEC Custom Reporting

We've all been in that frustrating situation where we need to create a report to share with stakeholders and don't have an easy, efficient way to get it done. Maybe our data isn't accessible in an app, like Survey123, that has built-in reporting capabilities or maybe our department has a small budget and doesn't have room to invest in a shiny new third party application.

What are some alternative methods for creating repeatable, scalable reports from our data, avoiding the laborious and monotonous task of creating a report from scratch each time. Aside from downloading a copy of the data and manipulating it manually, wouldn't it be great to automate report generation from any feature service?

In this tutorial we'll do just that! We'll walk through the process of using a  Jupyter Notebook  that will leverage the  ArcGIS for Python API  to grab a Feature Service to create a custom report from. We'll also work with  python-docx , a Python library for creating and manipulating Microsoft Word files, to automate the report creation process.

Once completed, you'll have the understanding of some basic concepts that will allow you to set up your own Notebook(s) for automated report creation.

Concepts to be covered:

1. Cloning your default environment in Pro

2. Installing additional Python packages 

3. Connecting to your Portal to access the data with the Python API

4. Setting up a Report Template in Word

5. Run Sample Notebook to produce your first report

6. Modify the Word template and Notebook to include additional data from Feature Service

First, you'll create a new conda environment in order to install the Python library you'll use for creating and updating Microsoft Word (.docx) files. ArcGIS Pro includes a default  conda environment , arcgispro-py3. The default conda environment includes several common packages, like ArcPy, SciPy, NumPy, and Pandas, among others but is lacking the one we need. Since the default Python environment can't be modified, we'll need to clone it first in order to add any additional packages.

Open up Pro and follow along to create a new conda environment by cloning an existing environment using the Package Manager page in ArcGIS Pro.

Be sure to name your new environment something that will be easily remembered and distinguishable - i.e. "arcgispro-py3-docx"

With the new environment created, let's install the python-docx package we'll need to access the Word template and create each report.

Navigate to your start menu and open the 'Python Command Prompt' found under the ArcGIS folder:

Once the prompt is open, check it is referencing the new environment, arcgispro-py3-docx, created in the previous steps and enter the following commands in succession:

pip install python-docx

pip install docxtpl

Close out of prompt when complete.

Now that our environment has been properly setup and configured, let's download the sample data and create our first report!

 _{Download 'Report Template' to your local "Downloads" folder on your computer} 

In this example, we're going to be creating a simple daily weather report, for a specific location, using data from a Feature Layer that can be found in the Living Atlas.

Feel free to explore the data we'll be using in our example report. The Notebook is already hard wired to access this data but it will be good to get familiar with the schema as we'll be referencing it later on.

The report template we'll be using should look very familiar if you've ever worked with  Feature Report templates  from Survey123. If not, then it's essentially a Microsoft Word document with some keywords referencing field values from a layer. These keywords will ultimately be replaced with the data from the record(s), contained within the layer, you want to report on. You can still put all your Microsoft Word tricks to work to ensure your report looks exactly as you want and has that custom "feel". So in this case the template is being provided for you but you will quickly see how easy it is to create and use your own.

Feel free to take some time and open the report template document (.docx) you downloaded and explore.

Before running our Notebook and creating a report using the provided template, let's take a moment to look it over and become familiarized with what's happening. This way you can feel more comfortable modifying it and making your own changes if you'd like.

Now that we've set up our environment, downloaded our sample Notebook with report template, and gained a basic understanding of what it's doing, let's run it and create our first report!

With Pro still open make sure you're positioned on the first cell of the Notebook and click 'run'. Do this for all cells contained within the Notebook in till you reach the bottom and the messages "Report document export complete.." and "Check your 'Downloads' folder..." are displayed. Then check your 'Downloads' folder to view your report with data from the Grand Rapids, MI station.

In the previous example, a single report was created for a single station. But what if I wanted to create multiple reports for multiple stations?! How would I go about modifying the existing Notebook to create multiple reports at once? Let's take a look!

With the 'Demo Report Notebook' still open, navigate to the 4th cell in order to modify the current SQL statement.

We're going to modify it to return multiple records using a more complex statement. Copy and replace the current statement with the one below:

where_filter = "COUNTRY LIKE '%Michigan, United States Of America%' And OBS_DATETIME >= timestamp '2022-11-16 18:56:00' "

This statement will now return any records that:

At the time of publishing '2022-11-16 00:00:00' was used but feel free to change the date to reflect a date closer to the current date of running through this tutorial as to not create too many reports

1. Contain the text - 'Michigan, United States Of America' within the "COUNTRY" field
2. AND fall on or after the date - '2022-11-16 00:00:00' within the "OBS_DATTETIME" field

While we're here let's also modify the fields being returned from the service to include an additional field, "R_HUMIDITY". Copy and replace the current statement with the one below:

out_fields = ["ICAO", "OBS_DATETIME", "STATION_NAME", "ELEVATION", "WIND_DIRECT", "WIND_SPEED", "TEMP", "SKY_CONDTN", "VISIBILITY", "WEATHER", "LONGITUDE", "LATITUDE", "R_HUMIDITY"]

Before moving on, let's modify the current report template to include a new keyword for the additional field we just added to our return. With the document open, append the following to the current line for temperature in the report template:

 with Relative Humidity at {{R_HUMIDITY}} %

Moving back to the Notebook, navigate to the 5th cell in order to modify the code to work with multiple records. Copy and replace the current cell with the code snippet here:

That's it! Now you can save and re-run the Notebook. Check your downloads folder to see all the reports that were generated, including the new value for Relative Humidity.

Download the modified Notebook and template here:

Now you can modify this and rewire it to work with any Feature Service to create any report. Happy creating!