Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
9 out of 19 rated this helpful - Rate this topic

Integrating Analysis Services with Reporting Services

SQL Server 2000
 

Sean Boon
Microsoft Corporation

June 2004

Applies to:
   Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios. (33 printed pages)

Download the associated ASandRSSamples.exe code sample.

Download the ASandRSSamples.exe source code.

Contents

Introduction
Developing OLAP Reports Using Analysis Services 2000 and Reporting Services
Datasets and Data Regions in SQL Server 2000 Reporting Services
Defining a Data Source
Building a Static Report with Analysis Services Data
Adding Parameters to an OLAP Report
Adding Additional Interactivity to Reports
Analysis Services Actions
Conclusion

Introduction

There are many features of Reporting Services that can be beneficial to Analysis Services customers. These features include centralized report storage and management, control over how reports are processed or administered, and the ability to quickly change report formats (HTML, Excel, PDF, etc.). With these capabilities, Reporting Services is a valuable client application for Analysis Services implementations.

Likewise, there are features of Analysis Services that may complement many reporting scenarios. Analysis Services users already benefit from the ability to author complex analytical queries using MDX() and have grown accustomed to fast query response times via MOLAP partitions.

The good news is that both of these technologies can be used together. Reporting Services 2000 can consume data from Analysis Services databases and Analysis Services can render Reporting Services reports. The goal of this whitepaper is to demonstrate how both of these technologies that ship with SQL Server 2000 can be used collectively to create compelling solutions for your customers. In addition to the material presented in this whitepaper, the report files used to illustrate the concepts here are included in the associated sample download. The sample files contain additional .rdl files not specifically referenced in this article that demonstrate connectivity to Analysis Services, as well as a copy of the Foodmart database with changes to the cube structure to illustrate points made in this article. Readers are encouraged to examine these files as they work through the presented material.

Developing OLAP Reports Using Analysis Services 2000 and Reporting Services

Analysis Services is the OLAP server that is part of SQL Server 2000. Like all OLAP servers, Analysis Services provides a multi-dimensional space for users to navigate data that is important to them and do so in a manner that facilitates analysis. For example, in order to navigate the multi-dimensional space of an Analysis Services cube, applications take advantage of hierarchical structures that define that space and use a query language known as MDX (Multidimensional Expressions) to return results. This means that if we are going to author reports that include data from Analysis Services cubes, the majority of the discussion is going to be centered on how to use the MDX query language within Reporting Services. While the MDX concepts here are explained in detail, the author would highly recommend that the reader have a good understanding of MDX before proceeding.

Datasets and Data Regions in SQL Server 2000 Reporting Services

When authoring reports in Reporting Services, you will inevitably become familiar with the concept of datasets and data regions. Datasets represent the results of queries returned by data providers. They might represent a list of employees or a table of product sales listed by month.

Data Regions consume data provided by datasets. Data Regions include report objects such as tables, matrices, lists, and charts. Each of these data regions expects the data in datasets to be returned in a specific format. For example, if you are working with a list data region and wanted to display a list of products, you would return at least a single field that would include the name of the product. In its simplest form, the dataset would look like the following:

Product Name
Apples
Pears
Oranges
Bananas

As a report designer you would then drag the "Product Name" field into the list data region, and when the report is processed, the list of products would be displayed—a row for each one. This is what the list control does. The list control loops through the dataset and returns the field value(s) for the current row and displays the value of the field in the list. You can have multiple fields in the list, but that doesn't change the behavior of how the list control loops through the dataset.

However, if your goal was simply to write and execute a query that returns a list of products, it is possible that the dataset returned could look like the following.

ApplesPearsOrangesBananas

While the above dataset meets the requirement to return a set of products, it would be relatively difficult, if not impossible, to represent a product list in the report based on such a dataset. The reason for this is that each product would have its own field, and as new products are added and old products are removed from the company's inventory, the reports would begin to break because some of the fields would no longer be valid.

The point of this example is to illustrate that each data region in a report expects data in a dataset to be presented in a certain manner. In this example pertaining to the list control, the list expects the data to be returned will be listed in a single field. The list will iterate over each record in the data set and add the value of the current record to the list. Other data regions expect their supporting datasets to adhere to specific structures as well.

Since the structure of the dataset is important, and the structure of the dataset is defined by the query, it follows that the syntax of queries is important when designing reports. This is the case for SQL, MDX, and any other query language. With the focus of this whitepaper being on developing reports based on data from Analysis Services cubes, we'll examine what needs to be done in order to author MDX statements for use in reports. For example, the two MDX queries below return the exact same information. However, only one is ideal when used as a source for a matrix in a report.

MDX Expression

SELECT 
     CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members})  on columns, 
    NON EMPTY ([Store].[Store City].members) on rows

FROM SALES

MDX Expression

SELECT 
     CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members})  on columns, 
    NON EMPTY ([Store].[Store City].members) on rows

FROM SALES

By the time you've finished reading this whitepaper, you should be able to discern which of these expressions will work as the source of a matrix, and which will not. With that as our goal, let's begin developing reports with Analysis Services cubes.

Defining a Data Source

Just as with any report defined using Reporting Services, the first step in building an OLAP-based report is to start Visual Studio, and from the list of available project types, select "Business Intelligence Projects" and the "Report Project" project type. This creates an empty report project in Visual Studio that will serve as the starting point for this demonstration.

The next step in the creation of the report is to define a data source for the project that either one or many reports will utilize. Data sources represent connection information to any supported data source. To create a report that will contain data from an Analysis Services cube, select the "Microsoft OLE DB Provider for OLAP Services" from the list of available provider types. Once the provider is selected, select the Connection tab and enter the name of the server to connect to as the data source property. Choose Windows NT Integrated Security as the connection method, and then a list of Analysis Services databases should be available to choose from as the initial catalog. For the purposes of this demonstration, select the "Foodmart 2000" database as the initial catalog. Defining a data source to an Analysis Services database is that simple.

Building a Static Report with Analysis Services Data

