Inside Foundant – the Blog

Adventures in Reporting

As the Lead Software Engineer for Foundant, I’m pleased that the release of Foundant Grant Lifecycle Manager (GLM) 2.1 and the addition of new question types to evaluation forms brought about a long overdue reworking of a lot of data and its core model in the deep nether regions of GLM. These changes and additions are complex and involved, but also incredibly helpful in simplifying our model and making future work in report fields and other raw data related functionalities much easier. Even with these updates, there are still some very complicated and unique problems that we have when trying to do things like reporting off of our data model.

For anyone who has done any reporting or data work with excel before, you are probably pretty familiar with how aggregation of columns, grouping and sorting generally work in the context of a few tables of data. Normally a report starts out with grabbing the main data table you want to show data from, joining a few tables that contain related details and choosing groups and some aggregates.

Some of the data in GLM works in much the same way including organizations and grant and payment data, but much of the data in the system is related to Form Submissions and their Report Fields. This data poses a very unique problem for reporting because there are no set columns to use for joining together other tables of related data. Every “column” in every client’s form data is completely customizable in name and content as are the Report Fields related to them, so standard reporting tools have a lot of trouble handling this type of data. For this reason we have spent a great deal of time looking to find ways to simplify this problem and help reporting software figure out how to deal with our complex data.

Our first attempt at easing data access for reporting was in the use of “system” Report Fields. These are the 9 most common default fields that come with every instance of GLM that we assumed we could be conceptually sure of the content of their data. For instance it was assumed that the Geographic Area field would always include data that could be used for grouping reports into regions geographically. This assumption turned out to be incorrect though, as many clients chose to rename many of these “system” fields to conceptually unrelated data instead of adding more Report Fields. This use of the system fields and the need for reporting on other custom Report Fields made the system field concept unreliable and unable to be used by reporting in the way we intended.

Since the attempts to make the data easier for reporting tools to consume were not cutting it, we had to change our mindset and look for a more complex and sophisticated reporting tool that could handle the kind of data we need to throw at it. None of the tools we looked at can support our complex data needs out of the box, but a few of them have enough customization capability that we can make them do what we need with a little extra work. This extra work is the focus for development for the next release of GLM and should jump reporting up to one of the true strengths of GLM.