Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 26, 2004

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date