The simplest of reports generally consists of a static view of the data. While one of the benefits of using Analysis Services is to enable users to "slice and dice" a multi-dimensional view of the data, it's also possible to use an OLAP cube to build a static report. Such a report might be a monthly sales report that is distributed to all employees. In this case there is no slicing or dicing of the report. It might be printed, e-mailed, or sent in an Excel spreadsheet, but the data contained in the report is static and is intended to stay as such.

If you're already using Analysis Services, developing such static reports using Reporting Services is relatively straightforward. With a little knowledge of MDX, the query language for OLAP cubes, you can create a simple report. A more in-depth discussion of the MDX query language is not the focus of this whitepaper and there are many excellent books and resources on MDX available.

Once a data source exists in your project, the next thing you'll likely want to do is to create a report. To do this, add a new report item of type "report" to the Visual Studio project and give the resulting .rdl (report definition language) file a name. At this point, create a new dataset and give it an appropriate name for the data it represents. Select the "Text" command type and then enter the MDX expression that will provide the data for your report. For our example, you could use an MDX expression such as the one provided below.

MDX Expression

SELECT 
    {[Measures].[Unit Sales]} on columns, 
    ORDER([Promotion Media].[Media Type].members,
    [Measures].[Unit Sales],DESC) on rows

FROM SALES

This example MDX query asks for a list of promotion media types and the sales associated with them. The list is ordered by using the ORDER() MDX function that requires an argument for ordering the list and a direction to sort the data in. Running this query in the dataset designer should provide a dataset that consists of two fields (Promotion_Media_Media_Type and Measures_Unit Sales). These two fields are now available to the report designer.

At this point you will also notice that the query builder functionality for relational data sources is not available for Analysis Services cubes. This functionality is scheduled to be included in the next version of Reporting Services. In the interim, the query designer will allow you to enter MDX into the designer and will return result sets and fields that can be used in reports.

To create this report, add a table to the report in the report designer. Add the Promotion Media Type field to the table in the first (left-most) column of the report. Fix the name of the field that appears as the column header and then drag the Unit Sales field to the last column of the table in the details section. If you preview the report now you should see the results of the dataset.

There are probably some final touches you'll want to make to this report. For example, the unit sales should be formatted to appear as amounts in the currency they were recorded in and you'll probably want to add a title to the report so that consumers of the report will understand what data is represented in the report. Such cosmetic changes are fairly straightforward and we'll leave that as an exercise for the reader.

With that, you have successfully created a report based on data from an Analysis Services OLAP cube. It's a fairly simple report but it captures the basics of creating reports based on Analysis Services cubes.

MDX Guidelines for Defining Datasets

When building datasets based on Analysis Services cubes, there are some guidelines you'll need to follow due to the structure of the data that is returned from Analysis Services. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid. For those who are familiar with the MDX Sample Application, which displays the cellset representation of the data, you'll find that the two representations will not necessarily be the same. For example, if you run the following query in the MDX Sample Application and in the Report Designer for Reporting Services, you'll find that the data is represented differently.

MDX Expression

   SELECT {[Measures].members} on columns,
{Store.members} on rows
FROM Sales

The first thing you'll notice is that the data returned in the MDX Sample application contains only one column to represent the members of the store dimension. In the Reporting Services' query designer, the store dimension is broken into each of its levels and a field is created for each level. This is the result of the flattening process that occurs when a cellset is converted to a recordset.

Note   When designing datasets based on MDX, each level of a dimension that participates in a query is returned as its own field.

The next guideline to consider, and one that will become evident as you work through the examples in this whitepaper is that for most of the things you'll want to accomplish in a report, the best way to formulate your MDX queries is to place the members of the measures dimension on the columns axis. This means that the majority of your MDX queries will contain the following fragment.

MDX Expression

SELECT [Measures].[MeasureName] on Columns,
Tip   When designing datasets based on MDX queries for tables, matrices, and charts, use MDX queries that place members from the Measures dimension on the "Columns Axis". Use the "Rows Axis" and "Page Axis" for returning members from the remaining dimensions.

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note   The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

Aa902647.olapasandrs_01(en-us,SQL.80).gif

...or by stating it in the MDX Query used to define the dataset. An example of specifying a calculated member at a level below the "All" level is as follows.

MDX which returns the name of the calculated member

WITH 
  SET TopStores AS '{TOPCOUNT([Store].[Store Name].members, 5, 
  [Measures].[Units Ordered])}'
  MEMBER [Store].[All Stores].[Top 5 Store Total] AS 'SUM(TopStores)'

The following query would not return the name of the [Top 5 Store Total] member.

MDX which does not return the name of the calculated member

WITH 
  SET TopStores AS '{TOPCOUNT([Store].[Store Name].members, 5, 
  [Measures].[Units Ordered])}'
  MEMBER [Store].[Top 5 Store Total] AS 'SUM(TopStores)'
Tip   When referencing calculated members that are not on the Measures dimension, specify a parent for the member.

One final aspect of the flattening algorithm used to return MDX results as recordsets to Reporting Services is how member properties are treated. When the DIMENSION PROPERTIES syntax is used in an MDX query, the flattening algorithm will discard the default fields that are returned from the query and will instead return fields for the properties requested. For an example of this see the MDXDataSets.rdl file included with this whitepaper.

Instead of using the DIMENSION PROPERTIES syntax, one should consider creating a calculated member along the Measures dimension and returning the value for the member property as the value of the measure. This has the added benefit of allowing you to test the value of the property on records where it's not specified. This could be particularly important if you attempt to perform grouping operations on the data in the report.

MDX with DIMENSION PROPERTIES Syntax (not recommended)

SELECT {[Measures].[Unit Sales]} on Columns,
{Store.[Store Name].members} DIMENSION PROPERTIES [Store].[Store Name].[Name],
[Store].[Store Name].[Unique_Name],[Store].[Store Name].[Store Sqft]
on rows

FROM SALES

Where [Time].[1997]

MDX Query with Calculated members to return member properties

with member [Measures].[StoreName] as 'Store.currentmember.name'
member [Measures].[Store Sqft] as 'Store.currentmember.properties("Store Sqft")'
member [Measures].[Store UniqueName] as 'store.currentmember.properties("Unique_Name")'

