Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 17, 2006

Report Builder: Creating a Report Model - Page 5

By William Pearson

Define the Report Model

Report Models themselves represent an abstract layer, as we noted earlier, within which we can create business-based entities from which consumers can select report items. Let's create a Report Model next, and then discuss publishing it for consumer use.

1.  Right-click the Report Models folder within the Solution Explorer.

2.  Select Add New Report Model from the context menu that appears, as depicted in Illustration 23.

Illustration 23: Initial Step in Creating a New Report Model

The Report Model Wizard appears, opening with the Welcome to the Report Model Wizard page, as shown in Illustration 24.

Click for larger image

Illustration 24: The Initial Page of the Report Model Wizard

3.  Click Next.

We arrive at the Select Data Source View page, where we see RS028_Adventure Works, the Data Source View we created in the previous section, displayed within the Available data source views list box, as depicted in Illustration 25.

Click for larger image

Illustration 25: The RS028_Adventure Works Data Source View Appears as Available

Our newly created Data Source View is positioned as the default, and will serve us in the creation of our new Report Model.

4.  Click Next.

We arrive at the Select report model generation rules page, where we see the rules upon which automatic metadata generation will be based. For purposes of the current session, we will allow selection of all the defaults, as shown in Illustration 26.

Illustration 26: The Select Report Model Generation Rules Page – Default Selections

5.  Click Next.

The metadata is combined into entities. The Collect Model Statistics page appears next.

6.  Ensure that the checkbox to the immediate left of Update model statistics before generating is selected, to ensure that database statistics upon which the Report Model depends are updated from the Data Source View, as depicted in Illustration 27.

Illustration 27: Updating Statistics Prior to Generation of the Model ...

7.  Click Next.

We arrive at the Completing the wizard page, where we see that, by default, the Report Model has been given the same name as the underlying Data Source View, RS028_Adventure Works, as shown in Illustration 28.

Illustration 28: Completing the Wizard Page ...

8.  Click Run.

The statistics update occurs, and then the rules that were selected on the Select report model generation rules page are applied within the Report Model creation process. After all passes are completed, the status messages come to an end, and the Finish button becomes enabled, as depicted in Illustration 29.

Illustration 29: Processing Completes ... The Model is Generated

9.  Click Finish.

The Completing the wizard page, closes, and we are greeted with a Visual Studio message box (shown in Illustration 30) alerting us to the fact that modifications to the Data Source View have occurred outside the source editor.

Illustration 30: Microsoft Visual Studio Message Box Alerts Us To Changes ...

This alert is due to the statistics update (which we performed for practice, not because the Data Source View was likely to need an update - we had just created it shortly before). In any event, we can simply dismiss the message box without further ado.

10.  Click Yes to dismiss the message box.

The message box closes, and the new Report Model appears in the Report Models folder, within the Solution Explorer as depicted in Illustration 31.

Illustration 31: The New Report Model Appears within the Solution Explorer

Moreover, we see that the entities within the new Report Model appear on the new Report Model tab of the Designer, as shown in Illustration 32.

Illustration 32: Report Model Objects Appear within the Designer (Partial View)

11.  Click the Address entity on the Report Model tab, in the left Model tree.

We note that a list of fields, folders and roles (where appropriate), for an entity we select, is displayed in the middle pane of the tab. Moreover, properties for the selected entity appear in the Properties pane, as is the general case with objects throughout the Visual Studio environment, as depicted in Illustration 33.

Illustration 33: Selecting the Entity Displays Member Objects and Properties...

We can define new folders, source fields, expressions, roles and filters to further add to the richness of our Report Model. We do this by simply right-clicking within the pane, selecting New, and then selecting the object type from the cascading menu, as shown in Illustration 34.

Illustration 34: Embellishing the Model is a Right-click Away ...

We will work further with Report Models in subsequent articles within our MSSQL Server Reporting Services series. For the purposes of this introductory session, we will conclude with the steps required to publish our new Report Model Project in the next section.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM