It is no secret that I’m a big fan of leveraging Backstop Excel Toolkit (ETK) and Report Builder to extract actionable insight out of Backstop CRM. Fresh data comes out on a silver platter with a simple mouse click; life can hardly get better than that.

It can actually get better than that! Let’s first run through a quick Pros & Cons analysis of this workflow.

Pros

On-demand access to fresh raw data to all end users with ETK installed
Division of labor between administrators/power users and end users
Opportunities to develop custom reporting and analytics within Excel

Cons

Report Builder lacks formatting options. Everything boringly comes out in alternate blue/white rows
Every single column used in the report, hidden or not, is displayed in ETK reports
Incapability to sort any column descendingly or by more than one column

Backstop provides comprehensive documentation and webinars on how best to build advanced custom reporting in Excel. I highly recommend watching those excellent webcasts run by Richard and Fidel.

So what are we doing here? This serves as a field guide for those Backstop practitioners in the wild. We will illustrate a use case by grabbing Meetings/Calls, format the raw data with Excel macros and Visual Basic Application (VBA), and most importantly have fun with it.

Are you buckled up?

The Plot

MOM Capital is a fictitious fund of funds (FOF). Eli, Jim, and Kenneth are in the midst of raising for MOM VIII Venture Fund. ACME Ventures and MOM Capital have been in talks past few months. Each interaction and meeting is meticulously logged in Backstop. We are to automate the generation of weekly meeting logs.

The raw data natively out of Backstop Report Builder, although complete, could be prettier. The ask is to

Add an eye-catching report title and current date stamp
Display dates in MMM-DD format (eg May-12)
Abbreviate attendee names with their initials
Abbreviate activity tags
Delineate past and future meetings with a subtle visual clue

The Excel file template is freely available on Github.

Technical Approach

At a high level, we build two Backstop reports, invoke VBA code to mash and format the raw data, and crank out a visually-appealing masterpiece. Easy peasy.

1. Build an ETK report to pull Meetings/Calls out of Backstop > _meetings
2. Build an ETK report to pull MOM Capital employees and their initials >_employees
3. Create a worksheet (aka tab) to house mappings between Activity Tags to display values >_tags
4. Create a worksheet to house the gorgeous and curated output >Meetings

Before deep-diving into any VBA code, be forewarned this might feel a little overwhelming and over-engineered. Well, we are planting the seeds for even more legendary scenarios in upcoming blog posts. Stay tuned!

Under the Hood

The code is a little long. A brief anatomy overview adds clarity. The subroutines and functions are broken down to maximize readability and maintainability. It isn’t as complex as it looks!

Parent	Children	Key Purpose
Main
	Initialize	Remove pre-existing content and formatting
	SetCopy	Copy raw data to destination worksheet
	SetShortHand	Set abbreviations for Activity Tags and Attendees
	SetColumnName	Replace column names
	SetTimeline	Delineate past and future meetings
	SetColumnFormat	Set column types, widths, and formatting options
	SetDeleteColumn	Remove unneeded columns exported forcefully inserted by ETK
	SetTitle	Add and format a title row
	SetColumnName	Replace column names
	SetPrintArea	Set print margins, orientation and scaling
Helper
	GetSheet	Return Excel Worksheet objects
	GetDictionaryTag	Return mappings of activity tags and display values
	GetDictonaryAttendee	Return mappings of employee names and initials
	GetDictionaryHeading	Return mappings of column headings
	GetDictionaryDelCol	Return mappings of columns to deleted
	GetActiveRange	Return non-blank cell range
	GetLastColumn	Return last non-blank column
	GetLastRow	Return last non-blank row

Main

The primary function that calls a number of subroutines and functions. This is wired to run every time ETK refreshes data.

Initialize

To ensure a clean slate, we remove pre-existing data and formatting options in the destination worksheet before each ETK refresh.

SetCopy

Clone raw data to the destination worksheet.

SetShortHand

Abbreviate values to enhance report readability based on a Dictionary object.

SetColumnName

Rename column names. This could also be achieved globally within Entity Definition Manager or Report Builder. The benefit of renaming it here leaves everything else intact, except this Excel file.

SetTimeline

Find the threshold between the latest past event and nearest future one, and insert a black line as a quick visual clue.

SetColumnFormat

Set numeric and date formatting options.

SetDeleteColumn

ETK returns both visible and hidden columns from the Report Builder. This function removes those columns not used in the final printable version.

SetTitle

Add a report title and date stamp.

SetPrintArea

Set printing options such as margins, orientation, scaling, and gridlines.

Helper Functions

These helper functions streamline the repetitive low-level tasks.

GetSheet

Instantiate and return a Worksheet object based on a friendly name.

GetDictionaryTag

Using a Dictionary object to map activity tags to display values.

GetDictionaryAttendee

Using a Dictionary object to map attendee names to initials. Instead of being hardcoded, initials are stored as Other ID and pulled in using ETK.

GetDictionaryHeading

Using a Dictionary object to map columns headings. We could also easily revise the column names in Report Builder. However, some occasions mandate consistent naming conventions; this gives us more flexibility.

GetDictionaryDelCol

Using a Dictionary object to map columns to deleted from the final output. I wish ETK does not dump all columns indiscriminately onto the report.

GetActiveRange

Identity the non-blank cell range (e.g. A1:K18)

GetLastColumn

Identity the last non-blank column (e.g. K).

GetLastRow

Identity the last non-blank row (e.g. 18).

Source Code

https://gist.github.com/klopmp/f00d85bbbbca4fb1bd6519f4f5701f39