Extreme Reporting for Non-Profits with Conga Composer

Non-profit reporting can come with stringent standards, almost to the level that financial services firms see with the SEC, to ensure that work is being performed to benefit a cause or community or to meet the guidlines of a grant agreement. For this post, we’ll take a look at a reporting requirement that Community Environmental Center brought to us, and how we were able to leverage an AppExchange product and cross-object workflow to satisfy their requirements. Before we dive into the problem and solution, a little background: Community Environmental Center (CEC) provides consulting and construction services for low- and middle-income homes in New York City, to assist with energy efficiency and green building initiatives, and use Salesforce to track their projects, crews, and construction requests.

Large Report = Lots of Data

As part of our requirements meeting, we received a report template that we needed to effectively reproduce with live data from Salesforce spanning Account records and 3 related custom objects. Every Account record has Equipment, Tests on Equipment, and Workscopes; furthermore, each Account record and related data must be its own line of an Excel file. The primary goals of this report are to capture the following:

• General Account data (e.g. address information)
• Equipment specifics (e.g. make/model of a heater, size of an oil tank)
• Test metrics (e.g. CO2 leakage, temperature readings)
• Work performed (e.g. installation of programmable thermostats, insulation)

Every Account can have many types of equipment, like heating systems, water heaters, and central air units. Some Accounts only have one of each, other Accounts have multiple. For every equipment record, there are multiple tests, one performed before construction and one performed afterwards. Additionally, the picklist on the Workscope object to describe work performed is different than the report template. Some picklist values have a one-to-one relationship to a column in the file, while others are grouped together to satisfy a single column.

Conga Composer: The Aggressive Aggregator

CEC had previously installed Conga Composer when working with us for their Salesforce implementation and was interested in using it again for this project. Conga Composer by AppExtremes is an AppExchange product that creates documents based on file templates and Salesforce reports, allowing for beautiful contracts or stunning sales reports that fall outside of the standard Salesforce look and feel. Upon clicking a custom link or button, Conga Composer runs a series of connected Salesforce reports and populates data based on syntax in the target template file.

Above is the code for the custom button that generates the Conga Composer merge window. Notice that before the report IDs are square braces; the text between these names the sheet in the resulting data workbook.

Our template file contains a sheet for each Salesforce report and a “master” sheet that looks like the report template that we received as the requirement. Excel’s VLOOKUP() function matches Account IDs from the master sheet to the source sheets and pulls data onto this sheet from all of the other sheets. Essentially, Conga Composer does the heavy lifting of getting all the data into one file, and Excel does the pulling from within to get the data to the right cells.

Cross-Object Workflow to Aggregate Related Data

Aside from transposing and reorganizing related records, the requirement we found most demanding of attention was the Workscope picklist values. Workscopes have approximately hundreds of different picklist values, some of which would be aggregated into one column in the template file. When reporting on a list of all Accounts with Workscope records, there is an Account ID and picklist field, and unfortunately Excel does not have a contingent VLOOKUP() function.

To workaround this, we use the Spring 12 cross-object workflow on the Workscope object. Whenever a record is created or edited and the picklist matches a set of values, a field update is triggered that updates a checkbox on the Account record (corresponding to the column in the report template). This allows for a much easier reporting scheme as all Workscopes are easily seen in one Salesforce report, and can be matched with simple VLOOKUP() functionality in Excel.

&=&=IF(ISERROR(VLOOKUP($B{r},Report7!$A:$E,5,FALSE)),"",VLOOKUP($B{r},Report7!$A:$E,5,FALSE)/100)

This formula in Excel checks to see if the value from column B appears in the Report7 tab. If it does, it returns the value in the 5th column of that row. The ISERROR() and IF() ensures that the merge report looks nice and error-free. Also, note the &=&= at the start: this allows for Conga to copy the formula down for as many rows as there are data.

Creating Efficiency with Force.com

CEC presented us this challenge before they needed to run the report for the first time, however it is important to recognize the benefit to the organization if we were unable to architect this solution. Through simple reporting, this would require a great amount of manual entry and referring to reports, where an individual Account record would take approximately 15 minutes to process. Using a new Spring ‘12 feature and a great AppExchange product, this report can be generated for all account records in 32 seconds.

If you have an interesting or challenging reporting question, or a neat story about one that you did, feel free to leave a comment on our Facebook page at http://www.facebook.com/ArkusInc, below via Disqus, or directly to me via Twitter @RogerMitchell.