MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 8
December 20, 2004
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.
The Add New Field dialog appears.
57. Type the following into the Name box:
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:
The Edit Expression dialog appears as shown in Illustration 32.
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.
62. Click OK.
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.
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.
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.
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.
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.
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.
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.