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.
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
Contributors