his article explains the major components of Microsoft Reporting Services’ ad-hoc architecture and semantic model, and walks you through an end-to-end example that demonstrates how to author, manage, and deliver ad-hoc reports.

Microsoft Reporting Services (referred to as RS, for short, through the rest of this article) is a major component of SQL Server and one of the three pillars of the Microsoft Business Intelligence initiative. It is a complete platform for authoring, managing, and delivering standard reports. Report authors can use a visual Report Designer to create the report layout. Report administrators can publish reports and manage the report catalog using a Web-based Report Manager. Users can request published reports via standard Web protocols (URL or SOAP addressability) or administrators can push reports to end users via subscribed delivery.

While developers may rejoice over Visual Studio.NET Report Designer’s ease-of-use, less technically-savvy users may find it too complex and intimidating. Still, one of the most common data analytics features that business users request is the ability to generate reports in an ad-hoc fashion. Ad-hoc reporting lets end users run queries and create reports without having to know the technicalities of the underlying database schema and SQL query syntax.

The RS Ad-hoc Architecture
To support ad-hoc reporting, the RS 2005 architecture enhancements include several new components?shown in bold type in Figure 1.

	?
Figure 1. The RS Ad-hoc Architecture: This high-level diagram shows the relationship and technology layers between Reporting Services, data sources, and the presentation layer tools.

As in RS 2000, at the heart of the RS architecture is the Report Server, a Web-based middle-tier layer that receives incoming report requests, generates, renders, and delivers reports. Here’s a brief description of the three components involved in ad-hoc reporting.

• Model Designer?Business users need not be familiar with the technical aspects of the database schema to generate an ad-hoc report; instead, developers design and implement one or more ad-hoc models that abstract the underlying data source schema. To design the ad-hoc model, you use the Model Designer, which is one of several RS designers provided by the new Business Intelligence Development Studio. When the model is ready, you publish it to the report catalog to make it available to end users.
• Report Manager?You manage published ad-hoc models just like any other RS resource. For example, the report administrator can define Windows-based or Forms-based authentication security policies to enforce secured access to the model. To facilitate report and model management, RS provides a separate Web-based application called Report Manager, or administrators can use the new SQL Server Management Studio, which centralizes management of SQL Server, Reporting Services, Analysis Services, and Integration Services installations.
• Report Builder?End users use Report Builder to create reports against the ad-hoc model. The Report Builder is implemented as a .NET Windows Form application. The Report Server machine hosts the Report Builder assembly, distributing it to end users via the “click-once” technology forthcoming with .NET 2.0. As part of the deployment process, the Report Builder detects and optionally installs the .NET 2.0 framework if it is not already present on the target machine.

When users launch Report Builder, it prompts them to select an ad-hoc model, and then it downloads the model definition from the report catalog. Next, users build reports by choosing entities from the ad-hoc model. The Report Builder user interface is intuitive?similar to PowerPoint 2003. To author a report you simply select one of the pre-defined report templates and drag-and-drop model entities to the report canvas.

Query Translator
While designing reports, users can preview them in the Report Builder. Behind the scenes, the Report Builder generates the report definition on the fly. This differs from generating standard RS reports because the ad-hoc report definition includes a semantic query that describes the model entities selected by the user rather than being pre-defined by a developer. The Report Builder sends the report definition to the Report Server by invoking the Render method of the new ReportExecutionService Web service.

Upon receiving the request, the Report Server extracts the semantic query and forwards it to the Query Translator. The Query Translator translates the semantic query to a SQL query, using the SQL syntax understood by the underlying data source. Then, it executes the query against the data source, retrieves the data, and passes the report definition and data to the Report Processor, which is responsible for rendering the report in its final format.

RS 2005 introduces a new rendering format called Remote GDI (RGDI) for the purposes of ad-hoc reporting. RGDI is a lightweight EMF-style stream that includes additional report metadata. It is not meant to be used by other applications and hence is marked as hidden in the Report Server configuration file.

