Managing Report Models
In Reporting Services, model management includes adding and deleting models to and from a report server, modifying model properties, and managing the shared data source that is associated with a model. For more information about controlling access to all or part of a model, see Securing Models. For more information about how to generate a model from a predefined shared data source, see How to: Create a Model Using Report Manager.
Note
Report models are not supported in SQL Server Express with Advanced Services. For more information about features in this edition, see Reporting Services in SQL Server Express with Advanced Services.
Using Models in Reporting Services
To manage models effectively, you must understand how they are used. In Reporting Services, report models describe data for ad hoc reports that you create and modify in Report Builder. Report models are used in the following ways:
As a data source for designing a report in Report Builder or a model-based report in Report Designer.
As a data source for exploring data at run time. Because Report Builder reports are based on models, users can explore data in a non-linear navigation path, clicking through autogenerated reports when exploring report data interactively.
As a data source for executing queries to obtain data. Third party client applications can access the model programmatically and use it as a report data source.
Predefined and Autogenerated Clickthrough Reports
Ad hoc data exploration through model-based reports can be supported exclusively through autogenerated clickthrough reports that show users related data. However, you can supplement or replace an autogenerated report with predefined custom reports that show data in a specific way.
Autogenerated clickthrough reports are created by the report server on the fly as a user navigates the data in a report. The report server uses templates to create temporary reports. There are two templates: one for single instance data (for example, to show data about a specific customer) and another for multi-instance data (for example, to show a list of orders for a specific product). The templates cannot be modified. However, you can replace a temporary clickthrough report with a predefined report that has the style and layout you want.
Predefined clickthrough reports are custom, published reports that you map to specific parts of a model. When a user navigates to some part of the model that is mapped to a predefined report, he or she views the custom report rather than the clickthrough report that is generated by the report server. Because they are created by a report author, predefined clickthrough reports can use all of the report features that are available through Report Builder. You should create a custom report when you want to present data in a specific layout. Predefined clickthrough reports must be created in Report Builder. After you create and save the report, you can use Report Manager to associate it with an entity in your model.
Access to predefined reports is controlled by role assignments on the report. If a user is not allowed access through an explicit or inherited role assignment, the user will view a temporary report instead of the predefined report that you mapped to the model item.
Navigating Data in Model-Driven Reports
Report Builder reports provide a starting point for further inquiry. In contrast with the explicit drillthrough and drill-down navigation paths supported by Report Designer reports, Report Builder reports contain clickable data points that users can follow to view additional data provided by the model. For example, if you have a model that describes sales, product, and employee data, a user who starts with an Employee report can potentially view ad hoc reports about specific employees, products, and sales by clicking through the report.
.gif "Series of reports in drillthrough navigation")
If multiple entities are related to each other, the clickthrough navigation path is determined by the value in the report that represents an underlying entity. For example, suppose you have a model that contains a Customer entity, a Customer Orders entity, and a Customer Returns entity. If the Customer report includes fields for number of orders and number of returns, a user who clicks on number of orders will drill through to a report that shows a list of orders; a user who clicks on number of returns will drill through to a report that shows a list or returns.
The ability to navigate an ad hoc data path is determined by model item security. For example, to control access to customer orders, you can secure the Customer Order entity in the model. Only users who have permission to view the Customer Order entity will see customer order information in a report.
How to Map a Predefined Report to an Entity in a Report Model
You can design a custom report to use in place of the clickthrough reports that are autogenerated by the report server. To make a custom report available to users, you must create it in advance, publish it to the report server, and then map the report to the primary entity in the model.
If you are providing custom clickthrough reports, you should include both a single instance and multi-instance version of the report. The data path by which a user navigates to a specific entity determines whether a single instance or multi-instance report is required. You cannot always know in advance whether a particular version of the report is not needed. Use the following steps to create custom reports for clickthrough navigation:
Generate a model on the report server.
Create custom reports using the model as a data source.
To create reports that use model data sources, use Report Builder. You should create a set of reports for each entity: one for single instance data and another for multi-instance data. To learn how, see Tutorial: Customizing Report Builder Clickthrough Reports.
Save the reports to the report server.
Start Report Manager.
Right-click the model and select Properties.
On the Clickthrough Reports page, find the entity for which you want to specify a custom report. For more information, see Clickthrough Reports Page (Report Manager).
Choose the single instance and multiple instance reports you created.
Managing Shared Data Sources and Report Models
Report models can use data from SQL Server 2000 and later databases and SQL Server 2005 and later Analysis Services cube databases. To connect to a database, a report model uses a shared data source that defines the connection string, credentials, and data processing extension.
Each report model is associated with a single shared data source and a data source view. The shared data source is originally created along side the report model in Model Designer, but it can be managed as a separate item after the model and shared data source are published to a report server.
When specifying a shared data source for a model, certain restrictions apply in how the credentials are obtained. A shared data source that provides data to a report model must be configured to use:
Stored credentials
Windows integrated security.
Prompted credentials or no credentials are not supported for shared data sources that provide data to report models. For more information about credentials, see Specifying Credential and Connection Information for Report Data Sources (SSRS).
Adding Models to a Report Server
You can add model (.smdl) files to a report server in the following ways:
Publish a model from Model Designer to a report server.
Upload a model from the file system to a report server.
The easiest way to get models onto a report server is to generate them from a shared data source. You can also publish a model from Model Designer or upload a .smdl file from the file system.
Not all .smdl files can be uploaded. If the file is missing data source view information, you will get an error when attempting to upload the file. Data source view information will be missing if you attempt to upload a .smdl file that has never been published to a report server. Before publishing from Model Designer, the .smdl file and data source view are stored separately. During publication, data source view information is merged into the .smdl file. As a result, you should only upload a .smdl file that has been previously published to a report server, and then subsequently saved from the report server to the file system.
For more information about publishing and uploading models, see Publishing a Report Model and Uploading Files to a Folder.
Deleting Models and Data Sources
A report model provides the data to reports that you create in Report Builder. If you delete a model, you can no longer open, run, or modify any reports that are based on that model.
A report model uses a shared data source to specify connection information to a relational database or cube that provides data to the report. If you delete the shared data source, you will break the model and any reports that are based on it.
If you mistakenly deleted a shared data source, you can use Report Manager to create a new one that points to the underlying database or cube, and then specify the new data source in the General Properties page for the model. The new shared data source item can have a different name, credentials, or connection string syntax from the one you delete. As long as the connection resolves to the same data source, you can use the data source with the model.
You cannot re-create a model that you deleted inadvertently. If you deleted the model by mistake, you must regenerate it, recreate and save the reports, and respecify any model item security that you want to use. You cannot regenerate just the model and then attach it to an existing report.
Renaming Models and Dependent Data Sources
You can rename a report model without affecting the report that uses it. You can also rename a shared data source that is used by a model without affecting the availability of a report.
Updating Model Items
For models based on a SQL Server relational database, you can use Model Designer to modify and republish a model. If you republish a model while it is in use (for example, while a user is navigating the clickthrough reports that are generated from the model), the report will be locked while the deployment is in progress. To stop model processing while you republish the model, you can disable the shared data source that is used by the model. To do this, clear the Enable this data source option in the Data Source Properties page. If you delete a model item that is used in a published report, the user will get a "model item not found" error for that part of the report.
You can use Report Manager to create or modify model item security settings. Within a model namespace, model items are uniquely identified by model identifier numbers. When you update a model, the report server compares the identity numbers between the updated and previously saved versions of the model. If there is a match, the updated model item gets the security settings of the existing item. There are several requirements to securing model items. For more information, see Model Item Security Page (Report Manager).