Gallery

Introducing SQL reports

Posted on 6 Nov 2008 at 15:48

End users' reporting requirements are ever-changing. Simon Jones takes you through the process of building a report model to answer every demand.

This month, I've been preparing to go live with a large line-of-business application for one of our larger clients, and fixing the last few bugs. As well as testing, training and data conversion, we also have to finalise the reporting although, of course, we can never anticipate all the required reports so early in the project. Throughout its life, users of the application and their managers will always come up with new reporting requirements: sometimes simply to add an extra column to an existing report; at other times to change the sorting or grouping; occasionally wanting a whole new report that looks at the data differently.

Our new line-of-business application stores its data in SQL Server 2005, so we're basing all these reports on SQL Server Reporting Services (SSRS), which gives us great design flexibility. SSRS reports are created in Visual Studio, a cut-down version of which ships with SQL Server as SQL Server Business Intelligence Development Studio. A competent programmer can design complex reports by dragging and dropping data fields onto the design surface, to produce simple columnar reports, cross-tab matrix reports, graphs and charts, and layouts that look like business forms such as an invoice. SSRS supports multilevel sorting and grouping, calculated fields, rich and dynamic formatting, user-defined parameters, compound and drill-through reports, and much more.

Completed reports are published to folders on the company's internal SSRS website, where end users can browse and execute reports whenever they want. They're able to fill in the report parameters, change the sort order, expand and collapse groupings, drill through to more details and print any report they want to have on paper. SSRS reports can also be exported as PDF documents or Excel workbooks and, crucially, a user can choose to subscribe to a report, which means it will be generated automatically to a schedule the user decides (hourly, daily, weekly, monthly) and delivered by email or placed into a particular folder.

The one thing that wasn't possible in the original release of SSRS back in 2003 was for users to define their own reports: that required Visual Studio and a competent programmer. SSRS 2005 fixed this by introducing a new Report Builder module, though programmers are still needed to build the Models on which Report Builder will work.

Databases can be very complex entities whose internal structures aren't typically designed to be friendly towards end users: there are ID numbers or GUIDs (Globally Unique Identifiers) representing lookup values; complex relationships; dates; and numbers that may represent tallies, percentages, measurements or monetary sums - asking an end user to design their own report based on raw database tables and columns constitutes a "cruel and unusual punishment".

SSRS lets the programmer simplify and explain the database by building Report Models. As usual, there's a wizard to get you started that does a lot of the grunt work, but a programmer is needed to add his knowledge and expertise to the Models to help and guide end users who'll then base their reports on those models. To create a new Report Model, start Visual Studio or SQL Server Business Intelligence Studio and create a new project from the Report Model Project template in the Business Intelligence Projects type. Set the name of your project, its location on your computer, and add it to Source Control (Visual SourceSafe or Team Foundation Server) if you want. The template creates an empty project with three folders in the Solution Explorer: Data Sources, Data Source Views and Report Models.