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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 11, 2005

Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension - Page 8

By William Pearson

Create the % Profit Contribution Calculated Member

Now all that remains structurally is to add the % Profit Contribution to have all the components in place to meet the information consumers' stated business requirements.

1.  Right-click the Calculated Members folder.

2.  Select New Calculated Member from the context menu that appears, as shown in Illustration 24.

Click for larger image

Illustration 24: Select New Calculated Member ...

The Calculated Member Builder opens.

3.  Ensure that Measures is selected in the Parent Dimension selector atop the Builder.

4.  Type the following into the Member Name box within the Calculated Member Builder:

% Profit Contribution

5.  Type the following into the Value Expression box within the Calculated Member Builder:

   Ancestor([Store Location].CurrentMember,1))), 1,
      [Measures].[Warehouse Profit]/([Measures].[Warehouse Profit],      
          Ancestor([Store Location].CurrentMember,1)))

The above MDX provides for a Percentage Profit Contribution calculation, while also making the result a "1" (or "100 percent") when the CurrentMember of the Store dimension has no parent.

NOTE: For more information regarding the CurrentMember function, see my Database Journal article MDX Essentials: MDX Member Functions: "Relative" Member Functions.

The Calculated Member Builder appears as depicted in Illustration 25.

Illustration 25: The Calculated Member Builder, with Our Input

6.  Click OK to close the Calculated Member Builder, and to accept our input.

We see the new Calculated Member appear in the tree pane, within the Calculated Member folder, as shown in Illustration 26.

Illustration 26: The % Profit Margin Calculated Member Appears

7.  Click the % Profit Contribution calculated member in the tree, to select it, if necessary.

8.  Click the Properties bar at the bottom of the pane, to open the Properties pane, if necessary. (If "Properties" appears on the bar with an upward-pointing arrow to its right, clicking will open the pane / point the arrow downward).

9.  Click the Advanced tab in the Properties pane.

10.  In the selector for the Format String property box, select Percent, as depicted in Illustration 27.

Illustration 27: Setting Percent Format for the % Profit Contribution Calculated Member

Let's process the cube again, to update it for all our work.

11.  Select Tools -> Process Cube from the Cube Editor main menu, as shown in Illustration 28.

Illustration 28: Reprocess the Cube ...

12.  Click Yes when prompted to save the cube, as depicted in Illustration 29.

Illustration 29: Save the Cube before Processing

13.  Click No when asked if you wish to design aggregations, in the next Analysis Manager dialog, shown in Illustration 30.

Illustration 30: Decline Aggregation Design ...

The Process a Cube dialog appears, as before.

14.  Click the radio button to the left of Full Process, to select full processing.

15.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as we saw earlier.

16.  Click Close to dismiss the viewer.

At this stage, all components are in place to deliver the data presentation requested by the information consumers during the business requirements gathering phase.

MS SQL Archives

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