“It’s not you, it’s me.”
“I did not have sexual relations with that woman, Ms. Lewinsky.”
“Your end users will be able to write their own reports.”
It’s the biggest lie in the world of software. You hear it when demoing ANY report writing tool. “Your end users will be able to write their own reports.” And like any great lie, you believe it because it contains a core grain of truth – and that grain of truth is that reporting tools like Crystal actually are relatively simple to learn. If you’re proficient with Excel you can probably master Crystal with a little effort. But that doesn’t mean that your end users will be able to write their own reports because the challenging part is not the tool itself, it’s the data.
Data can be complex for many reasons, so to hide that complexity we often make use of views. With a view the Data Master* does the work ahead of time, giving the end user a much easier “virtual table” to use. There are many reasons to use views, including:
– Joining tables correctly
– Relabeling data
– Summarizing tables
– Avoiding nulls
– Calculating data ahead of time
– Controlling and Centralizing complexity
I’ll review all these advantages (and more) in future posts but today we’ll start with the first two – joining tables and relabeling data.
Here’s an example:
We’re writing a financial report, and for that report we may need as many as four different tables to get all the information we want.
We have a transaction that is posted to Company 2, Profit Center 123, Account 4000. If we have multiple legal entities we will want the name of the company involved. Then we want the name of the Profit Center – we want to know that 123 stands for Northeast. We may start with the basic transaction that was posted, but then we also want the name of the Account – 4000 is not enough for the report, we need to know that 4000 stands for Sales. So to write the report, we need at least four tables – and in large systems we often need a lot more.
If I’m an end user, I can use Crystal to join these tables and then write my reports. But this presents a couple potential problems:
1. If you’re not a Data Master, it’s easy to make a mistake connecting the files. If you have five reports, you wind up doing the same joins multiple times. Not only does that waste time, it means that if something changes you have to make the changes manually.
2. The other big problem involves the fields within the tables themselves. In a complex accounting system you’ll see more than one amount. If, for example, you have a UK subsidiary every transaction may have a value for the transaction in Euros, another value for GBP for the financial statement, and yet another value in USD for the worldwide consolidation. It’s not always easy to pick the right one.
So rather than having to work with four separate tables and lot of different amounts, like this:
they might see a single table, like this:
By using views you gain two big advantages:
1. The end user doesn’t have to worry about the tables involved – they just have one table to work with. Also, the virtual table is designed just for them. The accounting system may be designed to work with dozens of currencies, but at this company GBP and USD are always important so we can always label them specifically.
2. Once this view is created it can be easily reused in multiple reports. We test once and know that our data is fine, so when problems arise with individual reports we don’t have to start by examining the data.
* For a full rundown of the three different “Masters” you need to create perfect reports, CLICK HERE.