SELECT {[Measures].[StoreName],[Measures].[Store Sqft], 
[Measures].[Store UniqueName],[Measures].[Unit Sales]} on Columns,
{Store.[Store Name].members} 
on rows

FROM SALES

Where [Time].[1997]
Tip   Using the DIMENSION PROPERTIES Keywords in an MDX query causes only the member properties requested to be returned as fields. The fields that are usually returned when the DIMENSION PROPERTIES keywords are not used will not be included. Instead of using the DIMENSION PROPERTIES syntax, create calculated members on the Measures Dimension to return the member properties. This will preserve each level being returned as a field in the result set.

Adding Parameters to an OLAP Report

The previous report is a very basic report built with data from an Analysis Services cube. It served to illustrate how to connect to a cube and to indicate that it's possible to write an MDX query and use those results from the query in a report. Before long though, if you're developing reports, one of the first things you'll want to add to your reports are parameters. Returning to our previous sales example, you might want to allow the user to change the month of the report, or to pick a quarter or year and have the report change to reflect the user's input. For relational data sources, adding parameters to reports is fairly easy since the designer has native support for them. For reports based on OLAP data though, this can be a bit more difficult since Analysis Services does not have native support for parameterized queries. The next example will illustrate how to simulate parameterized reports for Analysis Services using Reporting Services.

Use MDX Queries to Build Parameter Lists

To create a parameter for a report based on Analysis Services data create a new dataset that will be used to populate the parameters values. While it's possible to create parameters without requiring an actual dataset (for example, you could allow users to enter any arbitrary value for a parameter), this is generally not considered a good approach because Analysis Services will return an error if the parameter value that is entered by the user cannot be resolved at query time. This is different from traditional T-SQL queries where if a member is not found, no records will be returned in the dataset for that entity.

For example, suppose that a user were to enter the value of "Calif" to represent the state of California as a parameter value when viewing the report. If there were no dimension member in the Analysis Services cube with the name of "Calif", the report would return an error back to the user. For SQL Server, if a user entered "Calif" as the parameter value, if there were no records that matched "Calif", the server would not issue an error and the report would run, but there would be no records in the report. Both results could be problematic for your users since there might indeed be data for California and the user expected to see it, but the lack of a matching record is handled differently by both systems.

There are, however, many ways to solve this potential problem. From an Analysis Services perspective, one way to solve this problem is to ensure that the user selects an available dimension member by providing the user with a list of dimension members that exist in the cube. To do this, you'll want to create a new dataset that will be used to populate the report parameter. Once the dataset is created, the next thing to do is to issue an MDX query that will be used to populate the parameter values. Our example scenario in this case is that you'd like to give the user the ability to filter the previous report on unit sales associated with various promotion media types by any member of the time dimension.

Tip   When designing reports based on MDX queries that use parameters, it's considered a good practice to limit the parameter values to members that exist in the dimension. If an MDX query references non-existent members, Analysis Services will return an error.

One of the great features of Analysis Services is that it allows users to view data along dimensions that can consist of one or many levels. Most cubes contain some type of time dimension that can contain any number of levels. In the Foodmart 2000 Sales cube, the time dimension contains the following levels: (All), Year, Quarter and Month. To populate the dataset for the parameter you could use a query like the one provided below.

MDX Expression

with Member [Measures].[TimeMemberUniqueName] as '[Time].currentmember.UniqueName'

member [Measures].[TimeDisplayName] as 
'Space([Time].Currentmember.Level.Ordinal * 4) + Time.Currentmember.Name'

SELECT 
{[Measures].[TimeMemberUniqueName],[Measures].[TimeDisplayName]} on Columns,
{[Time].members} on rows
from  
SALES

For those familiar with MDX, using the above query to retrieve a list of members for the time dimension might seem like overkill. However, if you analyze each of the fragments of this query, you'll see that each part of the query serves a function.

MDX Expression to Retrieve Member's Unique Name

with Member [Measures].[TimeMemberUniqueName] as '[Time].currentmember.UniqueName'

The first part of the MDX expression serves to retrieve the unique name property of the dimension member. This is the field that will ultimately be used as the value field when constructing the final MDX query that will return the results to be used in the report. By having the unique name of the member available to us it makes it much easier to construct the MDX we need. For example, suppose that the user is interested in the member "January 1997". The unique name for this member is [Time].[1997].[Q1].[1]. If the user simply selected "1" (the value for January that will ultimately be displayed in the parameter drop-down list) it would be impossible to figure out which "January" the user wants based solely on the member's name (which in this case happens to be just the value "1"). By returning the fully qualified name for the dimension member, it'll make it easier to construct the final MDX use to return the results to the user.

MDX Expression to Retrieve Member's Name

member [Measures].[TimeDisplayName] as 
'Space([Time].Currentmember.Level.Ordinal * 4) + Time.Currentmember.Name'

The second part of the MDX expression retrieves the member's display name and is what we'll use to populate the values that the user can select from in the parameter drop-down box. Notice also here that the Visual Basic for Applications (VBA) Space() function is used in conjunction with retrieving the current member's level ordinal property and multiplying that by a value of four. This is not necessarily a requirement, but is one way that you can make the parameter drop-down list look more like a hierarchy. If you examine the records that are returned for this field in the dataset, you'll notice that the deeper they are in the hierarchy, the more padding space is added at the beginning of the member's name.

Tip   When designing reports based on MDX queries that use parameters, create calculated members on the Measures dimension and use the UniqueMemberName and Name functions to return these values to use as the parameter's value and label properties respectively. This also has the added benefit of producing a single field for which the parameter can be sourced from.
Tip   When designing reports based on MDX queries that use parameters, by using the VBA Space() function in conjunction with Level.Ordinal you can simulate a hierarchy within the parameter drop-down list.

Finally, the SELECT portion of the MDX query returns the member's unique name and it's more friendly display name that the user will see in the parameter drop-down list.

MDX Select Statement

SELECT 
{[Measures].[TimeMemberUniqueName],[Measures].[TimeDisplayName]} on Columns,
{[Time].members} on rows
from  
SALES

