Active Reports, I hardly knew you
I tried to like ActiveReports from Data Dynamics, I really did. I mean, Jim Wooley likes it, so why shouldn't I?
I'm trying to set up a simple crosstab report - a report that takes the data normally viewed in rows and turns it into data viewed in columns. I've got data like this:
| dept |
cat |
period |
sale amount |
| AA |
abc |
1 |
400 |
| AA |
abc |
2 |
200 |
| BB |
def |
1 |
300 |
| BB |
def |
2 |
100 |
| BB |
def |
3 |
600 |
| BB |
ghi |
1 |
300 |
| CC |
jkl |
2 |
200 |
| CC |
jkl |
3 |
90 |
I need to turn this report on it's ear and make it look like this
| dept |
cat |
period 1 |
period 2 |
period 3 |
| AA |
abc |
400 |
200 |
0 |
| BB |
def |
300 |
100 |
600 |
| BB |
ghi |
300 |
0 |
0 |
| CC |
jkl |
0 |
200 |
90 |
Except now imagine that the source table has 400,000 rows in it. I've got 34,000 rows if I group by category so I've really got to turn this into a drilldown report, sorted first by department THEN by category in the drilldown. That will turn the report into something managable.
So I set about doing this in Active Reports. Um, it was HARD. Not what I wanted for a simple report. There was no simple crosstab component. I called support to see if I was doing something wrong (after all, I am using the demo version). The guy on the support line said that they've been trying to get their dev team to supply some crosstab components but as of now all the work has to be done by hand. That sucks.
On a whim I check the index of Teo Lachev's Reporting Services In Action book. Crosstab reports, designing....page 129. Page 129 shows a simple example of some dynamic SQL to create crosstabbed output and how it can rapidly get complex. Then Teo showed me how the matrix region control can create interactive drilldown crosstab reports with little work on my end. BINGO. SSRS 1, ActiveReports 0.
Don't get me wrong - I'm sure ActiveReports is a great tool. I've never used it. In what was supposed to be my first exercise, it couldn't do what I wanted it to do. I knew I'd seen similar work in Reporting Services, so I knew where to go. Teo - you're the man! Thanks for the great book. I promise to start reading the 2005 Analysis Services book soon (first I have to get work to let me install SQL 2005!).