I’d Like to, but it’s Complicated….Isn’t it?
Ok, so at this point we have all heard about the cool new report writing tool available in many Sage accounting solutions, called Business Intelligence. After all, reporting is the whole reason for collecting all this business critical information every day. Typing is tons of fun, but extracting the metrics to measure your business is much more valuable and BI can do this for you with little effort as we will demonstrate in this blog series.
We all have been waiting for someone to come up with an easy way to produce meaningful information, organized the way we want it, so we can make rapid decisions for our business, right? But, for many of us, Business Intelligence (BI) sort of exists over there in that pile of stuff that you passionately refer to as “Boy, I’d Love to Know How to Do That, but it Looks Hard and I Don’t Have the Time to Learn Something Complicated.” I shorten that all too familiar phrase down to “How Do I Get From Aaarrgh to Aaahh”
I’ve Heard That it is Hard
I understand that Thomas Edison found at least a thousand ways not to make a light bulb back in the 1870s, but after a few more tries, he finally got it right! He started at the beginning and worked his way up and never gave up. Well, creating a light bulb back in 1879 was quite hard, but learning to build reports with BI is really quite easy and can be a ton of fun!
OK, So How Do I Get Started?
The fastest way to build confidence and begin learning BI is to build a report from scratch using an existing container (a pre-organized set of Data Tables, Fields, and Expressions; Connected to a Data Source, like a Sage accounting program). First you need to get organized and gather a few goodies:
- A Report Layout > this is an actual picture of what the report should look like when completed; a spreadsheet works great for this
- Your BI Report Manager
- Microsoft Excel
- A reference book would be helpful; check out Excel 2010:The Missing Manual by Matthew MacDonald
- The Alchemex website
Ok, now you need to begin analyzing the layout. In report writing, a picture truly IS worth a thousand words. The layout will give you lots of tips on how to produce the report. By analyzing the layout you can determine the following key elements:
- Which data fields do I need; and in what order
- Are they calculated, or are they native to the data source (like Customer Name)
- What are the “groupings” so you can have subtotals (like all sales for a Sales Rep)
- What fields do you need to sort by
Once you have a good understanding of the fields and behaviors, your next step is to match that up to a container. For a detailed listing of all your existing containers and their content, go online to the Alchemex Community group for your Sage solution. For Peachtree (becoming Sage 50), you can navigate to http://community.alchemex.com/group/sagepeachtreebusinessintelligence. Once you get there, look for this file >> Sage Peachtree Business Intelligence Data Dictionary.xlsx Now that you know which container to use, you have mastered most of the hard part.
A Sales Comparison Report and its Benefits
A very handy report that can be built from scratch using an existing container is called Sales Comparison. The existing container that has all the necessary ingredients for this report is called Sales Analysis. This Sales Comparison report displays Customer Names by Category and lists Unit #Sales and Amount $Sales for any two user-selected date ranges in a nice side-by-side comparison. With this awesome report, you can compare any two periods that you choose and change it every time you run the report. Compare last month to the same month last year; Compare 4th Quarter to 3rd Quarter; Compare last YTD to this YTD, and so on. This is definitely a very powerful and flexible report that you can build yourself without ever setting foot in the Connector module or knowing any SQL at all! Easy, you bet!!
How About Dashboards
As an eye-catching, graphical display of your comparison data, this report will contain two extra sheets. One sheet; The Good Stuff, is a nice dashboard that displays the Top 5 Customers and Top 5 Items for each of the two user-selected periods. Another sheet; The Bad Stuff, will display the Bottom 5 Customers and Bottom 5 Items so as to identify those Customers and Items that may need to be addressed or possibly even discontinued.
Can I Really Do All This?
Yes, You Can! This report can easily be produced using the tools mentioned above. The mechanics of how to actually build this report will follow in Parts II & III of this 3-part blog series. Part II will include a step-by-step, easy to follow list of instructions that will help you build the main sheet with the two comparison tables. Part III will build upon your knowledge to produce two dashboards using pivot tables and pivot charts (don’t be scared, I’ll make it easy) for a really useful display of the top and bottom performers based on the user-selected date ranges. During the production of your Sales Comparison report, you will gain a lot of knowledge and concepts that can easily be transferred to other handy reports.
