MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

One of the biggest hurdles for nondatabase professionals to overcome when creating reports is the creation of the dataset. Unless data is being pulled from a ready-made view or stored procedure, dataset creation is going to involve the creation of a SQL SELECT statement with all the INNER JOINs and WHERE clauses that go along with it. For those who want to get the full benefits of the Reporting Services environment, becoming conversant in the SQL dialect is worthwhile. For others who would rather spend time on analyzing data than learning the ins and outs of the GROUP BY, and who would rather concentrate on aiding their organizations than becoming intimate with UNION queries, there is the Report Model.

The Report Model provides a nontechnical user with a view of database content without requiring an intimate knowledge of relational theory and practice. It hides all the complexity of primary keys and foreign key constraints. In other words, the Report Model hides the technical nature of the database and enables the users to concern themselves with the data.

Once created, the Report Model serves as the basis for report creation with the Report Builder. First, we need to have one or more Report Models built over the top of our database. Once these are created and deployed to the Report Server, then we can turn a select number of users loose to create ad hoc reports and do data analysis on the fly.

Creating a Report Model

The following sections walk through the creation of a Report Model for the Galactic database. This model is then used to construct ad hoc reports with the Report Builder in the second part of this appendix. Like reports, Report Models are created in Visual Studio 2005 or the Business Intelligence Development Studio, and then deployed to a Report Server to be used. Unlike reports, Report Models can have security rights assigned to different pieces of their structure to provide the fine-grained security often required in ad hoc reporting situations.

We use the Report Model Wizard to create the Report Model, and then do some manual tweaking to make it more usable. We deploy the Report Model to the Report Server. Finally, we look at the method for setting security within the model itself.

Create a Report Model Project in the Business Intelligence Development Studio

Follow these steps to create a Report Model project in Visual Studio 2005 or the Business Intelligence Development Studio.

1. Start up Visual Studio 2005 or the Business Intelligence Development Studio and create a new project.

2. Select Report Model Project in the Templates area of the New Project dialog box. (See Figure D-1).

   Figure D-1: Creating a Report Model project

3. Type GDS Model for the Name and select the MSSQLRS folder for the location.

4. Click OK to continue.

Create a Data Source

As with reports, Report Models require a data source to provide the information and credentials for connecting to a database. The following steps enable us to create a data source in the Report Model project.

1. Right-click the Data Sources folder in the Solution Explorer window and select Add New Data Source from the Context menu. The Data Source Wizard dialog box appears.

2. Click Next. The Select How to Define the Connection page appears.

3. If a connection to the Galactic database already exists in the Data Connections list, select this connection and go to Step 12. If there is no connection, click New. The Connection Manager dialog box appears.

4. Type the name of the Microsoft SQL Server database server hosting the Galactic database or select it from the drop-down list. If the Galactic database is hosted by the computer you are currently working on, you may type (local) for the server name.

5. Click the Use SQL Server Authentication radio button.

6. Type GalacticReporting for the user name.

7. Type gds for the password.

8. Click the Save My Password check box.

9. Select Galactic from the Select or Enter a Database Name drop-down list.

10. Click Test Connection. If a Test Connection Succeeded message appears, click OK. If an error message appears, make sure the name of your database server, the user name, the password, and the database were entered properly. If your test connection still does not succeed, make sure you have correctly installed the Galactic database.

11. Click OK. You return to the Data Source Wizard dialog box.

12. Click Next. The Completing the Wizard page appears.

13. Make sure the Data source name is Galactic. Click Finish.

Create a Data Source View

In addition to the data source, the Report Model also requires a data source view. The data source view is simply a selected subset of the tables from the data source itself. In many cases, we do not want our model to include data from all the tables in the database. Some tables might be used for logging or a temporary holding place for other operations. The data source view enables us to exclude these types of tables from the Report Model.

The following steps create a data source view for the tables in the Galactic database.

1. Right-click the Data Source Views folder in the Solution Explorer window and select Add New Data Source View from the Context menu. The Data Source View Wizard dialog box appears.

2. Click Next. The Select a Data Source page appears.

3. Select the Galactic data source and click Next. The Select Tables and Views page appears.