After the Report Processor generates the ad-hoc report, the Report Server streams it back to the Report Builder which displays the report to the end user. Optionally, users can save reports for later retrieval by uploading it to a report catalog folder (e.g. My Reports).

The Ad-hoc Model
The main characteristic of the ad-hoc reporting model is that it was designed with users (not the system) in mind. The RS ad-hoc model consists of data source, data source view, and semantic model layers (see Figure 2).

	?
Figure 2. Ad-hoc Model: The ad-hoc model serves as a bridge between the end users and the data.

Interestingly, you’ll find that Microsoft shares the same model across all three pillars of the Microsoft BI 2005 platform; however, the top layer differs among the three. In Analysis Services 2005, the top layer is the dimensional model, while in Integration Services (formerly known as DTS) the top layer is the control flow. Besides minimizing your learning curve, this uniform model provides consistency by letting you share data sources and data source views across the three project types in the Business Intelligence Development Studio.

Briefly, here are the components of the ad-hoc model.

• Data Source?The data source layer represents the connection to the underlying data source. Out of the box, the Query Processor supports SQL Server 2000 (and above) and Analysis Services 2005 as data sources. At present, this limitation exists because RS needs to translate the semantic query to data-source-specific SQL statements, but the final release is expected to support pluggable custom query processors that can retrieve data from other data sources.
• Data Source View (DSV)?A DSV is a metadata logical layer that isolates the ad-hoc model from the data source schema. A DSV lets you use an existing data source schema but augment it to meet your ad-hoc reporting needs. For example, suppose that you need to build the ad-hoc model on top of a vendor’s database, and further, that security or licensing restrictions prevent you from making changes to the data schema. With a DSV this predicament simply disappears. You can add any required tables to the DSV designer and adjust the schema. For example, you can define table relations and primary keys, or create virtual tables (similar to SQL views) in the form of named queries. You can also define calculated columns by adding named calculations to DSV tables.
• Semantic Model?A detailed discussion of the semantic model could easily require an article of its own, if not a book, so I’ll focus on the essential concepts. The RS semantic model is very similar to the Object Role Modeling (ORM) methodology. The main focus of the ORM methodology is to model the database schema in a way that business users can understand and verify against their requirements. To do so, ORM describes the database schema in conceptual terms such as “a Customer has Orders.” Microsoft Visio for Enterprise Architects supports drawing ORM diagrams and generating the database physical model.

Semantic Model Definition Language
Similar to the report definition language (RDL) that describes RS reports, the RS semantic model uses an XML-based grammar called Semantic Model Definition Language (SMDL). The semantic model defines the following main objects:

Entities
In the most common case, an Entity object corresponds to a DSV table or named query. An entity object consists of fields which could be either attributes or roles, described in more detail below. Each entity object has the following properties:

• IdentifyingAttributes?Specifies the attribute(s) that identify uniquely the instance of the entity.
• SortAttributes?Specifies the attribute(s) by which this entity will be sorted.
• DefaultDetailAttributes?Specifies which attribute(s) which will be displayed by default when the entity is dragged and dropped to the Report Builder report canvas.
• DefaultAggregateAttributes?Specifies which attribute(s) the entity should aggregate upon.

For easier navigation, the model designer can organize entity objects in folders.

Attributes
An attribute corresponds to a DSV column or a named calculation. Some of the most important attribute columns are:

• Binding?Specifies which underlying table column this attribute is bound to (not applicable for expression-based attributes).
• Expression?Specifies the expression for expression-based attributes. SMDL defines various aggregate (SUM, COUNT, etc.), conditional (IF), conversion, date (YEAR, DAY, etc.), logical (AND, OR), mathematical (MOD), and text functions.
• IsAggregate?Informs the client application if this attribute can be aggregated. For example, you would typically set this property to False for attributes such as phone numbers or names.
• Hidden?A hidden attribute not displayed to the end user but that you could use in expressions.
• EnableDrillthrough?Lets end users see entity details.

