Ad Hoc Reporting

Thus far, we have looked at tools available in Reporting Services for developers to create and manage reports for users. At times, however, users need direct access to the reporting capability. Perhaps the need for information is immediate and no time exists to involve a report developer. Perhaps it is a one-time need and, thus, does not justify development time.

Fortunately, Reporting Services provides tools to meet this user reporting need. The Report Builder along with the Report Models provides a means for end users to explore their data without having to learn the ins and outs of SELECT statements and query builders. Best of all, the data stays in a managed and secure environment.

Here are the basic features of the two report authoring environments:

Report Builder

Report Designer (in Visual Studio 2005 or Business Intelligence Development Studio)

Targeted at business users

Targeted at IT pros and developers

Ad hoc reports

Managed reports

Autogenerates queries using Report Model layer on top of the source

Native queries (SQL, OLE DB, XML/A, ODBC, Oracle)

Reports built on templates

Free-form (nested, banded) reports

Click-once application, easy to deploy and manage

Integrated into Visual Studio

Cannot import Report Designer reports

Can work with reports built in Report Builder

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 of 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 have been created and deployed to the Report Server, 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

Like reports, Report Models are created in the Business Intelligence Development Studio, and then deployed to a Report Server. Unlike reports, Report Models can have security rights assigned to different pieces of their structure to provide the fine-grained security that is 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 then deploy the Report Model to the Report Server. Finally, we set security within the model itself.


Before defining a Report Model from a relational database, it is important that the database exhibit good design and implementation practices. Tables should have explicitly declared primary keys. Also, all foreign keys should be maintained by foreign key constraints.

Learn By Doing—Creating a Table Report

Features Highlighted
  • Creating a Report Model

  • Deploying the Report Model to the Report Server

Business Need Maximum Miniatures has decided to set up an ad hoc reporting environment for its employees. The SQL Server 2005 Reporting Services Report Model and Report Builder are to be used to implement this ad hoc reporting. The MaxMinManufacturingDM serves as the pilot database for the project.

  1. Open the Business Intelligence Development Studio.

  2. Click the New Project button in the toolbar.

  3. Make sure Business Intelligence Projects is selected from the Project Types, and then select Report Model Project from the Templates.

  4. Enter ManufacturingModel for Name and set the Location to the appropriate folder. Leave Create Directory for Solution unchecked.

  5. Click OK to create the project.

  6. 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.

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

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

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

  10. Select and complete the appropriate authentication method.

  11. Select MaxMinManufacturingDM from the Select or Enter a Database Name drop-down list.

  12. 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 have been entered properly. Click OK. You return to the Data Source Wizard dialog box.

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

  14. Enter MaxMinManufacturingDM for the Data Source Name.

  15. Click Finish.

  16. 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.

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

  18. Select the MaxMinManufacturingDM data source and click Next. The Select Tables and Views page appears.

  19. Move all of the tables into the Included Objects list.

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

  21. Enter Manufacturing Data Mart for the Data Source View Name.

  22. Click Finish.

  23. 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.

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

  25. Select the Manufacturing Data Mart data source view and click Next. The Select Report Model Generation Rules page appears as shown in Figure 15-73. This page lets us select the rules to be applied 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 can 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.

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

  27. 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, it is recommended that you select the Update Model Statistics Before Generating radio button. Therefore, we leave this radio button selected. Click Next. The Completing the Wizard page appears.

  28. Make sure the Data Source View Name is Manufacturing Data Mart. Click Run. The wizard creates the model.

  29. The wizard page shows the actions taken during each pass of the model generation process.

  30. Click Finish.

image from book
Figure 15-73: The Select Report Model Generation Rules page of the Report Model Wizard

The Report Data Model Parts and Pieces

Let's first take a look at the model that resulted from the wizard. Double-click the Manufacturing Data Mart.smdl file entry in the Solution Explorer window to open it, if it is not already open. The model appears as shown in Figure 15-74. You can see that each of the tables in the MaxMinManufacturingDM database has become an entity in the model.

image from book
Figure 15-74: Entities in the Manufacturing Data Mart Report Model

The fields from our database become attributes of our entities as shown in Figure 15-75. The attribute type is identified 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.

image from book
Figure 15-75: Attributes and roles of the Dim Machine entity in the Manufacturing Data Mart Report Model


