Atlanta .NET Regular Guys

News

Brendon Schwartz - Email Me
Matt Ranlett - Email Me

Brendon Schwartz

MVP Logo
Community Kit for SharePoint
View Brendon Schwartz's profile on LinkedIn

Matt Ranlett

Matt Ranlett MVP Logo

Community Links

Useful Links

SharePoint 2007

ASP.NET 2.0

Atlanta Area Bloggers

BizTalk

SharePoint 2007 WebControls

SharePoint 2007 Development

Active Reports, I hardly knew you

Bye-byeI 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.

Microsoft Reporting Services In Action 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!).

Comments

Jim Wooley said:

You have stumbled upon two of the areas that Active is not strong in: Drill-down and crosstab. I have created a crosstab report in Active and it is not a simple process. I should write it up one of these days.  Basically it entails taking a tabular data source, crunching through it inside of one report to create a datasource with dynamically added columns when new key values are included. Then dynamically generating a new subreport based on the values of the dynamically created crosstab datasource. It works, but is definately hard than it should be. No single tool is a panacea. Each should be used for what it is good at. Active Reports is great for reporting and printing, but not so nice for interactive processing (which is often better done through a more standard UI anyway.)

Another tool you may want to look at is Dev Express's Xtra Reports at http://www.devexpress.com/Products/NET/XtraReports/. I haven't tried it, but have heard good things about it. It appears to have drill-down capability, but possibly not crosstab/pivot.
# June 2, 2006 12:00 PM

Mark Dunn said:

The single best thing I can say about ActiveReports is it isn't Crystal.   I had to use Crystal for quite a while, which does a lot but its full of bugs.   I really like SSRS.  Its big shortcoming before 2005 was lack of an end user ad-hoc report designer.  That has been addressed in 2005.
# June 4, 2006 2:48 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)