Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 and end user ad hoc reporting

In my opinion the weak link in reporting services is that there is no tool for information workers to design their own reports and so IT professioanls have to spend a lot of time doing it for them.  I can see there is a place for these reports:

  • They will be accurate or at least give the user what they asked for but not necessarily what they want. 
  • They will leverage all of the cool features in the tool such as document map, linked reports and actions to other tools.  But there is often a long gestation period for reports written in that way as the users insist on the correct formatting and layout and usually ‘refine’ the spec in the process.

Reporting Services in its 2005 release does have a lightweight tool for end user reporting, called Report Builder and I have implemented this for a very simple application.  The great thing about it is that relies on a Report Model, which is a bit like a unified dimensional model in analysis services in that it insulates the end user from the underlying relationships in the source database and has derived calculations in. A Report Model can either be built from the ground up against a relational source in the BI Development Studio (BIDS) or be generated from a cube in Report Manager by creating a data source to the cube and then selecting  generate model.  Once a Report Model is deployed to Report Manager the user can then immediately create their own reports.

However this tool is far too simplistic and there are no plans to enhance it in SQL server 2008.  Instead there is a new tool called Report Designer Preview (Name not determined yet).  It’s included in CTP 4, which gives an idea of how it will turn out ,but at this stage it is severely handicapped:

  • It only connects to a SQL Server database. 
  • It has got no help and some of the menu options are not populated.

Hence the Preview, it is not supposed to be fully formed and this has been done to illicit feedback from those of you adventurous enough to try it.  For me what is really important about it are three killer features two of which you can see in the CTP and one you can’t! 

Firstly It’s a standalone install (not a smart client like report builder) and business users don’t have to battle with Visual Studio. 

Then there is tablix. Although it looks from the tool that you still only get table and matrix reports in fact there is only one tool the cunningly named tablix (table + matrix) this gives the benefits of both as you can see from this:

So data can be show in one grid from two dimensions across the dimension they have in common and that is only one of the problems that can be cracked using this tool.  Note that you have to use the report designer preview as the designer inside BIDS hasn’t changed to be tablix aware. 

The bit I can’t show you is how this will help users and that’s because in CTP4 the tool can only source data from SQL server and that means writing a query which isn’t good for the business user. I understand that the plan is allow the tool to use report models, as well as source data from cubes and the usual raft of data providers that reporting services currently supports. 

So serious end user reporting will be available in SQL server 2008 reporting services.  Which means that while the users design their own reports we’ll have a little bit more time to keep up with all the other new stuff in this release.