Generating a report with sub reports from external data sources [Geocortex Tech Tip] - Geocortex

Generating a report with sub reports from external data sources [Geocortex Tech Tip]

Ryan Cooney
Ryan Cooney
Product Owner
February 22, 2019

By leveraging third party APIs from non-GIS business systems like asset management, business intelligence and enterprise resource planning, Geocortex Reporting 5 unifies these sources together and puts you in a position to choose only the data you want to display to your end-users.

In this week’s Geocortex Tech Tip, we look under the hood of sub reports, and how they can be used to extract data from external sources. Using an example of an ArcGIS feature layer that displays police vehicle locations and an external database showing dispatching information for those same vehicles, we’ll show you how you can combine the two into a powerful, intuitive report that contains information from both sources.

Watch on YouTube.

Video Transcript

“Hi, my name’s Ryan and I work on the Geocortex Reporting team. Today, I’d like to show you how you can author reports that uses sub reports to pull in data from external data sources. Let’s see how that’s done!

Let’s start by understanding the data that we’re going to be working with today.

So, I have an ArcGIS feature layer that represents police vehicle locations. We can see here, the select feature, it’s got its some attributes available to us that just represent general information about that vehicle. I also have an external database – this a postgres database, in this case that represents.

I have a table here with dispatch information for those police vehicles, so two totally separate data sources, but they are linkable, there is a relationship between the unit name field here. I mean this table and the unit field of the ArcGIS data.

We’re able to establish a relationship and the goal is to create a report that presents this basic vehicle information, followed by a summary of the dispatch information for that specific vehicle.

I’m going to start by creating a simple layer report, I’m just going to use the layer wizard.

I’ve set up these data sources ahead of time, so I can pick up on my emergency vehicles data source with the police layer and when I complete that, I get a basic report laid out for me and set the title.

I’m going to bring in that vehicle data. I should be able to preview this. It’s going to want to know that the object ID or feature ID of one of those police vehicles. I grabbed one here, and we do this just make sure everything’s working as expected. We can see that I’ve got a vehicle information showing up when I pass in a specific feature ID.

Now, to get to the interesting part where we’re going to want to bring in our dispatch history.

I’ll just add in a label for that section of the report. We’re going to want repeated records for every dispatch record for just the vehicle with this unit name.

To do this, we’re going to use a sub report. I’m going to drag in a sub report control and in my case, I want to make it the full width of the report. I need to take up all the space and the sub report- it’s going to define its own query to the data. I double click the sub report to get in here and basically, I’ve got a whole new report that I can run on my own.

This report needs a data source. I’m going to go ahead and click that data source button. I select my postgres database.

I’m going to select the dispatch table and just like I did for the layer information, I’m just going to bring all the dispatch records in. I don’t need all this extra white space.

At this point, it is a good idea to test this as well, if I preview when I’m inside a sub report, it’s only running the sub report query. We can see that we’ve got a row for every record that was in that table.

Now, we don’t want a row for every record that’s in the table, we want just the rows that apply to our particular vehicle or unit they were running.

What we want to do, much like our top-level report, we want to add a parameter. I go to the field list here and I add a perimeter. I’m going to call this parameter ‘unit,’ type is string.

Now, I’m going to modify the query that’s used on this dispatch table to use that parameter.

I select the query Builder and, in this case, it’s a filter that I’m applying. I’m going to add a condition to that filter and basically, I want where the unit name field is equal to a parameter.

It doesn’t actually matter what you call the parameter there. When I hit next, it’s now going to ask where that unit parameter is coming from. This is sort of the interesting bit- we wanted to come from an expression and we want that expression to pull the value of the unit parameter that is the input to this whole sub report.

Now, when I preview, it’s going to be asking me for a unit and if I want D143, I should get just the sub report. We can see that all the values are just from D143.

What we’re going to do is go back to the top-level report and tell this sub report how to link this unit named layer field into the sub report.

I’m going to switch to the property editor.

This a sub report under the data tab there’s a parameter binding section and what I’m going to do is, I’m going to take that parameter unit that we created on the sub report is available to us. I want to bind that to my police vehicle data and specifically the unit name field. If I do that, I should be able to preview. I need to pick that object ID again, and there we have it.

I’ve got my high-level vehicle layer information, this is for Unit D 152, I’ve got my dispatch history section starting, I’ve got repeated records and for each record, that history table. We can see if we’re only getting the D 152 values here.

That’s exactly what we wanted from this point, that’s a lot of formatting to get this to look the way that we want. We can make it pretty, but this is the general way that we can take layer data relate it to external SQL data in a report and we’re using sub reports to do that.”

Find out more about how Gecortex Reporting 5 brings together key data sources to simplify and enhance your reporting capabilities

Discover Geocortex Reporting 5


Categories:
Archive
Open: 2019
2019
March 22, 2019

Displaying data from SQL Database when you select features [Geocortex Tech Tip]

March 20, 2019

How Geocortex users are enriching the communities they serve

March 15, 2019

How to Configure a Geocortex Essentials layer report with Geocortex Reporting 5 [Geocortex Tech Tip]

March 14, 2019

Region of Waterloo: How GIS & Geocortex technology helped meet local Source Protection Plan requirements

March 6, 2019

Integrating Business Intelligence and Data Sources with Geocortex Essentials [Webinar]

March 1, 2019

How to quickly add attachments to new features when editing [Geocortex Tech Tip]

February 22, 2019

Generating a report with sub reports from external data sources [Geocortex Tech Tip]

February 20, 2019

Geocortex Essentials 4.11: Saving Time & Increasing Efficiency

