Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension - Page 8April 11, 2005 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 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:
IIF(IsEmpty(([Measures].CurrentMember,
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.
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.
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.
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.
12. Click Yes when prompted to save the cube, as depicted in Illustration 29.
13. Click No when asked if you wish to design aggregations, in the next Analysis Manager dialog, shown in Illustration 30.
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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