Now that there are two calculated members, the final step for building the dataset that will be used to populate the parameter list is to finish out the query by specifying that we'd like to see all of the members of the time dimension in our result set. The query ultimately returns three additional fields (one for each level of the time dimension) in addition to the fields that represent the calculated members for the unique name and the display name. While we won't be using the first three fields of the dataset, it's worth commenting why they are returned.

In order to retrieve data from an Analysis Services cube and present it in a two-dimensional manner, a process of flattening is used to return the data in a recordset. For more information on the flattening algorithm see the OLE DB documentation. The result of this process is that each level represented by a dimension member in a query is returned as a field in the recordset. In the case where we ask for all of the members of the time dimension, since there are three levels to the time dimension, three fields are produced in the result set. This creates a problem for building parameters since parameters are bound to a single field. If you're limiting the parameter list to a single level of a dimension it is fairly straightforward to work around this since every member to be included in the parameter drop-down box would be from the same field in the dataset. However, if you'd like users to be able to select a member from any level of the dimension, then using the approach of creating a calculated member and including it in your dataset query (as our example shows) returns a single field to a dataset and the parameter list can be bound to that single field.

Using Parameters to Filter Data in the Report

The whole purpose for creating parameters is to filter the data that is displayed in the report. To accomplish this we are going to rely on the ability of Reporting Services that enables queries to be generated dynamically at runtime. Treating the MDX query as an expression does this. Again, to illustrate this technique we'll provide an example.

If we return to our static report example, we'd like to be able to "slice" the report by a particular time member. To do this, add a new dataset to the report and copy the MDX query string from the static report example to your new report. At this point, your report is not parameterized, but you'll find that when you create parameterized MDX reports, the first thing you'll want to do is write the MDX query in the designer without the parameter. This allows you to preview the fields of the query and enables the designer to recognize the fields that will be used in the report. Without performing this initial step, it would be very difficult to design the report since the fields would not be available to the designer.

Tip   When designing reports based on MDX queries that use parameters, first write the query in the designer without the parameters, so that the fields will become available to the designer.

Now that the designer recognizes the fields that will be in the report, we'll modify the query to work with the Time parameter. Since Analysis Services does not support parameterized queries, the way to accomplish this is by dynamically building the query at the report's execution time to reflect the user's parameter selection. To do this, we're going to rely on the ability of Reporting Services to recognize certain report properties as expressions. Whenever Reporting Services encounters an equals sign (=) as the first character of a property, it will evaluate that property as an expression. Then, the next step is reference the time parameter's value in the query. The final MDX query for this report is presented below.

MDX Select Statement

="SELECT {[Measures].[Unit Sales]} on columns, 
ORDER([Promotion Media].[Media Type].members,[Measures].[Unit Sales],DESC) 
on rows FROM SALES WHERE (" & Parameters!pTime.Value & ")"
Tip   When designing parameterized reports that use MDX, dynamically build the MDX string by treating the command text property as an expression. The string must not contain any carriage returns or an error will be returned.

The last step for building the parameterized report is to set up the report parameter. To accomplish this, add a report parameter (in our example we called it "pTime") and bind it to the "dsTime" dataset. The value field for the parameter should be set to the "Measures_TimeMemberUniqueName" field and the label field should be set to the "Measures_TimeDisplayName" field. Below is the report parameters dialog from this report.

Aa902647.olapasandrs_02a(en-us,SQL.80).gif

The final version of this example report is included in the file named FoodmartSimpleParameter.rdl.

Options for Working with Large Dimensions

Large dimensions and dimension levels can be challenging to work with. One of the primary issues that arise in working with large dimensions, one which all OLAP applications have to resolve, is what is the best way for users to sort through the potential large set of dimension members and make a selection. For Reporting Services, you probably would not have a parameter list that contains thousands of items. Depending on your usage of large dimensions in your reports, you might need to consider some of the following techniques.

Use Cascading Parameters

One way to limit the possible choices for the user is to organize the dimension members into a meaningful hierarchy. Making use of levels in Analysis Services best does this. To illustrate the use of cascading parameters, will use an example from the Foodmart 2000 Sales Cube.

The Sales cube from the Foodmart database contains a Customer dimension with the following levels: (All), Country, State Province, City, and Name. The "Name" level of the customer dimension contains a little over 10,000 customers. This is surely too large an item list for users to select from. Instead, one option would be to create multiple parameters that would simulate a "drill down" experience.

To start with, first create a dataset called "dsCountry" and use the following MDX statement to populate it.

MDX Statement for Country List

with member [Measures].[CountryUniqueName] as '[Customers].currentmember.UniqueName' 
member [Measures].[CountryDisplayName] as '[Customers].currentmember.Name'

SELECT {[Measures].[CountryUniqueName],[Measures].[CountryDisplayName]} on Columns,
{[Customers].[Country].members } on rows
from 
Sales

Notice here again the use of calculated members to store the unique member name and the display name. Create a parameter called "pCountry", and set the options on the parameter so that the values for the parameter are sourced from the Unique Name field and the label values come from the Display Name field.

Once the user selects the country, a list of states that make up that country should be made available to the user for selection. In order to accomplish this, create a new dataset "dsState" that will be used to store the potential state values. To develop the query string for this dataset, we'll need to incorporate the value of the Country parameter. This will require the query string to be determined at run-time.

Reporting Services will treat a query string that begins with "=" as an expression, and we'll rely on that functionality in this example. However, with a query string that is defined as an expression, the fields are not available to the query designer. The workaround for this is to first build your MDX statement without the use of parameters and then add the parameter to the query after the fields have been identified. To populate the list of possible states use the following MDX query.

MDX Statement to Determine State Field Names

with member [Measures].[StateUniqueName] as '[Customers].currentmember.UniqueName' 
member [Measures].[StateDisplayName] as '[Customers].currentmember.Name'

SELECT {[Measures].[StateUniqueName],[Measures].[StateDisplayName]} on Columns,
{[Customers].[State Province].members } on rows
from 
Sales

Once the field names are identified, the next step is to add the "pCountry" parameter to the query. To do so, change the query string to the following.

MDX Statement to Determine State Field Names

