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

SQL Server 2008 and end user ad hoc reporting

  • Comments 14
  • Likes

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.

  • PingBack from

  • Hi,

    I need one help. Is it possible to create reports something like printing address labels using report builer. I need to get FirstName & LastName in the first line, address line 1 as second line and city,state & zip code information as third line of the label. How can we arrange fields in such a manner to get this type of output from report builder. Kindly help on this.



  • Kavitha - Let's do this direct. send me an e-mail address to and I will get you some help wiht this

  • >  this has been done to illicit feedback

    er.. I don't think that's quite what you meant!

    Made me laugh though..

  • Andrew, one quick question.  I have noticed and you have highlighted that Report Designer only connects to the SQL Server database (Type).  Do you know if Microsoft plan to support more types and in particular Custom Data Extensions?  Thanks.  

  • I somewhat agree with you Andrew T

  • Gary

    The rpoert designer preview in CTP does only talks to SQL server, and that's because it is a preview.  It will be able ot use any source that reporting services currenlty has and will also be able to run over a report model as report builder in 2005 does.

  • Any updates regarding the support on Custom Data Extensions?

  • So is the "Report Builder" as we know it in 2005 a dead-end product?


    Good question. As I understand it Report Builder won't be developed further in the next release.  --AJF

  • Hi Andrew,

    Do you know if sql server 2008 will allow reports to use  services as a data source?  I cannot find any information that says it does, so I am assuming the answer is no, but I would like to know for sure.  Thanks,


  • Dan

    Nout sure what you mean by a service in this context, can we take this off-line e-mail me at and so I can understand what yu want to do, then I will blog the answers

  • I work for a Microsoft Learning Partner and am curious if you know of any webinars / demos / tutorials on SQL Server 2008 reporting tools that would be appropriate for business users (e.g. Business Analysts).

  • Steve

    I don't think this has been given any thought as yet so I'll pass it on.  As a partner you can probably see nearly as many demos etc. as I can, but I will do some digging and get back to, can you e-mail me direct please (

  • If the tables structure changes, and also the model design, is possible to attach a new model to the existing Ad Hoc Reports?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment