 |
|
Report Models in Reporting Services 2005
|
By: Edgewood Solutions | Read Comments (2) | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More |
|
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:
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.
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
Last Update: 11/29/2006
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Thursday, March 06, 2008 - 7:24:34 AM - Hector Ortiz | Read The Tip |
|
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?. |
|
| Tuesday, September 14, 2010 - 9:37:48 AM - Mark Freeman | Read The Tip |
| 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? | |
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |