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.
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.
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.
» 
Comment and Contribute