Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I - Page 3
November 20, 2006
Preparation: Modify the OLAP Report for Use within Our Practice Session
We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters, and which is fitted with a simple filter. Lets open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.
1. Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.
2. Select Open from the context menu that appears, as shown in Illustration 1, as necessary.
DBJ_OLAP_Report.rdl opens in Layout view.
We will start with the Data tab.
3. Click the Data tab.
We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.
4. With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.
5. Once the Dataset loads, click the Delete button, as shown in Illustration 4.
6. Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.
The primary (and sole remaining) dataset, ProductData opens.
7. Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.
8. Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.
9. Within the Calculated Members pane, right-click the sole Calculated Member, Profit.
10. Click Delete on the context menu that appears, as depicted in Illustration 7.
11. Right-click the single entry in the Filter pane (directly atop the Query pane).
12. Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.
We will make an addition to the Dataset next.
13. Within the Metadata pane, expand the Date dimension, by clicking the "+" sign to its immediate left.
14. Expand the Fiscal folder that appears within the Date dimension.
15. Expand the Fiscal hierarchy within the Fiscal folder.
16. Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.
The new Month column appears, as desired.
We will now add in a simple filter, to make our report a bit more manageable for the practice session.
17. Click and drag the Fiscal hierarchy into the leftmost box (currently containing the phrase "<Select dimension>"), of the bottom row of the Filter pane, as shown in Illustration 10.
18. Click the Filter Expression box within the same row of the Filter pane to enable the selector.
19. Click the downward arrow of the Filter Expression selector to expose the selection tree.
20. Expand All Periods by clicking the "+" sign to its immediate left.
21. Select FY 2004 and FY 2005 by checking the boxes to the left of each, as depicted in Illustration 11.
22. Click OK to accept our settings, and to close the selector.
The newly added row in the Filter pane appears, as shown in Illustration 12.
Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.
23. Click the Layout tab, as depicted in Illustration 13.
24. On the Layout tab, within the Matrix Data Region, select the value appearing underneath the Internet Total column heading.
25. Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).
26. Select Delete from the context menu that appears, as shown in Illustration 14.
27. Right-click the upper left-hand corner of the Matrix Data Region (the gray square).
The gray column and row bars disappear, as a light, patterned outline forms around the Matrix Data Region, and the context menu appears.