In-Depth

Users Latch on to SQL Reporting Tool

SQL Server Reporting Services earns solid if not stellar marks from early adopters—and you can't beat the price.

• By Stephen Swoyer
• 01/01/2005

In the past 18 months or so, Microsoft has shipped Windows Server 2003, Microsoft Office 2003, SharePoint Portal Server 2003, SMS 2003 and Windows XP Service Pack 2—all high-profile product releases. So you're forgiven if you missed it in June when the company shipped SQL Server Reporting Services. But based on our survey of early users, you may want to pay it some attention.

Filling a Void
SQL Server Reporting Services fills a void in Microsoft's otherwise robust Visual Studio .NET and Office productivity stacks, offering reporting capabilities that IT and end users alike can employ.

Enterprise developers often need to incorporate limited reporting capabilities into custom-built applications, while Windows administrators are habitually hectored by users who demand strong end-user reporting functionality.

Production support engineers may need to report on how many users experience problems with their application on a month-to-month basis; how one month compares to other months; and how problems are ultimately resolved.

Sales managers can get custom reports that break down sales on a per-salesperson basis, for example, while business executives get a regional or per-division view and financial officers run sales forecasts and overall revenue projections.

Many users are taking advantage of Reporting Services' can't-miss price tag—free of charge with all SQL Server client licenses—to introduce base-level enterprise reporting functionality. Still others are replacing legacy reporting tools or makeshift solutions based on Microsoft Access or on the limited version of Crystal Reports that Microsoft bundles with its Visual Studio IDE.

More than a few are replacing mature products from Business Objects and other commercial reporting vendors with Reporting Services, while still others put it through its paces and decided it wasn't a good fit for their needs. As of late September, Microsoft says the version 1.0 product had been downloaded 100,000 times.

SQL Server Reporting Services at a Glance Cost: No additional charge with each SQL Server client license Vendor: Microsoft Corp. www.microsoft.com Pros: Simple to install Easy to use—little training required Good performance Generally good manageability and administration Cons: Limited available documentation May not be suitable for larger environments (10,000 users or more) Security model causes management issues Difficult to add new users Job scheduling not as granular as SQL Server Job scheduling not as granular as SQL Server's

Identifying a Need
Internet wholesaler CISP Inc. is a textbook case of the typical Reporting Services adopter. CISP had long identified a need for reporting capabilities of some kind, says Systems Engineer and Lead Data Architect Justin Zahn, but balked at the perceived cost and complexity of commercial enterprise reporting offerings.

For customer usage and billing, the company has to track millions of records per month from different carriers. When users requested reports, they were loaded into Excel spreadsheets and sent via e-mail.

"We have known for some time that we needed reporting tools, but we wanted a simple approach to it," Zahn explains. "It took only a matter of a week to get Reporting Services installed and start generating reports that could be used by the sales people to help track costs and see trends developing, like decreased usage at certain times of the year," he says. Now users in the sales department can run their own reports, rather than ask IT.

Performance and Scalability
Thus far, mostly small- and medium-size companies appear to be deploying Reporting Services. For what they've asked it to do, these users and others agree that Reporting Services is a great performer—especially compared to the custom-built or makeshift solutions many of them are replacing.

"I wrote a report using [Active Server Pages] that takes probably three minutes to load all of the report in the Web browser for certain cases," says Mark Bourland, formerly a senior software developer and analyst with First City Financial Corp. "Reporting Services runs the same report in mere seconds."

Reporting Services is being used in at least some large deployments, as well. Mid-Atlantic real-estate powerhouse Long & Foster has successfully implemented a Reporting Services solution to support as many as 10,000 users.

"What Reporting Services provides is what I had originally envisioned for our homegrown system, which never quite matured to that point," says Tim Ellison, a former lead architect with Long & Foster who's now a SQL administrator with media services company WFofR Inc.

But users who've tapped Reporting Services in large environments say they've had to scale back or otherwise reconsider their deployment plans. Jason Volpe, a SQL Server administrator with a prominent telecommunications firm, says he originally tapped Reporting Services to replace an existing —and seriously unscalable—Crystal Reports solution. Volpe's hope was that Reporting Services could scale to support as many as 15,000 of his company's sales representatives.

"We had to back off of Reporting Services and build our own control until Reporting Services is a little more refined," he says, citing issues with the product's inability to generate Report Definition Language files on the fly.

Security, Manageability and Administration
Most users give Reporting Services' good grades for security, manageability and administration—with certain notable exceptions.

Peter Schott, a SQL administrator with automotive financing specialist Drive Financial Services, says the product's security model makes it more difficult to manage. "It would be great to allow users entry to just their sub-folders without having to grant a site-wide permission first," he says, explaining that a user must be able to access the main folder—where all user resources are stored—in order to access his own folder.

