Geocortex Reporting offers a functionality to design and configure customizable pivot grids. This is an excellent way to organize complex data sets into an easy-to-understand tabular format. Not only are pivot grids a visually appealing way to organize data, but they also allow you to break down data into a format that is easier to work with.
This Geocortex Tech Tip provides an overview of how to create and configure pivot grids in Geocortex Reporting. Additionally, it demonstrates how to customize your pivot grids with the elements that will most benefit your specific user groups.
Video Transcription:
Hey everybody, my name’s Patrick Fingler. In this Tech Tip video, we’re going to look at how to configure pivot tables inside of Geocortex Reporting. Let’s dive in!
All right, so this is the report that we’re going to be building today. As you can see, this is a pivot grid, and it was built with Geocortex Reporting. There’s a couple of things to be aware of when you’re working with pivot grids.
The type of report that we’re building is a report that gives us a count of trees by region. We can see here that these are different regions in the city of Victoria and we’re getting counts of trees by the species of trees. We’re doing several different groups. We’re getting a count of the individual species of trees, but some species of trees are grouped by an overarching type or classification of trees. So, for example, an apple tree can have multiple different types of species of apple trees. Here we’re building this report for every region in Victoria and we’re determining the number of trees for that given region.
There is a little legend here at the top. I’ll walk through what each of these elements mean later on.
Something the pivot grid does well is these “Grand Total” calculations. It will automatically calculate totals both for the different rows and for the underlying columns. If we scroll down to the very end of the report, here I’m running it across the entire trees in our database, you can see that we’ve got some “Grand Totals” at the bottom as well. So, we can see the total number of trees in this “Burnside” region or the “Downtown” region. For example, there’s “996” trees “Downtown”. If you really wanted to, you could also go through and see for the “Downtown” region which trees are the most prominent or what the total number of species is. It’s a neat element.
Let’s dive in now and review this in action!
So again, the data that we’re working with here is a bunch of trees in Victoria. There are different regions in Victoria. There’s “Fernwood”, “Harris Green”, “Downtown”, “James Bay”. And this is the underlying data set. So, there’s a “Region” attribute which defines the location that the tree is in. There’s this “Common Name”, which is the type of tree. Over here is the underlying “Species” of trees. So again, for a given type of tree, there can be multiple species associated with it.
The data is in this nice tabular format. Let’s build a pivot grid component that works with this data. The first thing that we’re going to do is go to Geocortex Reporting. This is the completed report, but let’s first jump into this “Report1” tab here at the top.
Because the pivot grid component is quite large, I’ve adjusted my report template. I’m using a page size of about 20 inches long. The size needed depends on the type of report that you’re configuring, but in my example there’s a lot of data that we’re visualizing.
Let’s go and add a pivot grid. One thing to be aware of is that you’re not going to want to add a pivot grid to the “Detail1” band because the detail band repeats for every feature that you’re passing to your report. We want this to only appear once rather than in the pivot grid for every single feature. That would just make a very long report. So, let’s hide our detail band.
Here I’ve configured the “GroupHeader1”. This could be a report header. Our page header doesn’t really matter. But here let’s add our pivot grid component to the group header. I can give it a little bit of space if I want to. I could also make it fit the entire bounds of our container so that it’s nice and long. Now we can start to configure the pivot grid.
When you have your pivot grid selected, the first thing that you’re going to want to do is set a data source. You can see here on the right in the “Properties” menu, I’ve got the “Data Source” configured. Let’s set it to my “sqlDataSource1”. I’ll set my “Data Member” to “Trees”. Again, this data is coming from is the underlying data source connection in your report.
Once you’ve configured that, we can start grouping our data and adding some content to it. Let’s insert some data in our row. We’ll click on this “Insert Field in Row Area” button under “Actions” and add it where it says, “Add Row Fields Here”. Then we’ll select the new “PivotGridField1” and assign it to a field. Under “Properties” there’s this “Data Column Name”, we’ll set this to “species”.
So, we’re going to be creating a grid based on the species of the tree. I also want to group this by region though. So, if we select our pivot grid again, we can select this “Insert Field in the Column Area” option here in the “Properties” menu. You can see here that it’s been added as “PivotGridField2” to the top. Let’s select the “Data Column Name” here to be “region”.
These “Data Column Names” are attributes from my data source. “Region” is the region that the trees fall in, and “Species” is the species of tree.
If I were to try to run this, the regions and species would get populated, but we wouldn’t have any values that get displayed. So, for example here if I preview our pivot grid, you’ll see that it’s essentially an empty report. There are no values being included. Yes, it’s dynamically retrieving all the regions and all the different species of trees, but we need to define what data we want to be included in here.
To do that we’ll just click on our pivot grid again. We’ll click the “Insert Field in the Data Area” button. We now see “Data Area” in the “Area” field. We’ll set our “Data Column Name” here to be “species” and we’ll set the “Summary Type” to be “Count”.
Now when we preview our pivot grid it will have a count of the species within each region. So, here we can see there’s “149” of this “Acer campestre” species of tree in the “Burnside” region. On the right here we’ve got our “Grand Totals”. If we scroll to the bottom, we’d also have “Grand Totals” for everything there. This doesn’t look nice at the moment, but you can continue to stylize it.
Something to be aware of is that you can also add additional groups. We had all our species, but I might want to group these by common name as well. So, we could go into our pivot grid again, and we could add another option for our columns. Here I’ll insert another field in the row area. We’ll set the “Data Column Name” of this to be “species”. We’ll then set our original “species” column in the pivot grid to be “commonname” because it’s the parent group. We’ll preview this again.
You can adjust the sizing of each of these components in the pivot grid individually. You can see that some of the words are being cut off because they’re long names. We could increase the size of these.
You can see now that we’re grouping the species of trees. For example, we are grouping species of “Apple” trees and we’re also giving an “Apple Total” of the trees that are of the apple type.
You can continue to adjust this, but I think it’s probably worth jumping into the finished report now. We’ll click on this “Pivot Grid” tab at the top of the screen.
Here when we preview this, you’ll see I’ve added this legend at the top. This is to help you understand what each of the components in the report are. In the pivot grid properties, when it refers to a “Cell”, it’s referring to these individual cells which I’ve highlighted in light grey. Anytime it refers to “Total Cells”, it’s going to be a darker grey. “Grand Total Cells” are these blue items here on the right. “Field Headers” are green and are going to be up here on the left. “Field Values” are yellow and are going to be both the values on the rows and the values on the columns. Then there’s this “Field Value Grand Total” which is purple and is going to be this section here. If we scroll down, we’ve also got “Field Value Grand Total” at the bottom. The last section is the “Field Value Total” which is going to be assigned to the species’ total sections.
So, where do these legend elements come? If we go into Geocortex Reporting, you can define what elements you want to be visible. If I wanted to hide a certain element, I would be able to do that. Or if I wanted to hide or change the styling of the different elements, I could do that as well.
If we click on the pivot grid, you can see here there’s this “Styles” section in the “Properties” menu. Here I’ve created individual styles for each of the individual components. So, I’ve got a different style for “Cell”, a different style for the “Field Header” and a different style for the “Field Value Grand Total”. All these match-up to the elements in the legend. It’s a way to help you understand the different components of a pivot grid as you start working with them.
If you do want to hide different elements, there’s this “View Options” section here at the bottom of the “Properties” menu. If you want to hide an element, you can do that by going through and unchecking the box next to a particular element. So, maybe I don’t want to show the “Column Headers”, or I don’t want to show the “Data Headers” or “Row Headers”. Now when we preview this, I’ll have hidden those elements so that they’re not listed.
So again, the pivot grid is a very complex control, but hopefully this gives you an overview of how to design pivot grid tables using this nice legend at the top here. For me, when I’m working with pivot grids, I find that having something like this legend makes it a little bit more visual and easier to understand all the different components that are at play. So hopefully that helps. Bye for now!
Want to learn more about the capabilities of Geocortex Reporting? Click the button below for more information on what is possible with Geocortex Reporting.
Contributors