An interesting feature of the RS ad-hoc model is infinite drillthrough. If the EnableDrillthrough attribute property is set to True, the end user can click on the attribute in the rendered report to see the details of the entity containing the attribute. For example, the user may have a report that shows Customer Order history. If drillthrough is enabled on the Order Number attribute, the user can click on it to see the order header details. Behind the scenes, the Report Builder generates a semantic query on the fly, passing the user’s current context through to the data source. Users can keep drilling down as long as there is a path to follow from the current item.

Roles
In SMDL, a role defines an entity relationship, for example, “a customer has orders,” or “an order header has order items.” You can define different role cardinalities between entities, such as one-to-one or one-to-many. From an end-user perspective, roles define the navigational paths for entity selection. For example, if the ad-hoc model defines Customer and Product entities and they are not related, once a user selects an attribute from the Customer entity, that user is prevented from being able to add product-related attributes.

The Ad-hoc Demo
Now that you have a good high-level overview of the RS ad-hoc model, it’s worth walking through the implementation steps. Suppose that you are a Business Intelligence architect with a fictitious company called Adventure Works. The Adventure Works management has decided to empower business users by giving them an option to generate ad-hoc sales reports. To meet this requirement, you will use Microsoft Reporting Services 2005.

Creating the Report Model Project
You start by creating a new report model project in the Business Intelligence Development Studio (BI Studio) which is a lightweight shell of Visual Studio.NET. BI Studio comes bundled with SQL Server 2005 and it doesn’t require a separate license. Using BI Studio, you can design and manage BI (Reporting Services, Analysis Services, and Integration Services) projects, as shown in Figure 3. You’ll get the greatest benefit by working through the process to build the sample, but you can also download the sample project to get the code.

	?
Figure 3. BI Studio’s New Project Screen: BI Studio lets you design and manage BI projects.

Here’s how to get started:

1. Launch Business Intelligence Development Studio and create a new project.
2. From the New Project dialog select the Report Model Project template.
3. Name the project ReportBuilderDemo and click OK.
4. BI Studio generates the Report Model project template. The Solution Explorer shows Data Sources, Data Source Views, and Report Models folders that correspond to the ad-hoc model layers depicted in Figure 2.

Setting up the Data Source
Next, set up a data source that points to the AdventureWorks sample database.

1. Right click on the Data Sources folder in the Solution Explorer pane and choose “Add New Data Source.” The Data Source Wizard starts.

	?
Figure 4. The Connection Manager. You use the Connection Manager to create a new connection to a data source.

2. Click Next to advance to the “Select how to define a connection step.” Because you don’t yet have any connections defined, you need to create a new connection. Click on the New button to start the Connection Manager (see Figure 4).
3. Expand the Provider dropdown and select the SQLClient Data Provider from the .NET Providers section. Because the Report Server is implemented in .NET managed code the SqlClient provider gives you the best performance when connecting to SQL Server-based data sources.
4. Fill in the rest of the fields to finalize the data source setup. Click OK to return to the Data Source Wizard.
5. Click Finish and name your data source Adventure Works.

Creating the Data Source View
After creating a data source, you can set up the data source view on which the report model will be based.

	?
Figure 5. The Data Source Wizard: You use the Data Source Wizard to set up a data source view.

1. Right click on the Data Source Views folder in the Solution Explorer pane and choose “Add New Data Source View.” The Data Source Wizard starts (see Figure 5).
2. Click Next to advance to the “Select a Data Source” step. Select the Adventure Works data source you created in the previous section. Click Next to move on to the “Select Tables and Views” step.
3. For the sake of simplicity, this exploratory ad-hoc reporting model will be limited to five tables. These tables will allow users to browse sales order data by Product, Sales Territory, and Store dimensions. Select the Production.Product, Sales.SalesOrderDetail, Sales.SalesOrderHeader, Sales.SalesTerritory, and Sales.Store tables and then click Next.
4. Accept the default name of Adventure Works and click Finish. The Data Source View wizard closes and BI Studio loads the Adventure Works DSV in the Data Source View Designer.

