Everyone's Got a Cross(tab) to Bear

Crosstab queries are awesome. We used several of them to create our GWAC Dashboard. But BI Services don't handle them all that well. So we put together a couple tricks to make them work.

I really wanted a funny picture to go with the
title here, but this is what you get instead.

Right out of the gate, I have two confessions to make. First, this solution was mostly implemented by other members of our team, and I just inherited it (and found a few ways to tweak it). Second, it's not even original to our team. The core of this solution comes from this fantastic blog post. So go read that first. He does a great job of explaining what's going on, so I don't have to.

So what are we going to talk about, then? Well, implementing those instructions aren't easy. But I don't think I can make the explanation any more clear. What I can do, though, is offer a couple other tricks I discovered.

First, what happens if you have more than one row header? This isn't terribly uncommon, really. Sure, your basic crosstab has something like categories down the side and years across the top. But what if you have categories AND colors? What happens then? Huh? Huh?

Okay, now I'm getting belligerent, and that's no good. So I'll tell you. All your figures end up one column off, just like the original problem with the crosstab not lining up. This is because the "number of columns" figure does not take the row headers into account. Fortunately, the solution is simple. Check out the second formula:

All we need to do to make this happen is to  shift our expectations one more column to the right. So when we check to see if we have enough columns, we just add one: =IF(OR(G3="",G3>$B$2 + 1),"",G3+1)

Making the columns

Is that it? Well, yeah, pretty much. If you have two column headers, you adjust the third formula in the same way. Check to make sure that you are still generating sequential numbers when it wraps to the new row. And then, of course, run some test data through it to make sure you are getting the correct results.

How do you know how many numbers you need? The glib answer is "know your data". If you look at the GWAC Dashboard, I only have a handful of contract families, a handful of fiscal years, and other dimensions are similarly small. But we did run into this problem when we needed to have a crosstab that used Agencies. We might have four. We might have twenty. So you need to make sure your grids for the numbers and the reconstituted crosstab are big enough. At this point, we discovered that it made far more sense for us to put the grid and the numbers side by side rather than one over the other. We were far more likely to get a large number of rows than a large number of columns. So, just a little tip there.

But don't all those formulas hurt performance? In a word, yes. But, there are a couple saving graces. It doesn't hurt performance by a lot. Unless you have a LOT of crosstabs (or some really big ones), you probably won't even notice the lag. Also, it doesn't keep doing the calculations all the time (see our recent discussions on NOW()). These calculations will only be done when the connection refreshes. And, ultimately, you just have to suck it up. Everything you do has a performance cost, and there isn't really a better way to handle crosstabs.