the Profit Margin Calculated Measure
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.
Click the Layout
Locate the Fields
Selector window (I keep mine docked, for easy access), and ensure that ProductData
appears in the DataSet selector atop the window.
any blank area within the Fields Selector window.
from the context menu that appears, as depicted in Illustration 31.
Illustration 31: Select
Add to Create a New Calculated Field
New Field dialog appears.
following into the Name box:
radio button to the immediate left of Calculated field to select it.
Click the Expression
(fx) button to the right of the Calculated field box.
Expression dialog opens.
following into the Expression box to the right of the dialog:
Expression dialog appears as shown in Illustration 32.
Illustration 32: Expression
for the New Profit Margin Calculated
to accept the expression, and to close the Edit Expression dialog.
Add New Field dialog appears as depicted in Illustration 33.
Illustration 33: The
Add New Field Dialog
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
place the new calculated field on the report, in accordance with the needs
expressed by the intended audience.
Click the Profit
Margin calculated field in the Fields Selector window, to select it.
Drag the Profit
Margin calculated field onto the Layout area of the report, to the
right of the right-most existing value, Store Profit.
calculated field when the cursor indicates a drop point to the right of Store
drop point, the cursor resembles that depicted in the inset picture in Illustration
Illustration 35: Drop
Point is Indicated in the Cursor Change
within the layout. We now need to make an adjustment to the expression
appearing in the report.
the Profit Margin calculated text box on the report layout.
from the context menu that appears, as shown in Illustration 36.
Illustration 36: Select
Expression ... from the Context Menu
Edit Expression dialog appears.
expression that appears in the Expression box to the following:
=Sum( Fields!Store_Profit.Value)/ Sum(Fields!Store_Sales.Value)
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).
Edit Expression dialog appears as depicted in Illustration 37.
Illustration 37: The
Modified Expression in the Edit Expression Dialog
Click OK to
accept the modification, and to return to the Layout tab.
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.
For the time
being, replace the "C0" (currency without decimals) in the Format
box of the Properties dialog with "P" (percentage with
will revisit this setting in the following section. The Properties dialog
appears as partially shown in Illustration 38, with our modification
Illustration 38: Format
Modification, Properties Dialog
execute the report to ensure all operates correctly, at this point.
Click the Preview
from the parameter picklist that appears atop the report on the Preview
Click the View
Report button to execute the report.
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
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.