Don’t be alarmed if the DSV Diagram pane doesn’t show all the table joins. This lack is related to the complexity of the AventureWorks database schema. For example, the table Sales.Store is indirectly related to Sales.SalesOrderHeader through the Sales.Customer table because Adventure Works supports two types of customers: individuals and stores. Since we didn’t select the Sales.Customer table, the relationship between the Sales.Store and Sales.Customer tables won’t be shown in the DSV Designer.

DSV gives you a lot of flexibility to adjust the database schema to fit your needs. For example, suppose that instead of bringing up the entire sales order history (some 30,000 plus orders), your ad-hoc requirements allow users to report only on orders placed in the current quarter. As a report designer, you should pay attention to the performance implications of your report queries. You can implement horizontal data filtering at the data source (by using views or stored procedures) or at the DSV level (with named queries). Further, assume that the Adventure Works security requirements rule out database changes, so the only option left to filter data is to use a DSV named query. You can filter the Sales.SalesOrderHeader table by replacing it with a named query as follows:

	?
Figure 6. Creating a DSV Named Query: Use DSV named queries to implement virtual DSV tables.

1. In the DSV diagram, right-click on the SalesOrderHeader table and choose “Replace Table With New Named Query.” The Create Named Query designer appears (see Figure 6).
2. Change the named query SQL statement to filter data for the current quarter. For this example you can hardcode the year parameter to 2004 because that’s the last year for which the Adventure Works sample database contains data. Change the named query name to Sales Order and click OK to return to the DSV designer.
3. You can annotate the DSV schema with custom table relations. For example, join the tables Sales Order and Stores by dragging the CustomerID column from the Sales Order table and dropping it onto the column of the same name in the Store table. Next, join the Order Details and Product tables by dragging the ProductID column from the Order Details table and dropping it onto the ProductID column of the Product table.

DSV also allows you to create expression-based table columns in the form of named calculations. One caveat: You must write such expressions using the syntax supported by the underlying data source. For example, to calculate the order total, right click on the Order Details table, choose New Named Calculation, and enter the following T-SQL expression in the Create New Named Calculation dialog:

	?
Figure 7. The Adventure Works Data Source View: The figure shows how your DSV diagram should look after completing the steps described thus far in this article.

   (isnull(([UnitPrice] * ((1.0) --      [UnitPriceDiscount])) * [OrderQty], (0.0))) 

After creating the named calculation, you’ll see it appear at the end of the table when you browse the table data (right-click on the table and choose Explore Data).

At this point, assuming that all is well, your DSV diagram should look similar to the one shown in Figure 7.

Now that you’ve created the Adventure Works DSV, you finalize the ad-hoc model by implementing the semantic layer.

Implementing the Semantic Layer
As an ad-hoc model designer, you will spend most of your time designing and refining the semantic layer. To jumpstart model generation, the RS team has provided (you guessed it!) a handy Wizard.

	?
Figure 8. The Report Model Wizard: Use the Report Model Wizard to generate the raw semantic layer.

1. Right-click on the Report Models folder and choose Add New Report Model to start the Report Model wizard. Click Next to advance to the Select Data Source View step.
2. Select the Adventure Works DSV you’ve just created and click Next to move on to the “Select report model generation rules” step (see Figure 8).

The Report Model Wizard generates the raw model in two passes. Each pass contains rules that could be applied when the pass is run. In Pass 1, it discovers and creates entity objects. Optionally, you can create various expression-based attributes. For example, when you select the “Create count aggregates” option, the Report Model Wizard generates count aggregates, such as # of Orders. Similarly, if you check the “Create numeric aggregates” rule, the Wizard creates the aggregate attribute on numeric fields such as sum, avg, min and max).

