My current project is using Reporting Services as the reporting engine for our application. The application involves brining in lots of data from an external system and reporting on that data in interesting ways. A lot of our reports are “pivot table’esque”. To implement these reports we’re using “matrix” reports within Reporting Services. Enough background on to the rants….
Rant #1 – Did anyone think about how reports would be implemented in a web environment?
One of our early frustrations was with linking reports that are displayed via the Report Viewer server control. For those of you unfamiliar with the Report Viewer server control, it renders an i-frame within your HTML with the report displayed within the i-frame. Unfortuantly the HTML that Reporting Services renders doesn’t allow you to use the Report Viewer as the container for sub-reports when you drill down. Reporting Services generates links with target tags on the anchor elements which causes the “drilldown” to the sub-report to launch in a new window. There are some workarounds for this such as using the rs:ReplacementRoot, but none of them are very clean. It seems like figuring out some use-cases for how people would use reports within web applications would have forced the implementation to be much better. Perhaps those use-cases were never developed?
We ended up using javascript: to get the desired behavior. Luckily SP1 provided support…
Rant #2 – Can we get a little flexibility with our Matrix reports?
As I mentioned above we have a bunch of “matrix” reports within our application. Compared to the table layout the matrix layout is very restrictive. It’s difficult to add on your own columns to the right of the matrix, and customization capabilities of the layout doesn’t provide the flexibility that’s necessary for the vast majority of reports that we wanted to create. The flexibility of the table layout needs to be combined with the matrix layout.
In order to get our reports laid out the way we wanted we had to use sub-reports. Our matrix was shown in a sub-report on the left and another sub-report was displayed on the right with our “custom columns”. The formatting and alignment of the sub-reports has proven to be a bit painful.
Rant #3 – Exporting to Excel doesn’t include sub-reports?!?!?!
To get around the limitations of the matrix report we ended up having our reports laid out using sub-reports. This allowed us to get all the data on the report that we needed and also get the benefits of the “matrix”. I imagine that most reports developed using Reporting Services make use of sub-reports. With this in mind, why in the world would the Export to Excel feature not support sub-reports? One of the advantages of going with a reporting package such as Reporting Services is that it supports exporting data to a variety of formats…well at least we assumed. Without support for exporting sub-reports the “export to excel” feature within Reporting Services is completely useless. What’s even better is that as far as I can tell this isn’t something that will ever be supported for SQL Server 2000. I guess by making Reporting Services crappy on SQL Server 2000 there is a hope that people will just move to SQL Server 2005. I should probably go get started on my custom export to excel feature :-(
No solution for this item so far, I’m hoping to uncover some miracle solution so I don’t have to write the export to excel feature myself. The ability to export to other formats (excel specifically) was one of the major selling points for us. What a bust…