4. Move all the tables except the dtproperties, TransMonitorI, TransMonitorQ, and TransportMonitor into the Included objects list. Do not include any of the views in the Included objects list. The dtproperties table is a system table, so it should not be included. The other three tables are used for temporary processing and do not hold any meaningful data for ad hoc reporting.

5. Click Next. The Completing the Wizard page appears.

6. Make sure the Data source view name is Galactic. Click Finish.

Create a Report Model

With the preliminaries done, the following steps utilize the Report Model Wizard to create the Report Model.

1. Right-click the Report Models folder in the Solution Explorer window and select Add New Report Model from the Context menu. The Report Model Wizard appears.

2. Click Next. The Select Data Source View page appears.

3. Select the Galactic data source view and click Next. The Select Report Model Generation Rules page appears. See Figure D-2. This page enables you to select the rules to apply during the first pass and the second pass through the tables in the data source view. The default settings work for most data models, so we will leave the default settings. You can also select the language to use when creating your data model. The figures here use a data model generated in English.

   Figure D-2: The Select Report Model Generation Rules page of the Report Model Wizard

4. Click Next. The Collect Model Statistics page appears.

5. The data model generation process uses the database statistics in the data source view. To create a data model that best reflects the current database and how it is used, we recommend you select the Update Statistics Before Generating radio button. Therefore, leave the Update Model Statistics Before Generating radio button selected. Click Next. The Completing the Wizard page appears.

6. Make sure the report model name is Galactic. Click Run. The wizard creates the model.

7. The wizard page shows the actions taken during each pass of the model generation process. See Figure D-3. When the process is complete, click Finish.

   Figure D-3: The Report Model Wizard creating the report model

8. You may receive a message stating the data source view file has been modified outside of the editor and asking if you want it reloaded. If this message appears, click Yes.

The Report Data Model Parts and Pieces

Let’s first take a look at the model that resulted from the wizard. Double-click the Galactic.smdl file entry in the Solution Explorer window to open it, if it is not already open. The model appears as shown in Figure D-4. You can see each of the tables in the Galactic database has become an entity in the model. An entity is simply a set of things, events, or concepts of interest to us in the data world. Each individual thing, event, or concept is an instance of an entity.

Figure D-4: Entities in the Galactic Report Model

The fields from our database become attributes of our entities as shown in Figure D-5. Attributes are bits of information about each instance of an entity—the name of a particular company, the address of a particular employee. Attributes may also be referred to as fields. The field, or set of fields, that uniquely identifies a particular instance of an entity is called the identifying field. This is the primary key from the database.

Figure D-5: Attributes and roles of the Customer entity in the Galactic Report Model

The attribute type is identified in Figure D-5 by the icon to the left of each attribute name. The # notes a numeric attribute. The a notes an alphanumeric attribute. The calendar identifies a date/time attribute. The check box identifies a bit or Boolean attribute. Numeric attributes also include sum, average, minimum, and maximum aggregates. Date/time attributes also include the date parts of day, month, year, and quarter, along with aggregates for the first and last date.

Finally, in the model, entities can have various roles. Roles are created by the foreign key constraints in the database. The roles link one entity to other entities in the model. A role can be a one-to-many, many-to-one, or one-to-one relationship. For example, in Figure D-5, a customer may have many invoice headers associated with it. This is a one-to-many relationship. On the other hand, an Account Rep Employee is an account representative for many customers. From the customer entity’s point of view, this is a many-to-one relationship. Finally, a customer may have no more than one loyalty discount. This is a one-to-one relationship. Note the differing icons associated with each of these types of relationship.

Anyone familiar with entity relationship diagrams (ERD) immediately recognizes this terminology. A database designer often creates an ERD first, and then designs the database from the ERD. We are now working this same process in the reverse direction.

Cleaning Up the Report Model

Creating the Report Model using the Report Model Wizard is only half the battle. The wizard does a great job of creating the model for us. However, a number of refinements still need to be made to the model by hand to get it ready for the users.

Here are the tasks that must be accomplished to clean up the Report Model:

• Remove any numeric aggregates that don’t make sense

• Remove attributes that should not be present

• Rename entities with cryptic names

• Use folders to organize entities, attributes, and roles

• Rearrange the entity, attribute, and role order

• Manually create calculated attributes

• Add descriptions

• Create perspectives coinciding with business areas