= "with member [Measures].[StateUniqueName] as '[Customers].currentmember.uniqueName' 
member [Measures].[StateDisplayName] as '[Customers].currentmember.name' 
SELECT {[Measures].[StateUniqueName], [Measures].[StateDisplayName]} on Columns, 
{DESCENDANTS({" & Parameters!pCountry.Value & " }, 
[Customers].[State Province])} on rows from sales"

There are a few important characteristics of this string. The first is that the equal sign (=) is used in conjunction with double quotes to indicate that this string is an expression that Reporting Services will evaluate during the report's execution phase. The second important characteristic is that no carriage returns are introduced into the string in the query designer. While it might make the query somewhat difficult to read, in order to enter an expression into the query designer, you cannot use carriage returns in the query string. Finally, notice that the "pCountry" parameter value is referred to in the string.

With this dataset defined, create a parameter called "pState" and source the parameter from the "dsState" dataset. Make sure that the "pState" parameter is the second parameter listed in the parameter collection. This is how you specify a cascading parameter and that the user must select a value for the country parameter before the cities will be listed.

To finish out this example, create two additional datasets (one for cities and one for customers) and their corresponding parameters. Remember to first build the MDX queries without the parameters included so that the field names can be identified. Once you've previewed the query to get the field names, change the query string to an expression and add the parameter value to the string. The final version of this report is included in the MDXCascadeParams.rdl file.

Add Search Capability to Dimensions

Another option you may consider for working with large dimensions is to add search capability via a parameter. To illustrate this solution, we'll use another example using the Foodmart 2000 Sales cube and the Customer dimension. In this example, the user will be required to enter a portion of the customer's name as one parameter, and the list of available customers that match those criteria will be listed as possible values for a second parameter.

To implement this solution, first create a parameter called "pCustomerSearch" and indicate that the source for the parameter is not from a query and is of string value type. Once this parameter is created, create a dataset and name it "dsCustomerSearch". Enter the following MDX query as the query string:

MDX Statement to Determine Customer Field Names

with Member [Measures].UniqueCustomerName as 'Customers.currentmember.uniqueName' 
member measures.[DisplayName] as 'Customers.currentmember.Name' 
SELECT {[Measures].[UniqueCustomerName], [Measures].[DisplayName]} on Columns, 
{Filter([Customers].[Name].members,
INSTR(Customers.currentmember.name, "Sally" > 0)} on rows FROM SALES 

This query relies on a few tips that we have seen previously and introduces an additional function from the VBA library. As in the previous examples, if we are going to be working with data from Analysis Services cubes, it's a good practice to return both the unique member name and the standard member name as calculated members along the measures dimension. In addition, this example relies on the INSTR() VBA function that returns a value greater than zero for cases where the supplied string exists within the string in question (in this case that would be the customer's name). At this point, we're also relying on a specific MDX query in order to populate the field collection.

To finish the definition of the query string for this dataset, add a reference to the "pCustomerSearch" parameter and make the string an expression by adding the equals sign and enclosing the expression in double quotes. When finished the string should look like the following (Note: Remove the carriage returns in the expression or the designer will return an error.)

MDX Statement to Search Customers

="with Member [Measures].UniqueCustomerName as 'Customers.currentmember.uniqueName' 
member measures.[DisplayName] as 'Customers.currentmember.Name' SELECT 
{[Measures].[UniqueCustomerName], [Measures].[DisplayName]} on Columns, 
{Filter([Customers].[Name].members,INSTR(Customers.currentmember.name, 
""" & parameters!pCustomerSearch.Value & """) > 0)} on rows FROM SALES "
Tip   To add search capability to a large dimension use the INSTR() VBA function in one parameter to narrow the list of potential values for a second parameter.

Adding Additional Interactivity to Reports

Adding parameters to reports is one way to add interactivity to reports. The next section of this whitepaper will address additional ways in which reports can be made more interactive.

Building "Drill-Down" Reports against OLAP Data

Up until this point, all of the data contained within the example reports has belonged to only one level from each dimension. For example, if you ask the question, "What are unit sales by promotion media type?" even though the user might be given the option to select any available time period, the resulting dataset will only contain data from a single level of each dimension. The included level of the time dimension might change each time the report is executed, but for each report execution only one level of the time dimension will be referenced. What happens when you need to add interactivity to the report by allowing the users to "drill-down" on members in the table?

Adding "drill-down" capability to a report can be problematic. By definition, adding this capability means that some of the data in the report exists at one level of aggregation for a dimension, but that additional data can be contained in the report that exists at other levels of aggregation for the same dimension. For example, the following MDX query returns data from every level of the store dimension.

MDX Statement to Search Customers

SELECT non empty {ADDCALCULATEDMEMBERS(measures.members)} on Columns,
NON EMPTY [Store].members on Rows 
FROM [Sales]

If you preview this query in the designer, one of the things that you'll notice is that for each level of the store dimension there is a field that is returned in the dataset. You can determine what level each record belongs to based on the values of those fields. For example, the data that represents all of the United States has a value for the Store Country field, but doesn't have values for the State Province, City or Store levels. Grouping on this data results in a challenge because Reporting Services doesn't know that the data in the report consists of multiple levels. If you attempt to group this data at the store level, there would be a series of records that would aggregate to a NULL member since all of the state and country level data have a NULL value for those fields. There are a couple of ways that you can address this.

Return Data from the Lowest Possible Level

One option for developing "drill-down" reports against data stored in Analysis Services is to ensure that that the MDX you specify only for members of a single level for each dimension that participates in the query. You can then rely on Reporting Services grouping capabilities to aggregate the data at higher levels as long as the data can be aggregated along one of the aggregate functions that Reporting Services supports. The MDXDrillDownSingleLevel.rdl file that demonstrates how to enable "drill-down" accompanies this whitepaper. To build a drill-down report, simply create a table and insert multiple groups. For each group, specify the appropriate expression to group on. In the example report, three groups are created (Country, State, and City). For the State group the grouping properties are set as follows.

Aa902647.olapasandrs_03(en-us,SQL.80).gif

The visibility options for the group are shown below. Setting these options as shown in the figure is what enables the "drill-down" capability of the report. The lowest level of data in the report is the individual store level and that makes up the detail section of the table.

Aa902647.olapasandrs_04(en-us,SQL.80).gif