Pass 2 refines the model. For example, Pass 2 assigns appropriate attribute formats (currency, dates, etc).

1. Click Next to advance to the “Update statistics” step. The Report Model Wizard relies on correct database statistics (table row count, uniqueness of the column, etc) to derive to an accurate semantic model. It stores the statistics in custom properties in the data source view definition. In this step, the Report Model Wizard asks you to confirm if the database statistics are up to date. Select the “Update statistics before generating option” when you run the wizard for the first time or whenever the DSV definition has changed. Click Next to advance to the Completing the Wizard step.

	?
Figure 9. The Model Designer: You use the Model Designer to design the semantic model.

2. Name the semantic model Current Month Sales. The model name is important because this is how end users identify the model in the Report Builder. Click Run to generate the model.
3. You can edit a generated model by double-clicking on its name in the Solution Explorer. Double-click on the Current Month Sales model to open it in the Model Designer (see Figure 9).

The Model Designer lists the model entities in the left pane. When you select an entity, the Model Designer displays its fields in the right pane. As mentioned earlier, the field type could be either an attribute or a role. The Designer prefixes expression-based attributes with a number sign (#), and denotes standard attributes with an “a.”

If you have selected the “Create date variations” rule, the wizard generates automatically date parts (day, month, year, etc.) for columns of a date data type. For example, the Ship Date attribute includes the typical date hierarchy (day, month, quarter, and year), as well as the automatically generated First Ship Date and Last Ship Date.

Glancing at the Model Designer, you can easily see the roles defined for the selected entity. For example, the Sales Order entity has Customer and Territory roles that reflect the table relationships defined in the DSV diagram.

The only thing that the Model Wizard doesn’t do is read your mind, so you need to spend some time fine-tuning the model. Here are a few final touches.

1. Click on the Sales Order entity and change the SortDirection property of the Order Date to Descending so that reports will show the most recent orders first.
2. Enable drillthrough on the product name by selecting the Product entity and set the EnableDrillthrough property to True.

Deploying the Model
Now that the ad-hoc model is ready, you can publish it to the report catalog.

1. In the Solution Explorer pane, right-click on the ReportBuilderDemo project node and choose Properties from the menu. BI Studio uploads the data source definitions to the report folder specified by the TargetDataSourceFolder property. By default, that’s the Data Sources folder. Similarly, BI Studio uploads the models defined in the project to the folder specified under the TargetModelFolder property (Models by default). Finally, make sure that the TargetServerURL property specifies the correct URL to the Report Server. Change these settings as needed.
2. Close the ReportBuilderDemo Property Pages dialog.
3. Right-click on the on ReportBuilderDemo project node and choose Deploy. BI Studio will build and deploy the project.

	?
Figure 10. Report Manager Catalog View: Use the Report Manager to manage the report catalog.

4. Open the Report Manager (http://localhost/Reports). Observe the Data Sources and Models folders.
5. Click on the Data Sources folder and then on the Adventure Works data source to see its definition. BI Studio doesn’t pass the data source credentials if you’re using standard authentication. For this reason, take a moment to verify the data source authentication settings.
6. Finally, click on the Home folder and then on the Models folder. Observe that the Current Month Sales model is listed alongside any other ad-hoc models you have deployed (see Figure 10). Also, notice the Report Builder button on the toolbar.

The model administrator can use the Report Manager to manage the model. For example, if you want to enforce restricted access to the Current Month Sales model to members of specific Windows groups, you can set the security policies using the Security link on the model properties page. Note that the release version of RS 2005 will support fine-grained security where you will be able to set up security polices for individual elements of the model, such as entities and attributes.

Authoring Ad-hoc Reports
After deploying and setting up the model, end-users can author ad-hoc reports using the Report Builder. Behind the scenes, the Report Manager launches the Report Builder by requesting its click-once URL (http:///ReportServer/ReportBuilder/9.00.1090.00/ReportBuilder.application), where 9.00.1090.00 is the version number of the Report Builder application.

	?
Figure 11. Report Builder: Use the Report Builder to author your ad-hoc report by dragging model entities and attributes to the report canvas.

Here’s the procedure to author a matrix (crosstab) report that shows product sales broken down by products on rows and order date on columns.

1. Click on the Report Builder button in the Report Manager. The Report Builder launches and prompts you to select a report model.
2. Select the Current Month Sales model from the “Source of data” list and click OK.
3. The Report Builder switches to Report Designer mode (see Figure 11).

The Explorer pane lists the model entities. The Fields pane shows the fields of the selected entity. The Report Layout pane lists several pre-defined report layouts to jumpstart the report design process. The pre-release version of the Report Builder comes with two tabular and two matrix report layouts. The final version will most likely include other layouts, including a chart report layout. Unfortunately, the first release of the Report Builder will not support user-defined report layouts.

Now, create a matrix report by following these steps:

1. Choose any of the two matrix report layouts from the Report Layout pane. The Report Builder canvas displays an empty matrix template.
2. Select the Product entity in the Explorer pane and drag it to the “Drop row groups” area of the matrix report. Because Product Name is the default attribute of the Product Entity (as specified by the DefaultDetailAttributes entity property), it will be displayed in the rows of the matrix report.

Notice that once an entity attribute is added to the report, the Explorer pane changes to a tree view that shows the defined entity roles. In our case, the tree view shows the Product entity as a root level and the Order Details role as its descendant. This reflects the fact that the only role defined for the Product Entity is the Order Details role.

3. Click on the Order Details role. Observe that the Explorer tree view expands to show the Sales Order entity.
4. Drag the Sum Line Totals aggregate attribute to the “Drop totals” report area.
5. Click on the Sales Order entity. From the Fields pane drag and drop the Order Date onto the “Drop columns group” report area.

	?
Figure 12. Finished Report: The Monthly Sales Report in Report Design mode

You’re done! If you wish, you can spend some time prettying up the report by setting fonts and selecting colors for the report groups. You should also experiment with filtering the report data by using the Filter toolbar button. Note that the Report Builder allows you to use expressions in your report. To do so, right-click on a matrix group and choose the Formula context menu. Unfortunately, the first version of the Report Builder will not support .NET expressions or calling external custom .NET code.

Your final version of the matrix report should look like the one shown in Figure 12 when open in Report Design mode.

Figure 13 shows a preview-mode view of how the final rendered report might look.

	?
Figure 13. Report Preview: The Monthly Sales Report in Report Preview mode.

The report needs desperately some final touches but the pre-release version of the Report Builder doesn’t support sorting and formatting.

Viewing and Saving Ad-hoc Reports
After authoring the report, you can preview it by clicking on the View Report button. Behind the scenes, the Report Builder uploads the report definition to the Report Server instance which hosts the model and instructs the Report Server to generate the report. To initiate the drilldown feature, click on the name of a product. Whenever a user activates a drilldown link, the Report Server generates a new report that lists the details of the selected product.

Finally, you can save the ad-hoc report by uploading it to the report catalog. To do so, choose the Save main menu and select the RS folder where the report will be uploaded.

In this article, you saw an introduction to the forthcoming ad-hoc reporting model of RS 2005. The cornerstone of the RS ad-hoc model is the semantic layer that the model designer builds on top of the data source. The semantic layer organizes the data structures in entities that users can easily understand and work with. It allows less-technical users to author professional reports in the Report Designer without requiring familiarity with the database schema or SQL language. For those of you already familiar with Analysis Services, see the sidebar SMDL or UDM? for a brief comparison and recommendation as to when to use each. Happy ad-hoc reporting!