Working with Report Models in SSRS - Overview – Part I

When we are creating reports with the help of SSRS (SQL Server Reporting Services) using BIDS (Business Intelligence Development Studio), we are dealing with predefined report formats. Sometimes it is required to create certain reports on the fly. We do not have a set format for these reports. These report formats are required to be created as and when required by the client. For this reason, a very important feature has been provided called ‘Report Builder’ in SSRS. With the help of Report Builder, ad-hoc reports can be created. These reports are based on Report Models.

Business users who want to create reports on demand may not be familiar with the physical data source schemas of the databases. Report Model provides user friendly and easy to understand entities based on which report creation becomes easy. These people may also not be familiar with the designer for creating reports.

Report Model thus becomes a prerequisite of Report Builder, when working with Report Builder 1.0. However with Report Builder 2.0, it is no more a prerequisite, as we can create ad-hoc reports without Report Models created previously. A report model contains metadata of data source and the relationships within. This metadata is in the form of SMDL (Semantic Model Definition Language). This is an XML based way of presenting model items. Models can be created with Report Model Template provided by BIDS. Models can also be created with Report Manager or Microsoft Office SharePoint Server 2007, but using BIDS provides maximum flexibility. You can base the model on Analysis Services cube if required. If we change the cube later, we need to regenerate the model. Report Model on Analysis Services cube cannot be created using designer.

Report Models comprise of following components

1. Entities: similar to objects in tables (entities have attributes and roles)

2. Attributes : similar to columns in tables

3. Roles: specifies relation between entities

4. Folders: used to organize entities and perspectives, certain model items can be grouped

5. Perspectives: subset of model, useful when models are very large, perspectives in themselves can contain entities, roles, folders etc.

In next article we will discuss how a model can be created using BIDS.