Notice also in the body of the table that the value for Unit Sales is specified using the SUM() function.

Field Definition for Unit Sales

=SUM(Fields!Measures_Unit_Sales.Value)

This approach will work for cases in which the data can be aggregated using the available aggregate functions of Reporting Services. However, one of the benefits of using Analysis Services cubes as part of a business intelligence solution is that complex calculated members can be defined using MDX. If you've gone through all the effort to build those calculations into your Analysis Services cubes, you'll probably want to expose those calculations in your reports, without having to redefine them. Fortunately, it is possible to do so.

Using Grouping with Analysis Services Aggregates and Calculated Members

If you've made a significant investment in learning MDX, you'll find that MDX provides very intuitive syntax for what could otherwise be difficult SQL statements. A couple of quick examples will help to illustrate this point. A common query might ask, "What's the difference between sales this period versus sales last period?" In MDX, this can be accomplished using the following fragment.

MDX Fragment for Defining Sales This Period Versus Sales Last Period

(Time.Currentmember, [Measures].[Unit Sales]) – (Time.Prevmember, 
[Measures].[Unit Sales])

Another query might need to compare current period sales with sales from the same period a year ago. The MDX fragment for this calculation would be as follows.

MDX Fragment for Comparing Sales in the Current Period to Sales in the Same Period in the Previous Year

(Time.Currentmember, [Measures].[Unit Sales]) – 
((ParallelPeriod([Time].[Year],1,[Time].Currentmember), 
[Measures].[Unit Sales]))

In short, MDX is a great language for constructing queries in a manner that is consistent with the way that most people approach analytical questions, which is from a dimensional perspective. MDX is designed specifically for those questions, whereas SQL is not. Most cubes contain at least a few calculated members like this, and now you can use these calculations in your reports.

Expanding on the previous drill-down example, suppose in our cube there was a calculated member defined to compare the sales of the current period with sales from the previous period (as the first MDX fragment illustrates). If we followed the previous approach of just returning the data from the lowest level possible in our drill-down, we wouldn't be able to return correct results in our report since there would be no way to aggregate the data within the report. We would have the values that allow us to compare individual store sales for the current period against store sales in the same period in the previous year, but we wouldn't be able to aggregate the data at the city, state or country level because none of the aggregate functions in the report designer would suite our needs.

Instead of returning the data from just a single level for each dimension, in this example we are going to return the data from every level that is capable of being displayed as part of the drill-down and then filter out the records for each group.

Below is the MDX used for the dataset. Note again that it's an expression since it is making use of a parameter.

MDX Statement

="With member [Measures].[SalesVersusLastPeriod] as 
'(Time.currentmember, [Measures].[Unit Sales]) - (Time.prevmember,[Measures].[Unit Sales])' 
SELECT {AddCalculatedmembers(Measures.members)} on columns, 
NON EMPTY [Store].members on rows from sales WHERE (" & Parameters!pTime.value & ")"

This query returns data from multiple levels along the store dimension and accepts a parameter for the time dimension. If you examine the dataset returned by the query, you'll see that the dataset contains records at multiple aggregate levels, and that each level is represented as a field in the dataset. Since each record in the dataset consists of each field, if the field doesn't apply to that record, the field will contain a NULL value. For example, you can identify country level data by noticing that the state, city, and store fields are all left blank. This characteristic of the dataset is what you can use in the definition of the groups to filter out the appropriate rows for each group.

The next step uses the grouping capability of Reporting Services' tables, and filters the rows for each drill-down so that the Report Server only ends up grouping a single record. In order to enable the drill-down capability, add a table to the report and insert three groups in the table. These three groups correspond to the Country, State and City levels. The details section of the report is devoted to individual store records.

Once the groups are created, set the appropriate grouping expression based on the level of the data. The grouping expression for the country data would look like the following.

Aa902647.olapasandrs_05(en-us,SQL.80).gif

Next, use the filtering capabilities to limit the records included in the group. For example, on the country level group, the filtering options are set as follows.

Aa902647.olapasandrs_06(en-us,SQL.80).gif

By setting the filtering options in this manner, the only records left to be included in the group are the records in the dataset that correspond to the country level data, and there is only a single record for each country included in the dataset. Applying the LEN() VBA function to each field and using the AND condition for applying the filters allows you to filter out all of the records that don't belong to the particular level that the group represents. All of the other records in the dataset, the ones that correspond to city, state, and store level information, are filtered out. The net effect of grouping on the country level and filtering out all of the non-country level data is that the country level records in the dataset are returned in the report. To see this report in its entirety, see the MDXDrillDownManyLevels.rdl report included in the download.

The primary benefit of using this approach instead of returning data from the lowest possible level shown in the report is that for the higher levels in the report, you can return the MDX calculations and aggregates that are defined in the cube, rather than redefining the calculations in the report. For example, if you look at the "Measures Unit Sales" field in the layout pane of the report, the report that relies on a dataset consisting of just the lowest level data relies on the Reporting Services SUM() function to aggregate the data at the country, state, and city levels. In the latter example in which a dataset is returned that consists of multiple levels of data, the "Measures Unit Sales" column in the report refers to the field value returned in the dataset. No additional aggregation is required at the higher levels since Analysis Services has returned the data. The benefit of this last approach is that if you've invested a considerable amount of time in building calculations in MDX in your cubes, you can display those calculations without having to redefine them within the Reporting Services design environment.

Tip   To build "drill-down" reports against Analysis Services cubes, create a group for each level of the dimension included in the dataset and then filter the data in each group by using the LEN() function on each field to identify which records should be included in that level of the report. This method will reduce each group to just the records for that level.

Adding "Drill-through" Capability to a Report

The concept of "drill-through" can mean different things depending on the technology being used. For those familiar with Analysis Services, drill-through represents the ability to return the detail records that contribute to the value of a cell. For Reporting Services, "drill-through" is the capability to jump from one report to another report when the user selects an action-enabled object on the report. It's quite possible to develop a report that uses the drill-through action type for Reporting Services to issue an Analysis Services DRILLTHROUGH MDX query, and it's also possible to use the drill-through action type of Reporting Services to return a report that returns data from Analysis Services without using the DRILLTHROUGH MDX statement. Sound confusing? Hopefully a couple of examples can help us understand the difference between the two, and how the two concepts can also be used together.

