MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 10July 26, 2004 12. Click OK to save changes and return to the Matrix Properties dialog, Groups tab. 13. Click the single item in the Columns box, to select it. 14. Click Edit to open the Grouping and Sorting Properties dialog for the column group, just as when we worked with the row group. 15. Change the column group title in the Name box to the following: matrix1_ColumnGroup_Acct 16. In the Group On: section, in the top Expression field, select =Fields!Account_Level_03.Value to populate the field, as depicted in Illustration 45.
Click for larger image 17. Click OK. We return to Matrix Properties - Groups tab, where we see the newly added column group for Account, just below the row group we added earlier for Location, as depicted in Illustration 46.
18. Click OK to close the Matrix Properties dialog, and return to the Design Surface. We need to add row, column, and data textbox details next. 19. Right-click the bottom left textbox in our matrix (the textbox to the left of the box marked Data). 20. Select Properties on the context menu, shown circled in Illustration 47.
The Textbox Properties page appears. 21. Change the textbox title in the Name box to the following: textbox_Location 22. Type (versus select) the following: =Parameters!px_LocationHier.Label into the Value selector, as depicted in Illustration 48.
23. Leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page. 24. Right-click the top right textbox in the matrix (the textbox immediately above the box marked Data). 25. Select Properties on the context menu, once again. The Textbox Properties page appears. 26. Change the textbox title in the Name box to the following: textbox_Account 27. Select =Fields!Account_Level_03.Value in the Value selector, as depicted in Illustration 49.
28. Again leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page. 29. Click the textbox we have just named once to select it. 30. Click the "Center" icon in the format group on the design environment toolbar, as shown in Illustration 50.
Now let's perform the same binding exercise for the Data box. 31. Right-click the box marked Data in the matrix (bottom right corner of the matrix). 32. Select Properties on the context menu, once again. The Textbox Properties page appears. 33. Change the textbox title in the Name box to the following: textbox_Measures_Amount 34. Select =Fields!Measures_Amount.Value in the Value selector. 35. Enclose the selected value with the SUM() function, as follows: =SUM(Fields!Measures_Amount.Value) NOTE: The "=" sign must precede all characters assigned to the textboxes. 36. Under Format, in the right half of the Properties page, ensure that the radio button to the immediate left of the Standard label is selected.
37. Select Currency in the Value selector. The Properties page appears as shown in Illustration 51.
38. Again leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page. 39. Click the textbox we have just named once, to select it. 40. Click the "Center" icon in the format group on the design environment toolbar, as we did with the last textbox. The matrix, with new textbox values partially shown, appears similar to that depicted in Illustration 52.
There is far more involved than what we have accomplished, obviously, in writing a robust and useful OLAP report in any reporting package. Many more considerations, including a host of formatting nuances, more involved data selection and grouping, and almost certainly more restrictive filtering (such as by time, etc.), as well as others, can come into play. Our intent here is merely to see our MSAS-based parameter support in operation, so we have accomplished enough to conclude with executing and reviewing the report. If Reporting Services, the new paradigm in enterprise reporting, is of interest to you, see my series devoted specifically to the application at Database Journal. The same concepts will apply, as we have noted, with any enterprise-level reporting package that can access MSAS cubes and calculated members / named sets. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
