Percent of Total - Two Perspectives
Introduction and Business Scenario
A request for assistance
that I receive on a frequent basis, via e-mail, forums, and elsewhere, involves
the need to calculate a percent of a total, primarily to determine
members' contribution to greater wholes. This determination is quite desirable
and common in financial and other reporting. A good example might be the percentage
of total organizational revenue generated by each individual store, to support
analysis of store performance relative to peer stores in a group, or for other
possible purposes. We might build further sophistication to allow us to perform
the calculation of each store's share of the subtotals at the various levels
of the Store dimension hierarchy (City, State and Country),
but in the present scenario, we will find ourselves tasked with a relatively
basic requirement.
Let's
assume for our practice example that we have received a request from a group of
information consumers in the Budget and Planning unit of the FoodMart
2000 organization. The request is for support in the presentation of some
data, housed within the Sales sample cube, regarding USA Store Sales
for 1997. The consumers wish to see the percentage contribution of
each Store to the total USA Store Sales, alongside the sales values
that are already available for each Store. To restate, they want to see the
percentages of total USA
Store Sales that
can be attributed to each Store.
The ultimate report,
we are told, will display USA Stores on the row axis, with Store
Sales on the column axis. The report will require no drilldown features, and
will concern itself with 1997 data only. Its use will be strictly
limited to a high-level analysis of store performance from a Store Sales
perspective.
We will
examine the satisfaction of the request of the information consumers through
two independent means. First, we will present a solution at the Analysis
Services layer of the business intelligence system, using a straightforward
calculated member to deliver the percent of total value. We will
then examine a solution that will be completely contained within Reporting
Services. We will perform verification to ensure that both solutions
deliver the same answer at the presentation layer of the system by inserting
both into a report we modify especially for that purpose. Finally, we will
discuss some of the advantages that lie within each solution, and the some of
the contexts within which one might be a more optimal approach than its
counterpart. My objective is thus to illustrate the fact that we can provide
solutions at different layers of the system to meet the hypothetical business
requirement we have outlined, as a means of reinforcing the concepts in our
minds. Once we have activated the concepts, they can then be triggered upon
meeting similar situations in our respective business environments.
This
article focuses on two approaches to the same end, one at the data source
(an Analysis Services cube), and another at the reporting layer (a
Reporting Services report). In addition to providing approaches to meet the
need to present percent of total information, another important
objective of this article is to emphasize the fact that, even though Reporting
Services is an excellently flexible tool for generating calculations,
manipulating data, and more, an integrated philosophy needs to be adopted in an
environment where we can perform many operations at different layers within a "chained"
solution.
Considerations and Comments
Before we make
alterations to the FoodMart Sales cube to support the Percent of
Total calculation in our report, we will create a copy of the cube. Creating
a clone of the sample Sales cube means we can make changes to our cube
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 Analysis Services
in general. Once we finish with our structural changes to the clone cube, we
will create a copy of the Foodmart Sales sample report, for the same
reasons.
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. In addition, 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 same reasons we do so for the
Sales sample cube. 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.
» 