This appendix does not provide step-by-step instructions to clean up the entire Galactic Report Model. Instead, it provides a single example of each of these cleanup tasks using the Galactic Report Model. Of course, these cleanup tasks should be applied exhaustively to your own production models.

Remove Any Numeric Aggregates That Don’t Make Sense   As stated earlier, the Report Model Wizard creates aggregates for all numeric attributes. In the case of a numeric identifying field, such as the Invoice Number, these aggregates are complete nonsense and should be removed from the model. We do not want a user using the average of the invoice numbers when they think they are getting the average of the invoice amounts! Let’s keep our users out of trouble and remove these aggregates that simply don’t add up.

1. Select the Invoice Header entity.

2. Select the Total Invoice Number, Avg Invoice Number, Min Invoice Number, and Max Invoice Number attributes.

3. Press DELETE.

4. Click OK to confirm the deletion.

Remove Attributes That Should Not Be Present   Spotting numeric aggregates that don’t make sense is a fairly easy exercise. Finding other attributes that do not belong is a bit harder. After all, each of these attributes comes from a database field and, if the information in a particular field is not useful, what is it doing in the database in the first place? Still, there may be fields in the database for system tasks, housekeeping chores, or security purposes, which should not be available to end users.

The Galactic Report Model provides an example in the Employee entity. There are fields to hold a user name and the pieces necessary for password encryption. Nothing good can come from having these exposed to prying eyes!

1. Select the Employee entity.

2. Select the User Name, Password Hash, and Password Salt attributes.

3. Press DELETE.

4. Click OK to confirm the deletion.

Rename Entities with Cryptic Names   The whole reason for the Report Model is to make it easy for users to create ad hoc reports without having to know the technical ins and outs of the database. Therefore, we want to make sure our entities, attributes, and roles have names that are obvious to the users. Any names that include acronyms and abbreviations that are not well known to the users should be changed.

I will give myself a pat on the back here and state that the names used in the Galactic database are pretty self-explanatory, so the entity, attribute, and role names in the model are, as well. This is an endorsement for good database design. However, here is one example of a name generated by the wizard that could be improved on.

1. Select the Employee entity.

2. Right-click the Account Rep Employee role and select Rename from the Context menu.

3. Type Accounts for this Account Rep as the new name and press ENTER.

Use Folders to Organize Entities, Attributes, and Roles   We can create folders to help organize our entities and attributes. Not only can these folders help us with organization, but we can also assign specific security roles to these folders. Entities and attributes that should have more restricted access, therefore, should be put in their own folders.

We put the entities related to the employee evaluations in a separate folder to make it easier to assign security later. We put the contact-related attributes in separate folders in the Customer entity to keep things more organized.

1. Select Model in the left-hand pane.

2. Right-click in the right-hand pane and select New|Folder from the Context menu.

3. Rename the new folder Employee Evaluation.

4. Select the Evaluation, Goal, Performance, and Rating entities in the right-hand pane.

5. Drag these entities to the Employee Evaluation folder and drop them there.

6. Select the Customer entity.

7. Make sure no attribute or role is selected, and then right-click and select New|Folder from the Context menu.

8. Rename the new folder Billing Contact Info.

9. Select all the billing contact-related attributes in the right-hand pane.

10. Drag these to the Billing Contact Info folder and drop them there.

11. Make sure no attribute or role is selected, and then right-click and select New|Folder from the Context menu.

12. Rename the new folder Problem Contact Info.

13. Select all the problem contact-related attributes in the right-hand pane.

14. Drag these to the Problem Contact Info folder and drop them there.

Rearrange the Entity, Attribute, and Role Order   Entities and attributes can be arranged in any desired order in the model. By default, entities appear in the model in alphabetical order, while attributes and roles appear in the order in which the fields and foreign key relationships are encountered in the database. This may not be the most appropriate order.

You may want to group your entities together into related groups. This is a bit difficult in the Galactic model because so many entities are interrelated. Instead, let’s keep the entities arranged alphabetically. However, several of the items we moved around are not in alphabetical order, so we still have some rearranging to do. In the Delivery entity, it might make more sense to have the Pickup Date Time attribute right before the pickup contact and address information. The same is true with the Delivery Date Time.

1. Select the Delivery entity.

2. Right-click the Pickup Date Time attribute and select Move Up from the Context menu.

