Report Models in Reporting Services 2005
By: MSSQLTips | Updated: 2006-11-29 | Comments (2) | Related: > Reporting Services Report Models
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.
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:
The next step is to create a Data Source View. Right-click Data Source Views and choose New Data Source View:
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:
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:
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):
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.
- 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
View all my tips
Article Last Updated: 2006-11-29