MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 5
August 15, 2005
Modify the Foodmart Sales Report to Prepare for Our Procedural Steps
The sample objects that come along with an installation of Reporting Services are often the best to use in a practice example such as the one that follows, primarily because virtually everyone who has installed Reporting Services has access to these samples. The use of copies of pre-fabricated reports, among other sample objects, in this and other of my articles has saved significant amounts of time in preparing for practice sessions, allowing us to focus upon the specific techniques which the article is contrived to address. One of the disadvantages of working with readily available sample objects is that they are just that samples - and, as most of have become aware, the samples provided with even enterprise-level applications can be quite simplistic with regard to their reflection of business reality.
We have before us an example of this that we will need to manage with a minor adjustment: one of the specifications of the information consumers includes the conditional formatting of the existing Store Profit values. The consumers have stated that they wish to have negative values formatted in a manner that attracts attention. To perform the steps necessary to demonstrate an approach to doing this requires one thing that is not found in the sample data a negative Store Profit number! It would seem that FoodMart never meets with anything but a profit, even at a granular level, with any product it sells.
We will adjust the Store Cost value, within the Store Profit calculated field, with a simple multiplier to generate a few negative numbers, to make the conditional formatting portion of our practice session possible. To do this, as well to prepare the report further for our exercises, we will take the following steps:
1. Within the RS020 project tree in the Solution Explorer, double-click the new SWITCH_CondFormat report, to open it.
The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 12.
2. Right-click the Store_Profit field in the Report Designer Field List. (If the Field List does not appear, resurrect it by selecting View --> Fields from the main menu).
3. Select Edit... from the context menu that appears, as shown in Illustration 13.
The Edit Field dialog opens.
4. Click the Expression Editor (Fx) button that appears at the immediate right of the Calculated field box, in the lower portion of the dialog, as depicted in Illustration 14.
The Expression Editor opens.
5. Replace the expression within the Expression box with the following:
=Fields!Store_Sales.Value - 2.5*(Fields!Store_Cost.Value)
The Expression box appears, with the new expression, as partially shown in Illustration 15.
6. Click OK to accept our modification, and to close the Expression Editor.
We have now adjusted the Store Profit calculated field to generate some negative numbers in the report. We will, of course, be forced to "suspend disbelief" in the accuracy of the Store Profit values (no longer simply Store Sales minus Store Cost), but this will be a simple sacrifice to allow anyone with the standard samples to perform the exercises that follow. (We can be confident that calculated fields can generate accurate values in simple subtraction scenarios like this in the real world our focus here is conditional formatting, and this is simply a way to make the process possible).
7. Click OK to close the Edit field dialog.
We are returned to the Layout view for the report. To conclude our preparation steps, we will eliminate the existing Product Family parameter from the report, in accordance with the expressed wishes of the information consumers.
8. Select Report --> Report Parameters (click a point within the Layout view of the report to enable the Report menu item, if it does not already appear) from the main menu atop the Report Designer, as depicted in Illustration 16.
The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 17.
9. In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.
10. Click the Remove button underneath the Parameters list.
The ProductFamily parameter is removed from the list.
11. Click OK to accept removal of the parameter.
The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We must now delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.
12. Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.
13. Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)
14. Select Properties from the context menu that appears, as depicted in Illustration 18.
The Matrix Properties dialog opens, defaulted to the General tab.
15. Click the Filters tab.
16. Click the Value field of the single occupied row to select it.
17. Click the Delete button to delete the reference to the parameter, as indicated in Illustration 19.
The remaining reference to the now-deleted parameter is itself deleted.
18. Click OK to accept our changes and to close the Matrix Properties dialog.
We are now ready to proceed with modifications to our report to meet the conditional formatting and other presentation requirements of the information consumers.