Delivering Analysis Services Data with Reporting Services

Analysis Services has given many organizations the ability to produce powerful data marts and data warehouses, but delivering the data to the organization is sometimes problematic. SQL Server 2000 Reporting Services gives businesses a free, powerful tool for delivering warehouse data to users. Although Reporting Services won't replace full-blown analytics applications, it provides perhaps the broadest reach for warehouse data in the business intelligence (BI) process. It's useful to learn how to use Reporting Services to access Analysis Services data because it gives users the benefits of data warehousing.

Reporting Services 2005 adds many powerful features for accessing data in Analysis Services 2005 cubes, but many organizations continue to use Analysis Services 2000 and have cubes that will be challenging to upgrade to Analysis Services 2005. In this article, I walk you through an example that shows what Reporting Services can do to deliver data from a warehouse to the whole organization. Reporting Services lets you provide data to business users in static reports or reports that use parameters and have limited drilldown capabilities. In an upcoming article, I'll show you how to modify the report to display measures and dimensions differently, and walk you through the steps required to properly format such a report.

To create a report that pulls data from Analysis Services, the report creator must use MDX. MDX is a query language for cubes, just as SQL is the query language for relational databases. Cubes are multidimensional by nature, so the query language must understand multiple dimensions and have the ability to have more than one measure at the intersection of dimensions (basically by treating measures as another dimension). Reporting Services 2000 doesn't provide a graphical builder for MDX statements, which requires the report creator to code the query by hand. Even if your organization uses Reporting Services 2005, it's important to understand the MDX queries created by the graphical designer so that you can restructure them for added flexibility. If you don't have experience with MDX, you'll be glad to learn about the MDX generator in the Web sidebar "MDX for the MDX Phobic"(http://www.sqlmag.com, InstantDoc ID 50233).)

Building a Basic BI Report

As with any Reporting Services 2000 report, our example report usesVisual Studio's IDE. Start by creating a new project that will consume data from Analysis Services. In Visual Studio, choose Business Intelligence Templates as the project type and use Report Project as the template; don't use the Report Project Wizard.

Next, you need to create a data source. A shared data source usually works well, so right-click the Shared Data Sources folder in the Solution Explorer and choose Add New Data Source to bring up the Data Link Properties dialog box. On the Provider tab, select the Microsoft OLE DB Provider for OLAP Services 9.0, 8.0, and 7.0. Once you've filled in the name of the server and the cube that you want to use, you can create reports. In this article, I use the Foodmart 2000 sample database that comes with Analysis Services 2000 for all examples.

To build a basic report, right-click the Reports folder in the Solution Explorer and choose Add, Add New Item, Report. I don't recommend using The Report Wizard template because it doesn't allow for the full range of options you'll need to design the report. The first report, Basic Report, is in the sample files available for download at http:// www.sqlmag.com, InstantDoc ID 50204.

Once you've created the report, click the Data tab to create a dataset. Give the dataset a name, a data source, a command type of Text, and a query string. Developers who are familiar with MDX find that Reporting Services queries look somewhat odd because they aren't like queries in other tools such as the MDX Sample Application or analytic applications such as ProClarity Analytics. For this simple example, the query is

SELECT { [Measures].[Store Sales] } ON  COLUMNS, { Descendants([Product].[All   Products],    [Product].[Product Name],      LEAVES) }   ON ROWS, NON EMPTY {  [Time].[Month].Members }   ON PAGES FROM [Sales]

The preceding query retrieves the Store Sales measures for all products for each month in the time dimension. FoodMart 2000 contains many individual products, so the query returns several records.

To execute the query, click Run on the toolbar (the button has an exclamation mark as its icon). Figure 1 shows a few of the records the query returns. More information than you might expect appears in the grid because of the way Reporting Services handles MDX. The query asked for the products at the Product Name level, which is the lowest level. However, the grid displays columns for all higher product levels, such as Family, Department, Category, and so forth. The query also asked for all of the months, but the results include columns for the year and quarters. The extra data appears because most client tools understand that the results of an MDX query typically display as a cellset—a multidimensional representation of the query results. Reporting Services doesn't understand cellsets and must flatten them into recordsets, thus showing the entire hierarchy for each value. So for each month, the year and quarter also appear in the grid. The grid doesn't cause problems when you build the report because the report won't include the year and quarter fields if they aren't needed; in fact, this flattening helps you make reports that include expand and collapse capabilities.

Another strange thing you'll see in Figure 1 is the MDX statement, which contains three axes. Typical reports put everything on rows and columns because grids are two dimensional by nature. The MDX query in Figure 1 puts values on the Rows and Columns axes but also has a Pages axis. The general rule of thumb, which I'll break in the second part of this article, is to include the measures on the Columns axis. As it turns out, Columns is the axis that will actually fill the data part of the grid, which I demonstrate in a moment.

Alternatively, you could rewrite Figure 1's query as:

SELECT{ [Measures].[Store Sales] } ON  COLUMNS, { Crossjoin([Time].[Month]     .Members,   Descendants([Product].      [All Products],    [Product].[Product Name],     LEAVES))}    ON ROWS FROM [Sales]

This version of the query uses the Cross-Join function to combine on the Rows axis all dimensions other than measures. Both forms of the query return the same results, but we'll use the first form for this article's examples to help differentiate which axes we're using.

Once you confirm that the query is returning the correct results, you can create the report. On the Layout tab, drag the Matrix control onto the report designer. The matrix deals with multidimensional data better than the table, especially when it comes to expanding and collapsing rows and columns. Although the table supports expanding and collapsing, the matrix handles member data in columns. Next, drag your recordset's fields from the Fields window into the cells of the matrix. If the Fields window isn't visible, open it by choosing Fields from the View menu.

The list of fields shows not only the fields that the MDX query specified (e.g., Product Name, Month) and all the fields at higher levels (e.g., Product Family, Product Department, Year, Quarter). Thus, even a simple query can result in reports that aggregate values at higher levels—a useful capability as long as the higher-level values are sums, not averages or distinct counts.

Create the first report by dragging the Product_Product_Name field into the Rows text box in the matrix.Then, drag the Time_Quarter field into the Columns text box and drag the Measures_Store_Sales field into the Data text box. Note that when you drop the Measures_Store_Sales field into the Data text box, the formula that appears is

=First(Fields!Measures_Store_  Sales.Value)

Although this formula will work for data at the lowest level of detail, it won't work for this example report because we're looking for quarterly values instead of monthly values. Leaving the First function in place will return only the value for the first month of the quarter. Therefore, you have to change the formula, replacing First with Sum.

=Sum(Fields!Measures_Store_Sales   .Value)

Once you've applied a little formatting, such as setting background colors, bolding text, and right-aligning the numbers, the report you see on the Layout tab should appear similar to Figure 2. Note that the data value should have the Sum function around the field name. Click the Preview tab to execute the report and display the data. As Figure 3 shows, the query returns products at the lowest level of detail, resulting in 35 pages of information.

Providing Drilldown Capabilities

When you're building reports that consume Analysis Services data, one of the most common requests you'll get from users is to provide reports that have some limited interactivity. Called expand and collapse or drilldown, interactivity lets users see data at a high level and look at lower levels of detail if they want. For example, the report might start with the total sales for the year and let you drill down to the quarters, then the months. Likewise, a product report might start by showing the category, then the brand, then the individual products. Luckily, when Reporting Services flattens the cellset into a recordset, all the fields are created from the higher levels and displayed in the Fields window. You simply need to add text boxes to hold the fields, then put them in the report.

To add year and month information to the report you just created, click anywhere in the matrix control to display the gray column and row header areas. Right-click the gray column header over the column containing the quarter and the measure to display a pop-up menu, from which you then choose Add Column Group, as Figure 4 shows. This option opens the Grouping and Sorting Properties dialog box. In the Group On area, set the expression to =Fields!Time_Year.Value from the drop-down list box and click OK. Once you've added the year information, repeat the steps to add a column group for months in which the Group On expression is set to =Fields !Time_Month.Value. If the text boxes for the years, quarters, and months aren't in the correct order, you can simply drag them up or down to rearrange them.

You can also open the Grouping and Sorting Propertiesdialog box by right-clicking the gray area next to the row containing the product and choosing Add Row Group. In this example, we'll add two new groups, one for the product category and one for the product brand. To do so, arrange the text boxes in the row so that the first text box is the product category, then the brand, and finally the product name. Figure 5 shows the result with the columns widened to reveal the names of the fields in each text box; we'll narrow these columns before previewing the report.

At this point, you can preview the report and see that it's less than ideal; everything is displayed. The user sees the year, all four quarters, and all twelve months displayed at the start, with no ability to hide the months and quarters to see a yearly total. Similarly, the products show categories, brands, and individual products. The next step is to make the report display just the highest level of detail and let the user expand or collapse the level of detail.

Adding Report Interactivity

When you're including multiple levels of a dimension in a report, it's usually best to hide lower levels of detail and let users drill down by expanding parents, as you can do in Microsoft Excel Pivot Tables. You can add this interactivity by designating that a particular group, such as the product name group, is hidden by default and made visible by a higher level group, such as the product brand.

One catch to creating interactivity is that the names of the text boxes that hold the values of higher levels aren't always meaningful. For example, if you click the product brand name text box, it might reveal the name text box 5. The product name text box might be named Product_Product_Name. Ultimately, the name of the text box is immaterial and you can easily change it to something more meaningful by changing the text box Name property. In this example, I've named the text boxes: Product_Category, Product_Brand, Product_Name,Time_Year, Time_Quarter, and Time_Month.

Right-click the Product_Name text box to open the context menu. Select Edit Group to open the Grouping and Sorting Properties dialog box. Click theVisibility tab to show that the text box, and therefore all product names, are currently visible. To hide the columns containing product names, change the Initial visibility option to Hidden. However, because the goal is to give users the ability to see the product names when needed, you can use the final option on the Visibility tab, Visibility can be toggled by another report item to enable the drop-down list box. The item that will toggle the product name is the item that, for this report, was placed one level higher: the brand. However, the Product_Brand text box might not show up in the Report item drop-down list box. Simply consider this to be a "feature" and realize that you must type in the name of the text box. When you're done, the dialog box should look like the one in Figure 6.

When you're done, you can repeat this process so that users can see the product brand by toggling the product category, see months by toggling quarters, and see quarters by toggling the year. Once the report is finished, it will display a yearly total for each product category. Any category can be expanded to show brands, and the brands can be expanded to show individual products. Likewise, the year can be expanded into quarters and each quarter into months. Figure 7 shows the report with some of this expansion completed.

Dealing with Multiple Measures

So far, the report has had only one measure, Store Sales. You can add other measures such as measures from the cube or calculations in Reporting Services that are based on existing cube measures. To retrieve additional measures, you must modify the query to include the measures you want. For example, the following query is the same as the previous query example except that Store Cost has been added to the query expression on the Columns axis:

SELECT{ [Measures].[Store Sales],   [Measures].[Store Cost] } ON     COLUMNS, { Descendants([Product].[All   Products],    [Product].[Product Name],     LEAVES) }    ON ROWS,  NON EMPTY { [Time].[Month]    .Members } ON PAGES  FROM [Sales]

Once you've changed the query, click the Layout tab to show the new field in the Fields window.You can drag the new field, Measures_Store_Cost, onto the Store Sales measure text box. Dropping the new measure does two things. First, it adds a second text box under the previous column text boxes.

Second, it adds a new row of two text boxes where the measure names are displayed.The formula for the new measure suffers the same problem as when Store Sales was added, so you have to replace the function First with Sum. By removing the word "Measures" from the new measure name text boxes, you get a report that looks like the one Figure 8 shows.

Adding Parameters

The ability to drill up and drill down is convenient in many circumstances and gives users some control over the levels of detail they choose to view within the report. Giving users the ability to select items from a list to filter the data greatly enhances the value of the report as well. You can add parameters to reports in different ways, depending on the data that the query retrieves. Regardless of the method you chose, it's important to understand that Reporting Services 2000 doesn't support parameters in an MDX statement (this feature is available in Analysis Services 2005 and supported in Reporting Services 2005).

One of the challenges in adding parameters is creating the query. You can get the values to fill the query from a relational query of the data in the star schema, or retrieve the values by using MDX to query the Analysis Services data. Unfortunately, the data for a parameter is often a single column, which MDX doesn't handle well. However, if you use a calculated member, the cellset can return a column of Null values, which gets flattened so that Reporting Services can consume it for use with parameters.

You can create a new dataset on the Data tab of the basic report. To return a list of product categories, use the following MDX query:

WITH MEMBER Measures.NullColumn   AS 'Null' SELECT   {Measures.NullColumn} ON       COLUMNS,    { [Product].[Product      Category].Members }   ON ROWS  FROM     [Sales]

Notice that the query creates a calculated measure that simply contains the constant Null. This measure is then placed on the Columns axis, which is normally where the value ends up when the cellset is flattened. The values needed for a parameter list are the product category names, so not having any measures isn't a problem. Once you've created the new dataset, it's time to create a parameter that includes the dataset to display the list of values.

This particular query returns all the product categories and the values from the levels above Product Category. You add the parameter by clicking the Report menu and choosing Report Parameters. In the Report Parameters dialog box, you can add a new parameter that includes the new dataset. Assuming the dataset is named CategoryList, you can set both the value and label fields to Product_ Product_Category, as Figure 9 shows.

Once the parameter is created, you must tie it to the matrix as a filter by selecting the matrix control, right-clicking, and choosing Properties. Note that selecting the matrix control can be a challenge; clicking anywhere in the matrix control selects a text box, not the matrix itself. You have to click a text box, then click the upper left-hand square of the gray border that appears along the top and left-hand sides. Finally, carefully right-click the border that surrounds the entire matrix. Next time, you can avoid this "feeling around" method of selecting the matrix by using the drop-down list at the top of the Properties box. Once the Matrix Properties dialog box is visible, click the Filters tab and set the expression to =Fields!Product_Product_Category.Value, which refers to the field created in the first query. Then, set the Operator to an equals sign (=), and the Value field to the parameter that contains the expression =Parameters!ProductCategory.Value. Once you've completed that step, you can preview the report and chose a category from the parameter list. The report then shows data just for that particular category.

Adding parameters is a great way to provide analytical capabilities to all users in your organization. Users don't have to be analysts or know how the data is stored. Letting users select from parameters makes it easy for them to answer many of their own questions and narrow down the data to only the items relevant to their business duties.

You can use the techniques in this article to begin producing reports that access the data in your data warehouse so that many people in your organization can use it, even if they know nothing about Analysis Services. Although many people think Excel and ProClarity are the tools to use to access data in Analysis Services, Reporting Services gives data access to a much wider audience in a tightly controlled manner.