Extending Conditional Formatting: SWITCH and Drilldown Defaults
Introduction and Business Scenario
A large portion of the
requests for assistance that I receive, from direct clients, or via e-mail, forums,
and elsewhere, involve the need to perform conditional formatting of
some sort upon a value, based upon the amount / quantity that the value
represents. In addition, I often receive requests for approaches to replicate "tried
and true" presentation effects that have long been the standard within a
given environment or within larger disciplines, such as accounting and finance
in general. Finally, and more commonly within the settings of my work with
pioneering clients, I receive requests for approaches to other conditional
formatting scenarios that extend the uses for various functions into
another realm: the control of the default and other behavior of reporting
objects in a more mechanical way, beyond mere formatting in the routine sense,
and into leveraging more "mechanical" attributes within a report.
In this
article, we will examine a formatting technique for meeting a common
requirement, particularly when working with Accounting and Finance
practitioners, but with other functional groups as well. These information
consumers often like to see negative values presented in parentheses,
as opposed to carrying a negative sign in front. Moreover, such audiences
often prefer to see negatives in red, to have their attention drawn to
the negative numbers. This preference might, based upon the financial statement
or report, extend to the "positive" numbers, too: credits,
formatted with negative signs in many cases, are often a "good"
thing. A report that focuses, say, on revenues, which are stored in an
accounting system as negatively-signed credits, might seek to draw attention to
debits (expenses, for example), which in cases like this might actually be
presented as "positive" numbers. My point here is that the
logic we apply in Reporting Services to "highlight" a negative value
(say, with a red font) can just as easily be used to highlight a positive
value, as circumstances dictate.
In
addition to the conditional formatting of values, we will examine
another presentation nuance that I have found to be popular recently among
multiple clients with various reporting needs: a feature within a report that
presents a given level in one default drilldown state in one way (say, defaults
a line item to "drilled down") while presenting another, or a group
of other, line items, in another default state ("rolled up.") As we
shall see, this capability will be useful in various scenarios where we wish to
focus on the details of a given activity, while providing summarized
information about areas that are not typically the focus, but upon which we
might still want to perform ad hoc examination of underlying details for
various reasons.
We will illustrate the
aforementioned needs within our usual context of a business scenario. We will
assume that we have received a request from a group of information consumers in
the Finance Department of the FoodMart 2000 organization. The
request is for additional support in the presentation of some data, housed
within the Sales sample cube,
The
information consumers tell us that the existing FoodMart Sales report (for
operating year 1997, their current year), with a few modifications,
would serve well in presenting the data they need for a monthly presentation to
management. The group states, first, that the report will be intended for a
single use, and will not require parameterization (FoodMart Sales
parameterizes on Product Family). Product Family will become a
key grouping in the report, however, and will appear to the left of the
currently left-most column, Product Department.
Values
presented in the report will need to reflect conditional formatting.
Specifically, the information consumers, as members of the Accounting and
Finance fraternity, wish to see "negative" Store Profit
numbers, within the context of this report, as "bracketed" (enclosed
in parentheses), with a red font, so as to drive attention to these outliers.
Moreover, the information consumers wish the Drink and Food categories,
which will form the focus of the new report, to present Product Families
with a "drilled down" state as the default. In contrast, the consumers
tell us that Non-Consumable products, while not in themselves a focus of
the report, need to be included in summary, to provide the "tie"
to the totals, which must agree to the existing FoodMart Sales report.
We ask the group if they would prefer a summary line that could be drilled down
upon should the need arise, and they concur that this might be a useful
attribute.
We listen
carefully to the requirements, and, as a confirmation of our understanding,
present a spreadsheet mockup of the report, as shown in Illustration 1,
with the specifications we have discussed to the consumers.
Illustration 1: A Draft
(Using MS Excel) of the Desired End Report ...
The
consumers agree that the draft reflects the business requirements, and so we
begin the desired enhancements, as we shall see, in the steps that follow.
Considerations and Comments
For purposes of our
practice session, we will create a copy of the Foodmart Sales sample report.
Creating a clone of the report means we can make changes to our report while
retaining the original sample in a pristine state perhaps for other purposes,
such as using it to accompany relevant sections of the Books Online, and
other documentation, in learning more about Reporting Services in general.
Before we can work
with a clone of the Foodmart Sales report, we need to create a Reporting
Services project in the Visual Studio.Net 2003 Report Designer
environment. Streamlining, and then making the enhancements to the report to
add the requested functionality, can be done easily within the Reporting
Services Report Designer environment. Working with a copy of the report
will allow us the luxury of freely exploring our options, and leave us a
working example of the specific approach we took, to which we can refer in our
individual business environments. This approach also preserves the original
sample in a pristine state for the reasons we have already mentioned. If you
already have a project within which you like to work with training or development
objects, you can simply skip the Create a Reporting Services Project section.
If the sample FoodMart
2000 Analysis Services database or the Foodmart Sales report was not
created / installed as part of the initial installation of the associated
application, if either was removed prior to your beginning this article, or if
either or both applications have yet to be installed, etc., see the respective Books
Online or other documentation for the necessary procedures to prepare
for the exercises that follow.