February 15, 2019

Accessing Samples and Building your First Workflow [Geocortex Tech Tip]

February 12, 2019

Open Beta of Geocortex Printing 5 Has Begun

February 8, 2019

Different Ways of Creating Selections of Features in Geocortex Viewer for HTML 5 [Geocortex Tech Tip]

February 6, 2019

4 Best Practices to Follow When Using Geocortex Workflow 5

February 1, 2019

Creating a Report that Displays a Screenshot of the Map [Geocortex Tech Tip]

January 28, 2019

Diving Into Geocortex Reporting 5 [Geocortex Tech Tip]

January 24, 2019

How Geocortex is Advancing Water Management Solutions

January 16, 2019

Creating Custom User Experiences with Geocortex Workflow 5 [Webinar]

January 11, 2019

Showing the search results using the item picker in Geocortex Workflow 5 [Geocortex Tech Tip]

January 9, 2019

City of Troy: Using GIS and asset management technologies to manage legislative requirements

January 4, 2019

Using scripting to put attachments in reports in Geocortex Reporting 5 [Geocortex Tech Tip]

Open: 2018
2018
December 21, 2018

Integrating Pictometry, Bing and other 3rd party maps within your Geocortex applications

December 19, 2018

Recapping the Texas Geocortex Regional User Group

December 14, 2018

How to use Geocortex Workflow 5 to populate the Attribute Table in Web AppBuilder for ArcGIS [Geocortex Tech Tip]

December 12, 2018

Finding yourself: Using geolocation in mobile and web applications [Webinar]

December 7, 2018

Enabling real-time user-to-user map collaboration within Geocortex Essentials

December 5, 2018

Reflecting on 2018

November 30, 2018

Creating mailing labels with reports with Geocortex Reporting 5 [Geocortex Tech Tip]

November 29, 2018

Alberta Energy Regulator: Supporting safe and responsible energy resource operations

November 26, 2018

Important update for ArcGIS and Transport Layer Security (TLS) Protocol Support!

November 23, 2018

Using fine-grained security to control access to layers, features, attributes and application functionality [Geocortex Tech Tip]

November 21, 2018

Enhancing Web AppBuilder for ArcGIS® with Geocortex Reporting [Webinar]

November 16, 2018

Using Geocortex Workflow 5 to automatically display a form for editing layer attributes [Geocortex Tech Tip]

November 15, 2018

Geocortex Achieves Esri’s “Release Ready Specialty” Designation

November 14, 2018

Celebrating GIS Day with York Regional Police: How GIS Data Has Mitigated Risk & Increased Efficiency in Crime Prevention

November 9, 2018

How to access a SQL database from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

November 2, 2018

How to send an email from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

October 26, 2018

How to add and configure charts inside reports with Geocortex Reporting 5 [Geocortex Tech Tip]

October 24, 2018

Geocortex Water Webinar Series

October 19, 2018

Integrating Geocortex Essentials with ArcGIS Online and ArcGIS Enterprise portal [Geocortex Tech Tip]

October 17, 2018

Cross-Platform Development with Xamarin [Webinar]

October 12, 2018

City of Fort Collins: Dynamic flood maps for public awareness, and flood insurance rate saving

October 2, 2018

Technology Q&A: Geocortex Workflow 5 “Behind the Firewall”

June 27, 2018

Geocortex Essentials 4.10 is here!

June 12, 2018

Geocortex and the GDPR

June 6, 2018

Geocortex at the 2018 Esri User Conference

May 15, 2018

Technology Q&A: The evolution of Geocortex and Web AppBuilder for ArcGIS

April 25, 2018

How to manage data collected from Geocortex Workflow 5 forms [Geocortex Tech Tip]

April 18, 2018

Delivering accessible mapping applications for everyone [Geocortex Tech Tip]

April 11, 2018

How to search for data in a non-spatial database [Geocortex Tech Tip]

April 5, 2018

Configuring Geocortex Analytics to monitor a new Portal for ArcGIS instance [Geocortex Tech Tip]

March 27, 2018

Getting started with forms in Geocortex Workflow 5 [Geocortex Tech Tip]

March 21, 2018

Using Geocortex Workflow with Web AppBuilder for ArcGIS [Geocortex Tech Tip]

March 15, 2018

Understanding tool usage in your GIS applications [Geocortex Tech Tip]

March 7, 2018

Running Geocortex Essentials workflows from an identify operation [Geocortex Tech Tip]

March 2, 2018

GIS Health Assessment: A new way to think about your system

February 28, 2018

Using the in-app help system in Geocortex Workflow 5 [Geocortex Tech Tip]

February 21, 2018

How to configure a personalized dashboard in Geocortex Analytics [Geocortex Tech Tip]

January 25, 2018

GIS is shifting to SaaS, and it’s a win for everyone


Categories

Popular Tags
Accessibility Active Operating Picture ArcGIS ArcGIS Online Customer Story Data Models Energy Esri Geocortex Geocortex 5-Series Geocortex Analytics Geocortex Essentials geocortex integrations Geocortex Mobile Viewer Geocortex Printing 5 Geocortex Reporting Geocortex Reporting 5 geocortex tech tip Geocortex Tech Tips Geocortex Viewer for HTML5 Geocortex Web Viewer Geocortex Workflow Geocortex Workflow 5 GIS GIS Day GIS Health gis integrations Integrations Oil & Gas Pipelines Portal for ArcGIS SaaS Tech Tip Tech Tips Transportation Water Industry Web AppBuilder Web GIS Xamarin

Contributors