Joining the Evil Empire – Recommending Microsoft SSRS

I have sold my soul. I have crossed over – joined the dark side. I have started recommending a Microsoft tool.

I cannot tell you how much this disturbs me. I started my development life on Unix systems. I know how to chmod, chown, ls ‘lt, even ps ?ef | grep ajacobso | 2>&1 | more for crying out loud. I have years of experience with IBMs midrange systems, also known as AS400. I can wrkqry, strdpm, wrkacjob. I like good old fashioned text based interfaces. Mice are for wimps. I’ve made jokes for years about the need to reboot Windows server – some of which can still get a laugh. I talk about the annoying Microsoft tax where people have to spend hundreds of dollars on Office for every PC they buy.

But nonetheless, I made a deal with the devil.

The reason is simple: SQL Server Reporting Services. We spend over 80% of consulting work developing and distributing reports for our customers. Their data sits in all kinds of systems – Oracle, DB2, SQL Server, MySQL? And I’ve found that using Microsoft SRSS is the easiest way for us to get reports to our customers quickly and efficiently. It’s cheaper than Crystal Report Server, and what’s better, almost every customer has an SQL license for something, , even if they aren’t majorly a SQL Server shop. So it’s really cheap.

But still, I can program perl. I like open source! We’ve used apache, for goodness sake!! How could I?

Like so many things in life, the road to perdition didn’t happen all at once. It was subtle. It all began when we started writing complex macros for Excel. Our clients love that stuff. Even if they have a million dollar ERP system behind them, getting the data in and out of it through Excel just thrills them. I was making them happy. Everyone has Excel. What was the harm?

Then we started doing a few large projects on SQL Server, as in dozens of stored procedures, views, and queries. And we really liked SQL Server Management Studio, it’s both useful and easy to use. It’s not perfect, but it’s pretty nice. Again, what was the harm? The client made the choice and we are glad to work with pretty much any database. So my soul was still intact.

But then we were asked to make a choice for a client, and we recommended Microsoft SRSS and SQL Server, largely because it the cheapest option. It worked just fine, and we made money. Then we started playing with Analysis Services, and while it doesn’t do everything that the most sophisticated BI packages do, it gives you far, far more than most customers will ever use.

So we crossed over.

This does NOT mean that we are going to push all of our clients onto SQL Server. We are still dedicated to the idea that we’ll make the reporting tools you have work – “The reports you need from the software you already have.” ® And it’s not a magic tool that makes the detailed query building any easier. But it does mean that when we need to create and distribute reports, we’re recommending SQL Server, and that our development environment of choice is Microsoft.

I think I need a drink.


Room for Some Views Pt. 2 – Summarizing Tables

I recently talked about different ways to use views when writing reports. Here I’ll tackle two additional ways you can use views: summarizing data and avoiding nulls.

Summarizing Data

Importing too much data into a PC is a sure way to create problems. If you’re in a high transaction environment, like analyzing hits on a popular website or selling items among thousand of stores, it’s not impossible that you receive thousands of transactions per day. So you may have a sales transaction database that stores the following data:

If you pull all that transactional data into Excel or Crystal, you may have a problem.

Some systems will do the work for you, so you may have a Sales History file that looks like this, with all your data neatly summarized:

But what if you don’t have a file or you want to see the data summarized by day? You can build a view that will produce the data to look like this:

Avoiding Nulls
I’ve previously posted on left outer joins and on the problems of nulls. In a solid relational database, we expect there to be a record in each of the three files for a record in the transaction file. But we can’t always expect to find a record in the supporting file.

Let’s take our transaction file for example. What if, in addition to columns that always have values like Company, Profit Center and Account, you had another field, Project, which is sometimes filled in and sometimes isn’t. Instead of the original join you would create a “Left Outer Join” which would allow you to add the information when it existed.

But this can lead to another problem – how do your reporting tools and your end users deal with this new field? Because we don’t always have an entry in the Project field, we have a null ? meaning a missing value.

In the view, you can supply the spaces as necessary:

Case when Project.description is null then spaces else Project.description.

For some reporting tools, you may want to go further. Some tools don’t like fields that sometimes exist and sometimes don’t – it messes up the slicing and dicing. Again could again use your case tool for something like this:

Case when project is blank then “999999” else project.


Room for Some Views—Understanding the Value of Database Views for Reporting

“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.


Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284

Request a Consult