3. Repeat Step 2 until the Pickup Date Time attribute is immediately above the Pickup Contact attribute.

4. Use the same process to put the Delivery Date Time attribute immediately above the Delivery Contact attribute.

Manually Create Calculated Attributes   The Report Model Wizard created some calculated attributes containing counts, aggregates, and date parts. Some additional calculated attributes may be helpful. These calculated attributes could contain arithmetic calculations or even string concatenations. We look at an example of each of these.

1. Select the Delivery entity.

2. Right-click and select New|Expression from the Context menu. The Define Formula dialog box appears.

3. In the Entities list, select Service Type.

4. Drag the Cost field from the Fields list to the Formula area.

5. Click the minus (−) button.

6. In the Entities list, select Delivery.

7. Drag the Discount field from the Fields list and drop it after the minus sign in the Formula Area.

8. Click OK.

9. Rename the NewExpression attribute you just created to Net Cost.

10. In the Properties window, set the Nullable property of the Net Cost attribute to True.

11. Select the Employee entity.

12. Right-click and select New|Expression from the Context menu. The Define Formula dialog box appears.

13. Drag the First Name field from the Fields list to the Formula area.

14. Click the ampersand (&) button.

15. Type " " and click the ampersand button.

16. Drag the Middle Initial field from the Fields list and drop it after the ampersand.

17. Click the ampersand button.

18. Type " " and click the ampersand button.

19. Drag the Last Name field from the Fields list and drop it after the ampersand.

20. Click OK.

21. Rename the NewExpression attribute you just created to Full Name.

22. In the Properties window, set the Nullable property of the Full Name attribute to True.

Add Descriptions   Perhaps the most helpful thing you can do during this entire cleanup process is to add descriptions to each entity, attribute, role, and folder. These descriptions are displayed to the users when they hover the mouse over an item during report creation. The descriptions enable you to provide a detailed explanation of each item. These detailed descriptions can insure the users are selecting the correct items and getting exactly the information they are looking for. We are only entering one description as an example here. In your production Report Model, descriptions should be entered for all entities, attributes, roles, and folders.

1. Select the Employee entity.

2. Select the Full Name attribute you just created.

3. In the Properties window, enter The first name, middle initial, and last name of the employee. for the Description property of the Full Name attribute.

Create Perspectives Coinciding with Business Areas   Perspectives hide some of the complexity of the complete data model by grouping entities into logical units. Perspectives organize entities into sets that are likely to be used together by a given set of ad hoc report users. As you have seen, entity folders can also be used to group entities, but with two major differences. More on those differences in a moment.

Let’s consider the groupings first used to introduce the Galactic database in Chapter 3. The tables were presented in four diagrams, each relating to a different functional area of Galactic Delivery Services: Accounting Information, Package Tracking, Personnel Information, and Transport Maintenance. We use these four groupings to create four perspectives in the Report Model.

Someone working in the accounting department is most likely to run reports from the tables in the Accounting Information diagram. Conversely, someone in the package tracking area will have little interest in the Accounting tables, but will primarily report from the Package Tracking tables. Both of these people are interested in the content of the Customer table, however. This means both want the Customer table to show up in their perspective. Fortunately, an entity can appear in multiple perspectives.

This, then, is the first difference between perspectives and entity folders. A single entity can show up in multiple perspectives, but it can only reside in one entity folder. The second difference between perspectives and entity folders is security roles can be assigned to entity folders, but they cannot be assigned to perspectives.

Let’s create our four perspectives.

1. Select Model in the left-hand pane.

2. Right-click and select New|Perspective from the Context menu.

3. Click Clear All.

4. Select the following entities:

   • BusinessType

   • Customer

   • Customer To Business Type Link

   • Invoice Batch

   • Invoice Detail

   • Invoice Header

   • Loyalty Discount

   • Payment

   • Payment Invoice Link

   Note

   Other entities related to the entities you select are implicitly checked and marked with a dark gray checkmark in a light gray check box. Explicitly check all of the entities listed here, so they are marked with a black checkmark in a white check box. After you make all the explicit selections, leave the implicit selections as they are.

5. Click OK.

6. Rename the new perspective to Accounting Information.

7. Right-click on Model in the left-hand pane and select New|Perspective from the Context menu.

8. Click Clear All.

