Report Models in Reporting Services 2005

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.