917.848.7284Data for Finance and Accounting

Top

Do You Know Your RQ?

Do You Know Your RQ?

“RQ” or “report to query ratio” is key to reporting success. When you know your ratio, you can determine the resources you need to build your reports.

Let me explain. Every report is built on two processes:

1) Extract the data from the database.

2) Present the data with appropriate formatting, sorting, summarization, etc.

If you’re one of the (lucky?) folks who have Crystal on your desktop, you may not see the distinction between these two processes when reporting. Crystal (and other tools, like SSRS) have nice screens that allow you to select your tables and immediately build your report, making the two processes seamless.

But once you get beyond straightforward reports, these visual tools are no longer sufficient. Also, when extraction and report logic are combined, it’s hard to figure out where your issues are.

Which is why we rarely start with tools when building reports for clients. Rather, we build queries over the database with SQL tools. We use both database specific tools (e.g. SSMS, Oracle SQL Developer or Toad) and generic tools (e.g. RazorSQL). We can’t format data with these tools, but that’s not the point. What we can do is dump data and load it into Excel to make sure we’re getting the right data. Once we know our fundamental data logic is sound, we can apply it to a report.

Now this is where RQ comes in. Sometimes we build a query, and it only powers one report, such as a report that reconciles inventory to general ledger at month end. Only one (or maybe two) accountants will use this report. So the RQ ratio is 1 to 1. When the RQ ratio is low, the person who developed the query can also probably build the report. Bringing in a separate tool expert (or what we like to call a “tool master”) isn’t very cost effective. Usually, the database expert (or “data master”) can do both tasks.

However, other queries can power dozens of reports. This is especially true with sales data. A query may join many tables with dozens of columns. Once that query is built, it can power many, many reports. In this case, the data master doesn’t want to spend the time to make sure everything is formatted just so for every end user. You may find this hard to believe, but I used to have the patience to sit and work on the details of various reports. But I don’t now.

When you have a high RQ ratio, you want to get a tool master involved. This person needs to understand the data but doesn’t need to understand all the ins and outs of SQL.

Often, if our clients our concerned about cost, we provide the data master and let our clients supply the tool master—because data masters are generally more expensive and harder to find. We generally don’t do it the other way around—where clients work on the query and we work on the reports. Too often, folks who think they understand queries and databases actually don’t. And no amount of formatting is going to fix that.

For more on data masters, tool masters and cost effective reporting, request a copy of our white paper, More Data, Less Cost: A Guide to Building Authoritative Reports from Your ERP System.

To speak to someone at Red Three Consulting, give us a call at 646-736-0060 or email us.

 

No Comments

Sorry, the comment form is closed at this time.