In my previous post on Reporting Services I mentioned that other report makers like Crystal and Lockwood Tech would not be happy. I'll modify that a little bit to say that some users will be happy with products like Lockwood's SQLWebReports because it is easy to setup and use. SQL Reporting Services takes more attention to initially setup, and as I mentioned, you have to have the Visual Studio 2003 IDE in order to use Microsoft's Report Designer. You also have to have a SQL Server for the licensing and to store the report metadata, so any non-SQL Server shops would probably stick with whatever they are currently using, like Crystal Reports.
Now, the actual report definition is an XML file and Microsoft says they have published the Report Definition Language specification, so keep your eyes open for someone to create an independent replacement for Report Designer that does not require the IDE. I haven't heard anything yet, but I'm guessing someone will do it. Some of the features that really stuck out for me were: variety of data sources, "burst" reports, support for hierarchical data, caching and history options, and multiple output formats. I'll briefly describe each of these below.
- Variety of Data Sources: While the Reporting Services metadata is stored in a SQL Server, your reports can be based on data pulled from any of the usual data sources like SQL Server, Oracle, or other ODBC connections. They also said that Reporting Services can connect to business objects and not just directly to the database. Stored Procedures can also be used as the data source to retrieve the data.
- Burst reports: This is the act of running a report and having it automatically broken out to personalized sections and each section delivered appropriately. For example, think of a sales force running the end-of-month reports where each salesman gets the report that shows only their results. This could be based on a query in your database like "SELECT DISTINCT FullName, PrimaryEmail FROM SalesPeople" and have Reporting Services email each salesman their portion of the report.
- Hierarchical Data: Also called trees, hierarchies can be a royal pain to deal with in a relational database. Microsoft has built in features to define hierarchical relationships and handle them. It would be interesting to run a profiler trace on this to see how they're handling it in SQL 2000, but regardless, it's a nice feature to have.
- Caching and History: Reports can be cached for performance. For example, suppose that sales force comes in on Monday and everyone needs to run a list of the latest pricing updates. The report can be marked to remain cached for any length of time, say for this example, 4 hours so that all the salesmen get a quick response on their report and it's okay if they don't get the "latest up-to-the-second" updates. Perhaps no updates will occur that morning, or maybe it's just important that everyone is working off the same report. And related to this, you can create cached reports that essentially never expire. These become historical snapshots of the reports. For example, a company might want to keep historical snapshots of their end-of-month reports. These could be stored within the application.
- Output Formats: The default output format is to the web. But already built-in are options to output or export to XML, Excel, PDF, and Office Web Components. The reports can be emailed to the intended recipient either as a link to the web site, or embedded in the email.
I'm really looking forward to this release. I have some serious Crystal headaches that I hope to make go away with Reporting Services as soon as possible.
posted @ Saturday, November 15, 2003 12:07 AM