Add
the Profit Margin Calculated Measure
We will
next add a calculated field to the report to present the product Profit
Margin. While I would probably do this at the cube level in real life,
many cases arise where we have to work within Reporting Services to bring about
a desired effect, without the luxury of access to the underlying data source. Regardless
of its location, the Profit Margin calculation will be the basis, within
this article, for conditional formatting. Keep in mind that the concept is the
same, regardless of the genesis of the basis.
53.
Click the Layout
tab.
54.
Locate the Fields
Selector window (I keep mine docked, for easy access), and ensure that ProductData
appears in the DataSet selector atop the window.
55.
Right-click
any blank area within the Fields Selector window.
56.
Select Add
from the context menu that appears, as depicted in Illustration 31.
Illustration 31: Select
Add to Create a New Calculated Field
The Add
New Field dialog appears.
57.
Type the
following into the Name box:
Profit_Margin
58.
Click the
radio button to the immediate left of Calculated field to select it.
59.
Click the Expression
(fx) button to the right of the Calculated field box.
The Edit
Expression dialog opens.
60.
Type the
following into the Expression box to the right of the dialog:
=(Fields!Store_Profit.Value/ Fields!Store_Sales.Value)
The Edit
Expression dialog appears as shown in Illustration 32.
Illustration 32: Expression
for the New Profit Margin Calculated
Field
61.
Click OK
to accept the expression, and to close the Edit Expression dialog.
The completed
Add New Field dialog appears as depicted in Illustration 33.
Illustration 33: The
Add New Field Dialog
The Add
New Field dialog closes, and the Profit_Margin calculated field is
created, as evidenced by its appearance in the Fields Selector window
(shown in Illustration 34.
Illustration 34: The New Profit Margin Calculated Field in the Fields
Selector Window
Let's
place the new calculated field on the report, in accordance with the needs
expressed by the intended audience.
63.
Click the Profit
Margin calculated field in the Fields Selector window, to select it.
64.
Drag the Profit
Margin calculated field onto the Layout area of the report, to the
right of the right-most existing value, Store Profit.
65.
Drop the
calculated field when the cursor indicates a drop point to the right of Store
Profit.
At the
drop point, the cursor resembles that depicted in the inset picture in Illustration
35.
Illustration 35: Drop
Point is Indicated in the Cursor Change
Profit
Margin appears
within the layout. We now need to make an adjustment to the expression
appearing in the report.
66.
Right-click
the Profit Margin calculated text box on the report layout.
67.
Click Expression...
from the context menu that appears, as shown in Illustration 36.
Illustration 36: Select
Expression ... from the Context Menu
The
Edit Expression dialog appears.
68.
Change the
expression that appears in the Expression box to the following:
=Sum( Fields!Store_Profit.Value)/ Sum(Fields!Store_Sales.Value)
Note
that our change is simply restating the calculated field with its original
expression, and inserting a Sum() function around the denominator as
well as the numerator. The default expression, created when we dragged the
calculated field to the report layout, simply inserted the calculated field
wrapped in a single Sum() function, =Sum(Fields!Profit_Margin.Value).
The
Edit Expression dialog appears as depicted in Illustration 37.
Illustration 37: The
Modified Expression in the Edit Expression Dialog
69.
Click OK to
accept the modification, and to return to the Layout tab.
70.
Leaving the Profit
Margin textbox selected, open the Properties window (I keep mine
docked to the right of the Layout tab) for the text box.
71.
For the time
being, replace the "C0" (currency without decimals) in the Format
box of the Properties dialog with "P" (percentage with
2-decimals).
We
will revisit this setting in the following section. The Properties dialog
appears as partially shown in Illustration 38, with our modification
circled.
Illustration 38: Format
Modification, Properties Dialog
Let's
execute the report to ensure all operates correctly, at this point.
72.
Click the Preview
tab.
73.
Select Food
from the parameter picklist that appears atop the report on the Preview
tab.
74.
Click the View
Report button to execute the report.
The
report runs and returns the data associated with the Food Product
Family, once again. We note the appearance of the new Profit Margin
calculated field, as depicted in Illustration 39.
Illustration 39: Report (Partial View) with New Profit Margin
Calculated Field
We have
made the structural changes as requested by the information consumers, and we
are now ready to put into place the conditional formatting they have stated
that they wish to see.
» 