Tuesday, December 06, 2005

Report Builder and the UDM

This month is definitely Report Front end development month. After presenting at our cabana session at SQL Server 2005 launch I have been getting busy with Report Builder and Reporting Services 2005. The project I am working on is a small scale reporting solution.


At first I had a good look at using Analysis Services as my main repository for storing all my business logic and handling querying against the source system. This has its advantages; it reduces the load against the source system and offers high performance.

The main disadvantage was the complexity of getting data out of an OLAP solution. Of course it is easy with RS 2005 now, but the navigation across the OLAP cube can be a bit daunting to a novice business user.

Report Builder

Then there is Report Builder. Now there is something, an easy to use interface to create reports against any SQL Server/Analysis Services data source. The report writing is done via a report model. The model removes the need to create queries that require knowledge of the data source. This is great for users that don’t want/know how to join tables.

There is a gap in Report builder when it comes to complex reports. Report builder is a limited report designer allowing users to create reports in a controlled interface. For complex reports I always go for Report Designer. There is a not so well document feature that allows you to create Visual Studio Report Designer reports against a report model.

Russell Christopher wrote a neat little piece on using a report model as a data source. Check out: Using a Report Builder Model as a Report Designer data source in SQL 2005

How about both Report Builder and Analysis services?

The best solution is using a combination of Analysis Service cubes and Report Builder to provide an easy access way for users to reports. Using this hybrid approach will enable high performance query and reporting response times with user interface that’s not a pivot table.

It is a challenge to create a Report Model based on Analysis Services. The quickest way is creating a shared data source in reporting services. Follow these steps:
  • Select the shared data source in report manager

  • Click on Generate model button

  • Enter a Model Name, Description and Location Click ‘Ok

  • Open Report Builder and select your new model.

Mark Russo has also blogged on creating a report model against Analysis services in BI Studio. Check out: Report Builder Model with UDM


Joe Horton said...

Following the examples I get the error below. The connections string should
be ok as it's the same one I use when browsing the cubes in BIDS:

Cannot create a connection to data source ''.
(rsErrorOpeningConnection) Get Online Help For more information about this
error navigate to the report server on the local server machine, or enable
remote errors

Myles Matheson said...

Hello Joe,

I have seen this error before. It was caused by the SQL server being renamed Check the following:

1. Use the full server name instead of local alias

2. Check that the server has not been renamed

3. Try to connect to the RS server through Management Studio

Also are you only getting this error in BIDS. Have you tried accessing Reports in Report Manager?