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.
Prev. page
1
[2]
3
next page 