Similarly, Schott says, adding new users is "painful" because only one user or group can be added at a time. Dave Bienstock, a systems specialist with manufactured housing and recreational vehicle builder Fleetwood Enterprises Inc., says a security console—especially for managing users—should be one of Microsoft's top priorities for the next release. But overall he says Reporting Services is no more difficult to manage than any other Microsoft product.

And even though Reporting Services runs on SQL Server, its job scheduling capabilities aren't as granular as are those of its host database, says Schott. Nevertheless, there's at least one manageability feature in Reporting Services he likes: "The data-driven subscriptions are pretty easy to set up and make it easy to send user-specific information to a lot of users at one time."

Usability, Training and Staffing
Almost all users give Reporting Services high marks for usability. This isn't surprising, seeing as how many have transitioned from makeshift or legacy reporting solutions. "The usability was the main feature," confirms Bienstock, who says it's a big improvement over his "hokey Microsoft Access environment with all kinds of coding, fields, queries and no consistent format."

As far as training is concerned, Schott says he did little more than show his users where to get their reports and how to use them. Once they mastered the basics, he introduced them to the magic of drill-down—which they also picked up quickly.

Most of the adopters we surveyed were in shops with 100 users or less, so the feedback we got with respect to staffing requirements should probably be taken with a grain of salt. Nevertheless, some IT organizations have deployed Reporting Services in very large environments, using what could be called skeleton IT crews.

"We implemented a solution that included the creation of OLAP cubes as well as completely customizing the front end with four developers and a project manager," says Ellison, noting the solution was designed to support 10,000 users.

Schott says his shop has one resident DBA who doubles as a report designer, along with one dedicated report designer. Similarly, Bienstock says that a single staff member manages and develops for Reporting Services in his environment.

David Bienstock, a systems specialist with manufactured housing firm Fleetwood Enterprises, says SQL Server Reporting Services is a big improvement over his "hokey" Microsoft Access setup.

Reporting Services Wish List
As with most 1.0 products, nearly every Reporting Services user has a list of things for Microsoft to improve. Bienstock notes that his legacy Access solution had several capabilities, such as pivot tables with non-aggregate data, that can't easily be duplicated in the Reporting Services environment. On the other hand, he says Reporting Services is considerably more scalable than Access, which would "break when more than 20 people start hitting it."

Similarly, Jeff Renaud, a programmer/analyst with the County of San Bernardino, Calif., says that he's had "great success" with a Reporting Services pilot project that's slated to replace an existing solution powered by Crystal Reports. Even so, Renaud allows, he's had to troubleshoot a couple of unexpected issues.

"Web services calls cannot pass users' security credentials from NT 4.0 domains, [so] local accounts had to be used instead," explains Renaud. "And [ActiveX Data Objects] .NET DataSets are not native and require significant programming and hoops to get to work. Other than that, it is a very useful product and will be used to replace our current Crystal Reports environment in many cases."

Steve Swope, a technical manager with The Reynolds & Reynolds Co., says that his company is implementing Reporting Services as part of a .NET rewrite of one of its internal data entry applications. Swope likes what he sees so far, noting that Reporting Services is multi-threaded, features good printability, is relatively easy to develop for, supports a variety of export definitions and is easy to deploy. Reports can also be cached in memory, which helps to speed retrieval. His negatives include the fact that reports are directly coupled to data sources, that selection parameters are in turn coupled to reports, and that both available documentation and the extant example pool are sparse.

Drive Financial's Schott likewise cites the paucity of available documentation as a significant shortcoming—along with the limited availability of tooling to help build so-called "Regular Expressions" (Regex). Regex is a .NET class that provides a way to parse, edit and replace text. It's also notoriously complex, says Schott, who allows that "more examples or a better expression builder would help out"— particularly for non-developers.

Resultant Set Conclusion
Overall, most Reporting Services adopters seem satisfied with the product or even excited by it. To a surprising degree, the users surveyed have accepted the shortcomings of Reporting Services with equanimity. "I really look forward to seeing the next version when Yukon comes out. This product shows a lot of promise," says Drive Financial's Schott.

In this respect, Schott and other users concede, Reporting Services has already gotten better—thanks to Service Pack 1, which boasted improved Excel rendering, as well as support for older versions of Excel.

If nothing else, says Stephen Witter, a report developer with a healthcare products company based in the Midwest, adopters should consider that Microsoft has rapidly brought to market a product—Reporting Services—that was originally scheduled to ship with SQL Server 2005. In this respect, he argues, the software giant can be forgiven for a few rough edges, feature shortcomings and functionality issues. "Microsoft's Reporting Services team has done an exceptional job not only in the development of the product, but also in the support arena via the newsgroups and in some cases, even direct contact," Witter says.