=== Management Reporting ===

Are you operating a small business?

Many individuals start a business and at some point they become frustrated with the lack of concise data they need to operate their business. The sales team has difficulties tracking orders. The operations team has trouble finding a simple answer to a product cost question. The finance team has multiple reports, supposedly pulling data from one source, but producing different results. The CEO has a meeting with a major distributor, but can't consolidate the necessary data to produce a historical trend of products this partner has purchased in the past 2 years.

Small to mid-sized companies are flying blind with their reporting needs. Worse, many reports they use are inaccurate. At a company I recently worked for, one person they hired to head I.T. accepted a job for more money at another company, another was fired for viewing porn during office hours, and the person running the show today is more of a Network Administrator, than a Business Analyst.

Over time at this company, technology has evolved and each technologist has suggested the company use a new tool. A couple hundred Excel files here, a few custom Visual Basic utilities there, and two thousand Crystal Reports each "customized" by the end users and stored in a variety of locations. There exist pockets of data with no road map for guidance. A library of files with no index cards might be a good description.

I recently consulted at a company with the above problem. The solution was to take a step back and create a migration plan to stability and accuracy. I won't mention the company name for confidentiality purposes. Publicizing their dilemma to their customer base or competitors would likely hurt their business.

The Plan

We first outlined their existing system and data tables. The list included Great Plains accounting, MAX for ERP, an online CRM package, a custom package to handle their refunds written in Visual Basic (source code missing), an Engineering utility using Access 2000, and over 2,000 Crystal Reports scattered throughout their network. The Crystal Reports have accumulated over 5 years, each with a different naming convention, a different format, and many will lock-up when attempting to use as the original data source had been moved to new servers a year ago.

Step 1: To stabilize reporting, we created a SQL 2005 database titled "DataMart" which captures data from each of the various sources.

Step 2: We reviewed each of the 2,000 Crystal Reports we found on their servers and created a Library of one folder per department containing active reports in use. Additionally, we assigned a user name to each active report to assist us with understanding their content and purpose. This project boiled the 2,000 miscellaneous reports to 350 active reports. Meeting daily with a variety of individuals in the company became an entertaining task as many chuckled over coming to the realization that reports actually existed that they had been asking for. Speaking with new Senior Management, they didn't know they existed either.

Step 3: We "locked down" the reports by disabling the Write permission on the new Reports Library. Additionally, we added folder rights to only those authorized to view each department's folder. We also created a management "department" for the Senior Management team for financial reports. An individual could now view a report, make changes to a report, could create a local copy, but could not bastardize the original report and leave a mess for others to wade through.

Step 4: We created a font-end utility using Microsoft's Visual Studio 2008 product to simplify finding reports. Each department has a tab with a drop down tree for each report under a department. Each report now includes a selection criterion for date ranges and other field choices, which reduces the need for custom reports. All department tabs are password protected.

Step5: We ported each of the 350 active Crystal Reports over to SSRS (SQL Server Reporting Services) using Microsoft's Visual Studio SSRS product. The first release was initially met with skepticism, but quickly became a hit when individuals found reports to be accurate, consistent, and "customizable". They viewed using a drop down menu as a "customization" tool and loved the strategy.

Next Steps: The company needs to reclaim their data from the online CRM system they currently use. This is a leased system, billed monthly, and doesn't have export capabilities. Ouch! A risk management exposure if I even saw one. This was a cheap solution implemented by a shortsighted CFO a couple years ago. The company also will need to evaluate a new ERP package to better manage their business and accommodate their growth. When new systems are installed, the reporting system remains the same (no additional employee training needed) and we will only need to map the data to the "DataMart" tables behind the scenese. From there, the SSRS will grab the data as it does today.

The Results

Management is able to pull reports with confidence knowing their data is securely stored. As important, the reporting system is using verified reports for monthly metrics and target assessment across all functions. The 2,000 original Crystal Reports are still accessible in a secure folder for reference purpose. We convert 1-2 old reports a month to the new system from an occasional excited employee who asks, "Hey, what happened to my old Crystal report?" This happens rarely these days as the system has been in place for just over a year now.