Working with Report Model in SSRS - Part II

Creating Report Model using BIDS (Business Intelligence Development Studio)

In Part 1 of this series, Working With Report Model in SSRS – Overview - Part 1, we discussed the Report Model, the items in Report Model and its uses. Let us now see how a Report Model can be created with the Report Model Designer. Report Model Project is provided as a template with BIDS. With this tool you can do certain customizations to the Report Model items as required.

1. Select Report Model Project template and give it a name - “Report Model Demo”

2. Create a Data Source named “Adventure works DW 2008” pointing to AdventureWorksDW2008 database. It can be downloaded from following link http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407

3. Create a Data Source View (DSV) with following tables

namely “Adventure Works DW2008”

4. Before creating model let us do the following changes to DSV. Change the friendly names of the tables so that they look as follows:

5. Add 3 calculated fields as follows :

- Calendar Quarter Description: 'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY ' +
CONVERT(CHAR (4), CalendarYear)
- Calendar year description: 'CY '+ CONVERT(CHAR (4), CalendarYear)
- MonthName: EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)

6. In the solution explorer, right click on Report Model and select Add New Report Model which will start the wizard for model creation. It is based on the DSV created in the last step.

7. Keep default settings for Report Model Creation Rules, Collect Model Statistics, name for the model and click on Run and finally Finish.

8. Remove attributes such as Spanish Education, French Education, Spanish Occupation, French Occupation from Customer entity. You will observe that Internet Sales and Geography are added to this entity as Roles. If you select properties for any one you will see the foreign key relationship between the two specified in Bindings. Similarly you can remove descriptions from Product.

9. You can also remove attributes from Total Children like avg, min and max. You can change the alignment of numeric fields to Right.

10. Right click on “Report Model Demo” in the solution explorer and select properties. Check if the TargetServerURL is correct.

In case of a named instance of SQL Server, it should be http://<machine name>/ReportServer_<instance name>.

If the port number is other than 80 it should also be included as follows http://<machine name>:8080/ReportServer_<instance name>. Machine name can be replaced by localhost.

11. Deploy this Report Model

12. Start Report Manager by giving the URL. In case of named instance of SQL Server it should be http://<machine name>/Reports_< instance name> and verify that the newly created model is available in Models Folder. After installing Report Builder we can specify the report builder setting within which report server URL can be provided.

In next article we will discuss how to create a report using this report model with the help of Report Builder 2.0