MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives - Page 4
June 20, 2005
Enhance the Foodmart Percent of Total Cube to Support the Percent of Total Calculation
From the perspective of the Percent of Total cube, in order to present the percent of total information requested by the information consumers, we will need to create a calculated member in Analysis Services.
1. Right-click the new Percent of Total cube.
2. Select Edit ... from the context menu that appears, as shown in Illustration 5.
Click for larger image
The Cube Editor opens.
3. Right-click the Calculated Members folder within Cube Editor.
4. Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.
The Calculated Member Builder opens.
5. Ensure that Measures is selected in the Parent dimension selector atop the Calculated Member Builder.
6. Type the following into the Member name box within the Calculated Member Builder:
Percent Total Sales
7. Type the following into the Value expression box within the Calculated Member Builder:
([Store Sales], [Store].CURRENTMEMBER) / ([Store Sales], [Store].[All Stores])
The above MDX provides for a simple Percent Total Sales calculation. In essence, it consists of Store Sales for the currently selected Store, divided by Store Sales for all Stores. We will assign a "percent" format in the following steps, mostly for purposes of viewing in the cube browser - we will handle formatting for the report at the Reporting Services level in a subsequent section. Keep in mind that the above is a simple approach, and that, in a real world scenario, we would want to build in more sophisticated logic to manage such scenarios as nulls, composition of rollups, and so forth.
NOTE: For more information regarding the CurrentMember function, see my Database Journal article MDX Essentials: MDX Member Functions: "Relative" Member Functions. For information on managing nulls and other considerations surrounding "contribution to totals" scenarios, see both MDX in Analysis Services: Intermediate Concepts - Part 2, and MDX Essentials: Logical Functions: The IsEmpty() Function, also here at Database Journal.
The Calculated Member Builder appears as shown in Illustration 7.
8. 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
9. Click the Percent Total Sales calculated member in the tree, to select it, if necessary.
10. 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).
11. Click the Advanced tab in the Properties pane.
12. In the selector for the Format String property box, select Percent, as depicted in Illustration 8.
Let's process the cube, to update it for the first time since its cloning.
13. Select Tools --> Process Cube from the Cube Editor main menu, as shown in Illustration 9.
14. Click Yes when prompted to save the cube, as depicted in Illustration 10.
15. Click No when asked if you wish to design aggregations, in the next Analysis Manager dialog, shown in Illustration 11.
The Process a Cube dialog appears.
16. Ensure that the radio button to the left of Full Process, as depicted in Illustration 12, is selected (the default, as the cube has not been processed since its creation).
17. Click OK to begin processing.
Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 13.
18. Click Close to dismiss the viewer.
Let's do a quick verification of the operation of the cube's newest calculated member, Percent Total Sales. We can examine the results of our handiwork while here in the Analysis Manager, via the Cube Browser, before revisiting it later in our report. This allows us to ensure effectiveness before we leave the design environment.
19. From our present position within the Cube Editor, click the Data tab to open the Browser and see cube data.
20. Drag the Store dimension down, and drop it atop the existing row ("Y") axis heading, as necessary.
21. Ensure that the Measures dimension appears on the column ("X") axis, dragging it there if necessary.
The Data tab matrix appears as partially depicted in Illustration 14.
22. Double-click the USA Store Country in the row axis to expand it.
23. Expand the Store State level for Washington (WA).
24. Scroll over to the right in the Cube Browser, until our new calculated measure, Percent Total Sales, appears, as shown in Illustration 15.
We can readily see that the simple percent of total calculation is performing as expected. We will now move to Reporting Services, where we will create a similar calculated field, which we will then juxtapose in a report side by side with the Percent Total Sales calculated member in the cube, to compare the two, and discuss considerations surrounding the two methods from the standpoint of an integrated BI solution.
25. Verify the math behind the Percent Total Sales values, if desired, to gain comfort with its correctness.
26. Leave the Cube Editor open in Data view, for comparison against our report calculations later, if desired.