Work with Report Model in SSRS

The report model is the metadata layer component that serves as the basis for building reports. Report designers, both end users and IT staff, will use the report model as the data source for the report instead of directly accessing a relational database or OLAP cube data source when designing reports. 

The report model contains three key elements necessary for the ad hoc reporting process to function smoothly:

  • bindings to the underlying data-source schema
  • a logical representation of the schema in the form of business entities that are familiar to end users
  • the default information to be used according to the role that the entity plays in the report (primary entity, lookup entity, summary, and so on).

A report model definition is an XML file that follows the Semantic Model Definition Language (SMDL) specification.

Report Model Components

Report models primarily consist of the following three item types:

  • Entities are the equivalent of objects or tables in a database. Entities are composed of attributes and roles.
  • Attributes are the equivalent of object properties or columns in a database table.
  • Roles are the equivalent of object references or relational foreign keys and join operations.

Additionally, report models incorporate some organization functionality, allowing administrators to create folders (containers) and perspectives (sub-models). Consequently, report models for larger data sources can be more focused for their intended audience.

Clickthrough Reports ***

In SQL Server 2005 Enterprise Edition, Report Builder includes a practical feature that enables users to drill through on the data in their reports without having to first design target reports and connect traditional drill-through links to them. This feature is called clickthrough reports. Clickthrough reports are generated automatically from the report model metadata.

For example, while a user is looking at last month’s sales summary report, he might need to find out more about a certain order. Clickthrough reports allows him to click on the Order ID column, triggering an automatically generated report that shows the full order details for that order. Then, he might need to find out about a specific product or about the customer that placed that order. Again, he can click on the Product ID column or on the Customer Name field to see another clickthrough report about those fields.

Using SQL Server Management Studio, model designers can attach a custom report as the drill-through target if the automatically generated report is not adequate.

Because automatic generation of clickthrough reports relies entirely on the report model metadata, a well-designed report model is critical for a successful user experience and for reducing the need to develop additional custom reports. A correctly adjusted model will greatly increase user productivity and satisfaction. A poor or incomplete model will sit unused at best. Automatically generated report models from relational sources should be considered only a starting point for your model, not an end; you will need to edit the model to customize it in a business-savvy way.

Creating a report model with the Report Model Wizard involves several steps:

1.      Create a connection to a data source.

2.      Create a Data Source View (DSV) to contain schema metadata.

3.      Review the resulting DSV to do a basic clean-up and revise the metadata to fit your business requirements.

4.      Using the revised DSV, invoke the Report Model Wizard to create the base model.

5.      Edit the report model to adjust it to expected usage.

6.      Deploy the report model to the Reporting Services report server. The model is deployed automatically when it is generated through Report Manager or SQL Server Management Studio.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s