RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Generate Ad-hoc Reports with Microsoft Reporting Services 2005 : Page 2

Among the many new features in Microsoft Reporting Services 2005 is one that can truly help reduce the workload for developers—the ability to give end users the power to generate their own ad-hoc 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:

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.

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.

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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date