The Report Model contains some attributes that provide a count of the number of instances of an entity. For example, Figure 15-75 shows an attribute called #Dim Machines, which provides a count of the number of machine entities. Do not confuse the # icon, which indicates the attribute type, with the # that is used at the beginning of the attribute name.

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 15-75, a machine may have many manufacturing facts associated with it. This is a one-to-many relationship. On the other hand, a machine may be associated with only one plant. Note the differing icons associated with each of these types of relationship.

Cleaning Up the Report Model

Creating the Report Model using the Report Model Wizard is only half of 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 should 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 that have cryptic names

  • Put the proper items in the Lookup folder

  • 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

In the interest of time and book pages, we will not go through these cleanup tasks as part of these exercises.

Deploy the Model

Continuing with our process, it is time to deploy the model.

  1. Right-click the entry for the ManufacturingModel Project and select Properties from the Context menu. The ManufacturingModel Property Pages dialog box appears.

  2. Enter /MaxMinReports/DataSources for the TargetDataSourceFolder.

  3. Enter /MaxMinReports /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 ManufacturingModel 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 will probably be 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, there may be important financial information in the data that should not be widely disbursed.

Let's first take a look at the Report Model using the Report Manager. Open the Report Manager and browse to the /MaxMinReports/Models folder where the model was deployed. As you can see in Figure 15-76, the entry in the folder for the Manufacturing Data Mart model looks very much like the entries we have seen for reports and shared data sources. Clicking on the Manufacturing Data Mart model opens the Properties tab for the model.

image from book
Figure 15-76: The Manufacturing Data Mart 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 that we can use the Report Manager to make security role assignments on the Report Model as a whole. The Report Manager does not enable us to make security role assignments for individual parts of the model.

The SQL Server Management Studio provides us with more flexibility. Open the SQL Server Management Studio and connect to Reporting Services. Navigate through the folders until you come to the entry for the Manufacturing Data Mart Report Model. Now double-click the entry for Manufacturing Data Mart to open the Model Properties dialog box. This is shown in Figure 15-77.

image from book
Figure 15-77: 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 15-78. This page lets you assign specific security roles to individual items within the model.

image from book
Figure 15-78: The Model Item Security page of the Report Model Properties dialog box

Check the Secure Individual Model Items Independently for This Model to allow security roles to be assigned within the model. We cannot set security on individual items within the model. You must have security assigned to at least one group or user account before you can exit this dialog box with Secure Individual Model Items checked.

Report Builder Basics

Now that a Report Model is in place on the server, users can create reports based on that model using the Report Builder. Three types of report layouts are available in the Report Builder: the table report, the matrix report, and the chart. Let's go over some of the basics.

The Report Builder is a special type of Windows program known as a ClickOnce application. This type of application is installed on your computer by following a link or clicking a button on a web form. The application is launched in the same way.

Launching the Report Builder Application

You launch the Report Builder by bringing up Report Manager in a browser, and then clicking the Report Builder button in the toolbar. You can also launch the Report Builder without first going to the Report Manager. This is done by using the following URL:


where {ReportServer} is the name of your report server.

The Report Builder launches and begins creating a new report. The Task pane is displayed on the right side of the screen. You must select a data source for the report. See Figure 15-79. Instead of basing your report on the entire Report Model, you can select a perspective from within the model. A perspective is a subset of the information in the model. Usually, a perspective coincides with a particular job or work area within an organization.

image from book
Figure 15-79: Selecting a source of data from a Report Model with four perspectives

If a plus sign is to the left of the model, then the model contains one or more perspectives. (Our Manufacturing Data Mart model does not have any perspectives defined.) Click the plus sign to view the perspectives. If you select one of these perspectives as the data source for your report, only the entities in that perspective will be available to your report. Because perspectives reduce the number of entities you have to look through to find the data you need on your report, it is usually a good idea to choose a perspective, rather than using the entire Report Model.

You must also select a report layout. The Task pane shows the three types of report layouts available in Report Builder. The table report creates a report with rows and columns. This is the most familiar report layout. In a table report, the columns are predefined by the report layout, and we will not know the number of rows until the data is selected at run time.

The matrix report creates a report containing what is known as either a crosstab or a pivot table. We do not know how many columns or rows will be in the matrix in advance because it uses data selected at run time to define both. The matrix report can be somewhat confusing the first time you encounter it, but an example usually helps. If you are fuzzy on how a matrix works, be sure to check out the sample matrix report.

The chart creates a business graphic from the data. This can be a line graph, a bar chart, or a pie chart, among other things. While you can create a basic chart with a few mouse clicks, the chart report itself has a large number of options that enable you to format the chart just the way you want it.

If you are creating a new report, select the Report Model or the perspective that should serve as the data source along with the report layout and click OK. If you want to edit an existing report, click the Open button on the toolbar. You can then navigate the Report Server folder structure to find the Report Builder report you want to edit. You cannot use the Report Builder to edit reports that were created or edited using the Report Designer in Visual Studio 2005 or the Business Intelligence Development Studio.

Entities, Roles, and Fields

Reports are created in the Report Builder using entities, roles, and fields. Entities are simply the objects or processes that our data knows something about. Dim Product, Dim Machine, and Dim Batch are all examples of entities in the Manufacturing Data Mart Report Model used in these examples. A single report may contain information from a single entity or from several related entities. Entities can be grouped together in entity folders within the Report Model or in perspectives to help keep things organized.

Roles show us how one entity relates to another entity. For example, a product is related to a product subtype through its role as a member of that subtype. A machine is related to a plant through its role as a machine in that plant.

Roles enable us to show information from multiple entities together on a single report in a meaningful manner. This may seem a bit confusing as you read about it but, remember, the roles are already defined for you by the Report Model. If the model has been created properly, you should find they are natural to the way you think about your business processes. Information from different entities should combine on your reports just as you expect, without having to get caught up the technical structure behind the relationships.

The information about the entities is stored in fields. A field is simply one bit of information: a product name, a machine number, or a date of manufacture. Fields are what we place on our reports to spit out these bits of information.

The Entities List

Once a Report Model has been selected for your data source, or an existing report has been chosen for editing, the main Report Builder opens. When creating a new report, the Report Builder appears similar to Figure 15-80. Let's take a look at each of the windows that make up this screen.

image from book
Figure 15-80: The Report Builder screen

The Entities list, in the upper-left corner, shows the entities and entity folders in the selected Report Model or perspective. All of the data in a Report Builder report comes from the entities displayed in this window. Once an entity has been selected and placed on the report, the Entities list shows that entity along with its roles.

The Fields List

The Fields list, in the lower-left corner, shows the fields available for the selected entity. Some of these fields contain information coming directly from the database, while others contain information that is calculated by the report. The icon to the left of the field identifies the type of data being stored in the field. A pound sign (#) indicates that a field contains numeric data. A small a indicates a field containing alphanumeric data. A check box indicates a field that contains yes or no, true or false data. A calendar indicates a date and time. A grouping of three yellow boxes indicates a calculated field that combines values from a number of items into one value, for example, the number of machines in the Machine dimension.

You can create your own calculated fields by clicking the New Field button at the upper-right corner of the Fields list. This displays the Define Formula dialog box shown in Figure 15-81. Use this dialog box to define your calculated field. You can create expressions by dragging existing fields onto the Formula area. The fields values can be combined using the arithmetic and string operator buttons below the Formula area. You can also use the Functions tab to reference a large number of functions that can be used in your expressions. Once you click OK to create the calculated field, it is displayed in the Fields window of the Report Builder.

image from book
Figure 15-81: The Define Formula dialog box

Clickthrough Reports

As we discussed earlier, entities are related through roles in our data model. Sometimes it can be helpful, as you are analyzing your data, to follow these role relationships through your data. The Report Builder enables you to do this by using clickthrough reports.

Clickthrough reports are automatically created when you click on a role relationship to move from one entity to another. The clickthrough reports are defined on the fly according to the data that is in the model for the entity being reported on. Each clickthrough report can lead to other clickthrough reports. Using clickthrough reports, you can continue to follow your data wherever it may lead to perform in-depth analysis.

Delivering Business Intelligence with Microsoft SQL Server 2005
Delivering Business Intelligence with Microsoft SQL Server 2005: Utilize Microsofts Data Warehousing, Mining & Reporting Tools to Provide Critical Intelligence to A
ISBN: 0072260904
EAN: 2147483647
Year: 2007
Pages: 112
Authors: Brian Larson

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: