dcsimg

MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 10

July 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

Illustration 45: Selecting the Expense Account as the Column ...

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.


Illustration 46: Our Newly Added Groups...

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.


Illustration 47: Select Properties from the Context Menu...

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.


Illustration 48: Selecting Row Textbox Details...

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.


Illustration 49: Selecting Column Textbox Details

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.


Illustration 50: Centering Label Contents ...

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.


Illustration 51: Our Completed Properties Page

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.


Illustration 52: Matrix with Textbox Values

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

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers