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.
Illustration 12:
The Report Clone – Layout View
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.
Illustration 13: Select
Edit ... from the Context Menu
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.
Illustration 14: Click
the Function Button ...
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.
Illustration 15: The
Replacement Expression in the Expression Box (Partial View)
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.
Illustration 16: Select
Report --> Report Parameters from the Main Menu
The Report
Parameters dialog, where we define parameters for the report, appears, as
shown in Illustration 17.
Illustration 17: The
Report Parameters Dialog
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.
Illustration 18:
Accessing the Matrix Properties
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.
Illustration 19: Select
and Delete the Parameter Reference
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.
» 
Comment and Contribute
