Procedure
Insert
the Calculated Member from the Cube into the Report
Having
prepared the report, we are ready to assemble the data presentation requested
by the information consumers.
We will
take the next step from our current position on the Layout tab, within
the Report Designer. Here we will add the new calculated member, Percent
Total Sales from
the Percent of Total cube.
1.
Click the new
calculated member, named Measures_Percent_Total_Sales in the Report
Designer Field List, to select it.
2.
Drag the Measures_Percent_Total_Sales
onto the Layout tab, dropping it to the right of the data field
underneath the Store Sales column in the matrix (just as we would add
any existing field to a report in Report Designer), as depicted in Illustration
47.
One of
the more obvious advantages in creating a calculated member in the cube,
versus creating an equivalent calculation in the report, is that the
calculation thereby becomes available to us anytime we refer to it in using the
parent cube as a data source. If we create it within the report, we have to
re-create it in subsequent reports. Using cube structures to house calculations
(and, as I show in numerous articles of the series, other report objects)
therefore means centralized creation and maintenance, not to mention
consistency in reporting, as calculations of this sort are more easily
controlled - we don't have three developers, for instance, creating three
separate calculations locally, in three separate reports, each, perhaps, giving
the calculation a different name, or, worse, each arriving at differing results
because of differences in the construction of the calculations themselves.
Performance gains can also be enjoyed by constructing the calculation within
the data source.
The new
measure appears in a new column, automatically labeled Measures Percent
Total Sales, to the right of the Store Sales column.
3.
Right-click
the new data textbox, which currently displays the following expression:
=First(Fields!Measures_Percent_Total_Sales.Value)
4.
Select Properties
from the context menu that appears, as shown in Illustration 48.
Illustration 48: Select
Properties for the New Data Item
5.
The Textbox
Properties dialog opens.
6.
Replace the
text in the Name box (which was automatically provided) with the
following:
PercTotalSales
7.
Modify the
existing expression in the Value box from:
=First(Fields!Measures_Percent_Total_Sales.Value)
to the following:
=SUM(Fields!Measures_Percent_Total_Sales.Value)
8.
Click the Standard
radio button in the Format section (right half of the dialog) to
select it.
9.
Click Percentage
within the Standard list.
10.
Click the top
selection in the two options in the right-most pane (the selection that
provides for two decimal points).
The Textbox
Properties dialog appears as depicted in Illustration 49.
Illustration 49: The
Textbox Properties Dialog, with Our Modifications
11.
Click OK
to accept our changes and to close the dialog.
12.
Double-click
the box directly above the textbox just modified (currently labeled Measures Percent Total Sales).
13.
Replace the
text in the box (which was automatically provided) with the following:
Cube % Total
14.
With the newly
labeled field still selected, select Format --> Justify from the main menu of the Report
Designer.
15.
Select Center
from the cascading menu that appears, as shown in Illustration 50.
Illustration 50: Centering
the New Label ...
The
report appears, in Layout view, as depicted in Illustration 51.
Illustration 51: The
Calculated Member Renamed ...
16.
Click the Preview
tab, atop the design environment.
The
report executes quickly, and appears as shown in Illustration 52.
Illustration 52: Multi-Layer_Calculations,
Reflecting Modifications ...
We see
the effects of our handiwork. We can easily compare the values to those we see
in the Cube Browser of Analysis Services, to ascertain accuracy. Next,
we will investigate the accomplishment of the same results with a solution that
is freestanding within the report layer.
» 
Comment and Contribute
