How to Create a Meaningful Report or Analytic | CLEARIFY

The process is not a blank canvas

After speaking before thousands of inquisitive minds, and talking with thousands of customers, there are only two things I can say with 100% certainty:

• Everybody uses their application differently, e.g. QuickBooks
• Everybody looks at their data differently

And after every product rollout, or marketing promise made by some Business Intelligence Application, invariably the question arises "Is this the one tool I can use for everything?".  Of course, the answer is always no, but no such tool exists - and won't for many, many years.

If there were a perfect world, your Business Intelligence Application would be a blank canvas, and you would just place fields on it in any fashion you wanted, and viola! instant analytics!

But there are three big problems.

The first problem is the underlying data.  In order to get information that helps you run your business, the following premises have to be met.

• The data has to be accurate
• The data has to be consistent
• The data entry procedures have to be controlled

Let's look at a simple example. If a data field stands for "yes" or "no", it has to be consistent e.g. is it "N" or "no" or "NO"?  A more complicated example would be how somebody might define "machinery" for job costing.  Is it an account?  multiple accounts?  A particular item?  used in the description of an item?  Is the item unique?

Granted, once you nail the definition down, you can graduate to the analytic creation, but there are usually more than just one of these "gotchas" when creating a report or analysis.

The second problem is how the data pieces are all tied together.  This is more a matter of benign ignorance than anything else, yet most Business Intelligence or Reporting Tools expect you to be more knowledgeable about data and warehousing than you should ever be.

Data modeling requires an intricate knowledge of the underlying database, and even for people who work in those databases, it is a difficult undertaking - never mind the poor end user.

A good example of this would be Time Tracking and Payroll in QuickBooks.  Since users can create a Payroll from the Time Tracking activity, many expect that there is link between the two transactions; but there isn't.  First, you can change - after the fact - either the Paycheck or Time Tracking entry, without changing to other.  Second QuickBooks doesn't store any link.

Now, let's be realistic - how many QuickBooks users would know that?  Not many.

The third problem is that some people want to see the traditional rows and reports, others want ot analyze data on the fly, and still others just want a simple piece of visualization on their mobile device.

Again there is no one tool that can do all of these things.  Crystal Reports is still the king of reporting writing for pinpoint layers - even after 30 years.  Nothing beats Excel PivotTables for on the fly analysis, and tools like Tableau and Microsoft PowerBI take visualization to a whole new level.

So our main takeaway becomes:

Reporting is more of an art than it is a science

QQube takes care of the first two problems, and creates the necessary data modeling for you, so that all you have to focus on, is the actual report creation.  It also assists with the third problem, because the same data can be used out of the box for any type of application, whether it is a traditional report writer or visualization tool.

Of course, you will still be disappointed that your employees have been entering data in a particular field in all sorts of ways, and that Payroll and Time Tracking are not tied together - but your expectations will be enhanced because of your education.

Taking your expectations and putting QQube to work

Over the past two decades CLEARIFY® and its Solution Provider network have created thousands of custom reports, and have used the following guidelines for success:

1. Have a picture.  This is the most important piece of the puzzle to avoid scope creep, and problems down the road. Lay it out column by column.
2. Establish the level of needed detail.  If you are the boss - you may only want to see the top level figures.  If you are the bookkeeper - you may need more detail.
3. Determine skill level of end user. Creating a PowerPivot is great for providing multiple options for looking at your data, however, the boss might want to just 'press a button'.  
4. Define each column thoroughly. Make sure there is a CLEAR understanding of the data source for each column - whether it is a QQube column or calculated column.
5. Specify groupings.   Do you want to group by item, by customer?  or by customer, job, class, then item.  Grouping is NOT sorting; rather it is a way of providing the ability to subtotal or segment your data.
6. Decide on the sorting order.  Groups will sort alphabetically by default.  Determine if you need a different order to your grouping.  Details can be sorted based upon any column, or sequence of columns.
7. Choose the right analytic.  Decide which QQube Analytic is necessary to get your results. For instance if you use items and estimates for job costing, you would use the Job Cost Analytic, if you are using Job Budgets then you would use Profit an Loss by Job Analytic.    Use this page as a guideline to data model schemas.
8. Pick the correct reporting tool.  Excel is not always the best choice for creating reports.  If you need pinpoint layouts that are not 'column by column' then you need to consider something like Crystal Reports. If you are comfortable using Access, then start with the pre-configured examples in QQube.
9. Apply proper visual designs.  Traditional Rows and Columns are easy to spruce up. Deciding whether you should use a pie chart or line graph with the proper coloring is more difficult. We recommend reading Information Dashboard Design by Stephen Few.
10. Create a draft.  Create your rough layout first, so you can determine what space you need for columns, and how you want things to appear on a page.  You can always tweak your formulas later.
11. Troubleshoot.  If you compare you report to your application e.g. QuickBooks to make verify your report info, and find errors, look at single transactions first.  Finding a particular instance of an issue, is easier to locate and fix than approaching the whole issue at once.
12. Document your process.  Invariably someone will run a report you create down the road, and the results will be perplexing.  After you have ascertained that the QQube data is updating properly, then you have to look at how they run the report, and lastly determine if the information is being entered in your application as you *thought* it should have.  We have found over the years, that entering data into your application in a certain matter will dictate the final outcome.