Using the Reporting Services "Drill-through" Action

In the first example, we'll create a report action that is triggered when a user selects a bar on a chart. The report for this example is included in the file FoodmartDrillThrough.rdl. If you preview this report, you'll find that the report contains a chart that displays the sales for the selected product and time period for each city. If you select any of the bars that represent the sales of a city, a report action of type "drillthrough" is executed. This process opens the FoodmartDrilledTo report that displays how each promotion contributed to the overall sales of the selected product. The report parameters from the initial report are passed to the second report and are displayed at the top of the report for reference.

To see how this capability was added to the report, select the chart for editing and right-click on the [Measures].[Unit Sales] data field. Select the "Action" tab. This should indicate that the "Jump to Report" property is set to the FoodmartDrilledTo report, and by viewing the parameters option, you should notice that the "pTime" and "pProduct" parameters of the second report are set to the value of the parameters in the initial report. Finally, the last parameter listed, "pCity", is set to the current value of the City field in the dataset.

This example serves to illustrate the concept of a "drill-through" report for Reporting Services, but does not make use of the MDX DRILLTHROUGH statement in Analysis Services. An MDX query is still used; it's just not a DRILLTHROUGH query. It's also possible that the drill-through action on the report could have triggered a web page displaying weather forecasts for the selected city. The point is that Reporting Services has drill-through capabilities that are independent of the drill-through capabilities in Analysis Services.

Using MDX DRILLTHROUGH with the Reporting Services Drill-Through Action Type

For our final example, we'll build a report that uses the drill-through capabilities of both Analysis Services and Reporting Services.

The FoodmartMDXDrillthrough.rdl accepts a time parameter and then displays a chart representing store sales in each city for the selected time period. As in the previous example, the chart contains an action based on the [Measures].[Unit Sales] data field which causes a second report, FoodmartMDXDrilledTo.rdl, to be rendered. This second report uses the MDX DRILLTHROUGH statement for the source of the dataset. The MDX statement is provided below.

MDX Drillthrough Example

="DRILLTHROUGH SELECT {[Measures].[Unit Sales]} on Columns, 
{[" & Parameters!pCity.value & "]} on ROWS FROM SALES WHERE 
(" & Parameters!pTime.Value & ")"

The report then uses a table to present the individual records that contribute to the sales figure on the previous report.

Note   Drillthrough is a concept used by both Analysis Services and Reporting Services. The Drillthrough features in both products are independent of one another.

Rendering Reports Using Analysis Services

Thus far, this paper has been about how to build reports in Reporting Services with data from an Analysis Services cube included, focusing on MDX and how to effectively author MDX statements for use in a Reporting Services report.

In addition to being able to author reports in Reporting Services using Analysis Services data, it is also possible to render reports from an Analysis Services client application. This capability of Analysis Services, which will be discussed in more detail below, will help developers integrate Analysis Services and Reporting Services, providing end user's with seamless navigation between the two. Users will not have to open another application as they work with Analysis Services or Reporting Services. Instead, the user will have one experience with the data, using the right technology for each scenario.

Imagine a scenario where an analyst at headquarters logs into a business intelligence portal that utilizes one of the many cube browsers already available today. The analyst logs into the portal, and begins to navigate metrics on suppliers based on an initial view of the cube. After slicing on a few additional dimensions, the analyst decides to get more information about a specific supplier that is having difficulty in delivering needed parts in a timely fashion. The detailed information on this supplier, however, is not stored in Analysis Services. Instead, it's stored in en entirely separate database and maintained by a completely separate application. However, several profiles of the supplier have been built using reports in Reporting Services. By taking advantage of a feature in Analysis Services, you'll be able to seamlessly "link" to those reports.

Analysis Services Actions

Actions are the feature of Analysis Services that allows you to link OLAP cubes to other applications. Actions consist of a server-side "container" that stores the definition of the action. In this way, they are no different than cubes, dimensions, levels, and the other objects that can be defined on the server. In addition, just as cubes, dimensions, and levels are exposed via a client API for applications to take advantage of, actions are also exposed via the client APIs as well.

Actions are defined by specifying an Action Target, Action Type, and the syntax for the action. One of the action types that Analysis Services supports is 'URL', and with this action type, the return value is a URL that should be launched using an Internet Browser. Since one of the methods for rendering a report is to specify a URL, it should be possible to return a URL that represents a call to render a specific report.

Defining an Analysis Services Action

Identify the Report URL

Before you attempt to build an action in Reporting Services, you will want to know the exact URL call needed to render a specific report. At this point, don't worry about how you'll pass the parameters from Analysis Services to Reporting Services. That will explain in detail, but the first thing you will want to do is to capture the URL that is used to render a report.

There are a couple of options for determining the correct URL syntax. Reporting Services Books Online provides some information on the required URL syntax for rendering a report. Another option for determining the required URL for a report is simply to turn on IIS Logging and view the report. If you specify that all properties should be logged, you will be able to see the actual URL that was used to render the report. Turning on IIS Logging will also provide you with any character translations that you may need to specify for a given report. For example, if you want to use a parameter value of "San Antonio" for a city, you will need to pass the value of "San+Antonio" (the space is converted to a "+"). There are a couple of other character translation cases you will need to be aware of particularly when you are working with Analysis Services. Again, logging the report calls will identify those.

Define the Action

Once the Report URL is known, you can then define the action. This is done by selecting a cube in Analysis Manager and selecting "edit". For our example, we used the Foodmart 2000 Sales cube and we created a new action called "CustomerProfile". As we worked through the wizard we used the following values.

Aa902647.olapasandrs_07(en-us,SQL.80).gif

Aa902647.olapasandrs_08(en-us,SQL.80).gif

Aa902647.olapasandrs_09(en-us,SQL.80).gif

The expression in the MDX Builder is provided below.

Action MDX

