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