9. Select the following entities:

   • Customer

   • Delivery

   • Hub

   • Lost Package

   • Planet

   • Service Type

   • Transport

10. Click OK.

11. Rename the new perspective to Package Tracking.

12. Right-click on Model in the left-hand pane and select New|Perspective from the Context menu.

13. Click Clear All.

14. Select the following entities:

    • Assignment

    • Employee

    • Evaluation (Remember, this is located in the Employee Evaluation folder)

    • Goal (Remember, this is located in the Employee Evaluation folder)

    • Job

    • Payroll Check

    • Performance (Remember, this is located in the Employee Evaluation folder)

    • Rating (Remember, this is located in the Employee Evaluation folder)

    • Time Entry

15. Click OK.

16. Rename the new perspective to Personnel Information.

17. Right-click on Model in the left-hand pane and select New|Perspective from the Context menu.

18. Click Clear All.

19. Select the following entities:

    • Fuel Price

    • Propulsion

    • Repair

    • Repair Cause

    • Repair Work Done Link

    • Scheduled Maint

    • Transport

    • Transport Type

    • Work Done

20. Click OK.

21. Rename the new perspective to Transport Maintenance.

22. Click Save All in the toolbar.

Deploy the Model

Once you have the Report Model looking the way it should, it can be deployed to the Report Server. This is done in a manner similar to the process for deploying reports.

1. Right-click the entry for the GDS Model Project in the Solution Explorer window and select Properties from the Context menu. The GDS Model Property Pages dialog box appears.

2. Enter /Galactic Delivery Services/Shared Reports for the TargetDataSourceFolder. This causes the Report Model to use the Galactic shared data source that already exists in this folder.

3. Enter /Galactic Delivery Services/Models for the TargetModelFolder. This creates a new folder to contain the Report Model itself.

4. Enter http://{ReportServer}/ReportServer for the TargetServerURL where {ReportServer} is the name of the Report Server.

5. Click OK to exit the dialog box.

6. Right-click the entry for the GDS Model Project and select Deploy from the Context menu. The model deploys to the server. You receive one warning stating that the shared data source cannot be deployed because it already exists.

Secure the Model

The number of people who have access to the Report Model for ad hoc reporting is probably larger than the number of people who have access to the database for report authoring. This wider audience and increased exposure makes security doubly important. Personal information such as Social Security numbers, pay rates, and employee’s health care information must be protected. In addition, important financial information, which should not be widely disbursed, may be in the data.

Let’s first take a look at the Report Model using the Report Manager. Open Report Manager and browse to the /Galactic Delivery Services/Models folder where the model was deployed. As you can see in Figure D-6, the entry in the folder for the Galactic model looks much like the entries we have seen for reports and shared data sources. Clicking on the Galactic model opens the Properties tab for the model.

Figure D-6: The Galactic Report Model deployed to the Report Server

The General page, the Data Sources page, and the Security page on the Properties tab for the Report Model look and function almost identical to their counterparts for a report. This means you can use the Report Manager to make security role assignments on the Report Model as a whole. The Report Manager does not let you make security role assignments for individual parts of the model.

The SQL Server Management Studio provides you with more flexibility. Open the SQL Server Management Studio and connect to the Report Server. Navigate through the folders until you come to the entry for the Galactic Report Model. Now, double-click the entry for Galactic to open the Model Properties dialog box. This is shown in Figure D-7.

Figure D-7: The Report Model Properties dialog box in the SQL Server Management Studio

The pages of the Report Model Properties dialog box in the SQL Server Management Studio mirror the property pages available in the Report Manager. The one exception is the Model Item Security page shown in Figure D-8. This page enables you to assign specific security roles to individual items within the model.

Figure D-8: The Model Item Security page of the Report Model Properties dialog box

Check Secure Individual Model Items Independently for This Model to allow security roles to be assigned within the model. Next, create independent security entries for the root (Galactic) folder of the model. You must create at least one independent security entry for the root folder before you can create entries for model items and child folders.

Next, navigate to an item within the model that should have its own security assignment. For example, the Employee Evaluation folder in our model should have different security role assignments than the rest of the model. You can select the Employee Evaluation folder within the model and click Use These Roles for Each Group or User Account. This breaks the security roles this model folder would otherwise inherit and enables you to set your own. You can then use the Add Group or User button to add a security role assignment to a user or a group.