="http://localhost/ReportServer?%2fFoodmart%2fMDXActionReport&pAccountNum=
39860458970&rs%3aClearSession=true&rs%3aFormat=HTML4.0
&rs%3aCommand=Render&rc%3aLinkTarget=
_top&rc%3aJavaScript=True&rc%3aToolbar=
True&rc%3aReplacementRoot=http%3a%2f%2flocalhost%2fReports%2fPages
%2fReport.aspx%3fServerUrl%3d3aLinkTarget%3d_top&rc%3aArea=Toolbar"

In this example, there is a single parameter for the report that is the customer's account number. This parameter is represented by the string "pAccountNum=

39860458970". You should be able to save the action, and browse the cube to any customer, right-click on the customer and select "Customer Profile" to invoke the action. This should render the report for the customer with the account number specified.

Parameterize the Action

In our example thus far, we just copied and pasted a specific account number from a test run of the report to ensure that the action actually renders the report. More than likely, you will want to call a report that contains parameters. In our example, we have one parameter for the report (pAccountNum). The next task is to make the Analysis Services action work with each possible customer.

To do this, we simply need to modify the action's MDX statement to make it dynamic. In this case, we have added a member property to the customer level of the Foodmart 2000 cube that contains the customer's account number. Once the member property is created, the MDX for the action is modified to the following.

Action MDX

="http://localhost/ReportServer?%2fFoodmart%2fMDXActionReport&pAccountNum="
+ [Customers].currentmember.properties("AccountNum") + &rs%3aClearSession=true&rs%3aFormat=HTML4.0
&rs%3aCommand=Render&rc%3aLinkTarget=
_top&rc%3aJavaScript=True&rc%3aToolbar=
True&rc%3aReplacementRoot=http%3a%2f%2flocalhost%2fReports%2fPages
%2fReport.aspx%3fServerUrl%3d3aLinkTarget%3d_top&rc%3aArea=Toolbar"

Again, save the action and the cube and now you should be able to browse to any customer in the customer dimension and view the customer's profile. This will render a report that includes information from the Foodmart 2000 Access database. In this case, we have gone from data stored in an Analysis Services cube to data stored in Access 2000 database. This could have just as easily been a SQL Server database, or any other RDBMS supported by Reporting Services. The user, however, didn't need to launch another application to move from one view to the next, the features in Analysis Services and Reporting Services allowed this experience to be transparent. The final customer profile report used in this example is included in the accompanying files and is named MDXActionReport.rdl.

There are many possible scenarios for which developing an action in Analysis Services to display a report could be useful. Some common scenarios might involve presenting a customer profile, or recent billable activity.

Action Tips

The example action that is provided in this scenario is arguably fairly simplistic. The goal of this example was to illustrate the mechanics involved in rendering a report from Analysis Services. In our example, we created a member property to store the customers' account number, which is what the report expected for a parameter. Then, the action syntax was modified to pass the current customer's account number to the report. Below are some tips to consider when building actions for use with Reporting Services.

  • Turn on IIS Logging with extended properties. Turning on IIS Logging with extended properties will allow you to see the URL call for the report.
  • Use Member Properties to store URL fragments. In our example we used a member property to store the account number of the customer, which was used as a parameter for the report. This account number provided the link between the cube and the relational system.
  • Using member properties to store URL fragments can be especially useful when you need to do string manipulation in order to make the URL conform to the URL that Reporting Services expects. For example, if you were to use the MDX fragment [Customer].currentmember.name, it's likely that if the customer name consisted of a first and last name, there would be a space produced between the first and last names. Since URLs to reports can't have spaces in them (they are converted to "+" instead), one approach would be to store a URL fragment in a database so that the manipulation does not need to be done in MDX. For example, you might have a field in the database with a value of "Jane+Doe" to represent Jane Doe. This field would then be designated as a member property.
  • Use an Analysis Services UDF to create URL fragments. If it were not possible or desirable to store a URL fragment in a database, another option would be to create a UDF and register it within Analysis Services. By creating a UDF using a programming language, you can create sophisticated functions to perform string manipulations that can then be referenced in the action syntax.
  • For simple replace operations use the Excel Substitute() function. For cases, where only string replacement needs to be performed, one option would be to use Excel's Substitute() function. This function could be used to replace familiar characters in Analysis Services such as "[" and "{" and replace them with their Reporting Services URL equivalents.

Moving Towards SQL Server 2005

The release of SQL Server 2005 will see significant feature upgrades to Reporting Services and Analysis Services. Let's take a brief tour of some of the new SQL Server 2005 features and how they relate to our discussion on integrating Analysis Services and Reporting Services.

The most notable addition in SQL Server 2005 Reporting Services as it relates to Analysis Services will be a graphical MDX Query Designer that will enable report developers to author MDX for their reports. Users will be able to drag levels, measures, KPIs (Key Performance Indicators) into the query design pane and produce MDX queries. There will also be

In addition to the inclusion of the MDX Query Designer, Analysis Services will support parameterized queries in SQL Server 2005 and Reporting Services will add support for this feature as well. If you remember back to the previous discussion in this whitepaper on how to add parameter support to reports based on Analysis Services, this technique will no longer be required. However, any reports that you authored using the advice contained in this whitepaper will still execute correctly in SQL Server 2005. This will allow you to leave your existing reports "as is", or to update them.

Reporting Services 2005 will also add support for server-side aggregates. This feature will enable report authors to take advantage of aggregations as they are defined in Analysis Services, in addition to using the aggregate functions available in Reporting Services. Reporting Services will also expose server-side formatting features such as the cell background property.

Finally, a large portion of this whitepaper was devoted to the characteristics of flattened MDX recordsets. In SQL Server 2005, Reporting Services will continue to use this algorithm for consuming data from Analysis Services. Report authors should be able to rely on the information presented on this topic as it pertains to this subject.

Conclusion

This whitepaper has discussed how both Analysis Services and Reporting Services for SQL Server 2000 can be used together as part of an overall business intelligence solution. The ability to define reports, manage reports, and convert them to various formats can enhance many Analysis Services implementations. In addition, the Analysis Services capability to answer analytical questions very quickly with a rich multi-dimensional query language can be a benefit to many traditional reporting scenarios. This paper has discussed many ways in which the two can be integrated to provide better solutions for customers.

For more information

Microsoft SQL Server home page

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.