What to do when you can never have enough standard reports

One of the most common questions that I see on online registration RFPs is, "How many standard reports do you have?" It's a question that begs for a misleading answer, because there is no "standard" for reports in event registration and no two vendors' answers are comparable.

(Caveat: After thousands of industry discussions, the Accepted Practices Exchange (APEX) has published a few report "standards" for specific areas of meeting planning, but few people use these without making at least minor adjustments for their specific business needs.)

Early on, we stopped trying to give customers a dozen or a hundred standard reports in Certain Registration and focused on providing a minimal set of reports "out of the box", while allowing users to copy and customize those reports in order to define their own standard reports. But even this hasn't been enough to meet our goal of "one-click reporting" for all customers.


Reporting in Excel With Online Data

Last year, I described my experience on-site at the National FFA Organization's 79th Annual Convention. The 80th Convention just ended, so last month it was time to update their on-site reports for this years' event. This process reminded me of some unique reporting that I did for FFA using Excel Web Queries.

While most business users are familiar with Excel spreadsheets, not too many people use its more advanced features such as Web Queries, Macros, and Pivot Tables. Web Queries allow you to pull data from any Web site address into an Excel spreadsheet. Pivot Tables allow you to take tabular data in a spreadsheet and summarize it by any column or row. And macros allow you to record a series of actions within the spreadsheet and then automate them in the future.

The FFA requires on-site reports beyond the capability of Certain Registration. In addition to pixel-specific formatting requirements, they have several "special rules", such as single-day registrants should count as one-half of a person when calculating the total registration numbers. In order to meet their needs, I started with an Excel spreadsheet, where I used a Web Query on one worksheet to extract raw data from the FFA event in Certain Registration.

I next added a worksheet that used a Pivot Table to summarize this raw data into a simple table.

Then, I used Excel formulas to apply "special rules" and to transfer the desired data to a pre-formatted worksheet suitable for printing.

And finally, I recorded an Excel macro and associated it with the FFA icon.

In the resulting Excel spreadsheet, when an FFA user who is logged into Certain Registration clicks on their logo on the first worksheet, the macro automatically runs the Web Query, refreshes the Pivot Tables, and updates the printable worksheet via its formulas. The user then clicks the print button and pulls the report off the printer.

Learn more

I'll leave it to the reader to decide if they are interested enough in this process to learn how to apply Web Queries, Pivot Tables, and Macros to their Excel spreadsheets. If so then Microsoft's help files and online resources provide ample instruction.