Report Models in Reporting Services 2005

By: MSSQL Tips | Updated: 2006-11-29 | Comments (2) | Related: > Reporting Services Development

Problem

Users have all but fallen in love with Reporting Services. First, for its presence anywhere an Internet browser can be opened and second for the opportunities to export data to numerous formats. But users also like their toys and want to manipulate the data many ways.

So does it really do any good to publish reports on the server when they're going to take it and "do their own thing" to it? The answer is it depends on who's accessing the reports. Having a predefined number of reports available can help you keep the hardware resources from being overtaxed, but there are individuals in the organization that need the ability to create additional reports. This ability is provided through Report Models.

Solution

A report model, in technical terms, is a metadata description of a data source and the relationships that reside within that data source. What it is in practical terms is so much more. For a user it's their own little sandbox. But creating a Report Model is a multi-step process.

The first process is to analyze the needs of the users so you know what fields they are going to need in thier reports. Providing too little information renders the reporting service useless, but providing too much information could eventually cause performance issues as more users access reports. The second step is to ensure you have the appropriate referential integrity in place. Having a series of reports running on a poorly normalized database or poorly designed Analysis Services cube will cause additional headaches. When the project is first created, go into the properties of the project and set the URL of the Report Server (default setting is http://localhost/reportserver):

After setting the URL of the Report you must specify a data source. Right-click Data Sources and choose New Data Source. Then choose the server you wish to connect to and the authentication method:

Creating a new data source in Solution Explorer

The next step is to create a Data Source View. Right-click Data Source Views and choose New Data Source View:

Creating a new Data Source View in Solution Explorer

As usual, you must select the data source you wish to use or, if not created yet, do so inside the wizard:

The next screen in the wizard is one of interest, because here you can either choose from the entire list of tables and views, or narrow your selection based on the schema the object belongs to:

As stated before, you can choose either a view or one or more tables, although you should create a view if joining more than two tables is necessary. Once the choice of objects is made, click Next and provide a name for the data source view:

View of Solution Explorer with Data Source View

Now you have everything you need to create a report model. Right-click Report Models and choose Add New Report Model:

Choose the data source view you wish to create the report model for and click Next:

Then select the rules you want for metadata generation:

Choosing the Rules for Metadata Generation

The wizard then gives you an option on whether you want to update data statistics for the data source view or use the existing ones (the default is to use the current ones):

Collection of model statistics on the Data Source View

Once all the information is collected for creating the report model, click Run to complete the wizard:

Now you have to deploy the Data Source and Report Model to the Report Server (there is no need to deploy the data source view). Right-click each of the objects and select Deploy. From there you can assign security permissions to ensure that only those who need to access the data are able to access it. Users can also use Report Builder to create the ad-hoc reports and deploy either to their My Reports folder or to another folder on the Report Server.

Next Steps

Consider the need for ad-hoc reporting in your organization and create report models to provide this service
Map out a plan on paper of who needs access to what information so that security model creation is easier
Look at more Reporting Services tips on MSSQLTIPS
Review information on Ad-Hoc Reporting With Report Models

About the author

MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2006-11-29

Comments For This Article

Tuesday, September 14, 2010 - 9:37:48 AM - Mark Freeman	Back To Top (10158)
I am also interested in how to get an existing report to work with a new model. Another tip that would be useful is how to update a model. For example, we distribute a model with version 1 of our application. In version 2, we add new tables and want to add them to the reporting model. Do we have choices other than building a new model from scratch or hand-editing the XML?

Thursday, March 6, 2008 - 7:24:34 AM - Hector Ortiz

Hello,

"Is there a way to assign a new model to an Ad Hoc report?"

This company have about 100 ad hoc reports based on a bad designed Report Model, the table links and structure where bad. I download the model into a new Report Model Project and made the changes to the data source view and autogenerate the model, I have a semantic error running the reports with the changed model. I realize that in someway the report is closely related with the model and once you do a report you cannot alter the model making big changes to the model because the report will not recognize the model. I need your advice on this, is there a way to assign a new model to an Ad Hoc report?.