Formatting at Work in the Browser
set up the browser axes to approximate the proposed report display, so as to
verify the intended results of our conditional formatting handiwork.
Click the Reconnect
button in the Browser toolbar.
Expand the Sales
Reason dimension in the cube metadata tree, and drag the Sales Reason attribute
hierarchy to the empty data grid, dropping it in the area marked Drop Row
Expand the Sales
Territory dimension in the cube metadata tree, and drag the Sales
Territory Group attribute hierarchy into the area marked Drop Column
and then the Internet Sales Measure Group. Drag the following measures
/ calculations (preferably in the order shown) to the area of the grid marked Drop
Totals or Detail Fields Here:
partial view of our newly assembled and populated browser grid is depicted in Illustration
Reconnect: The Browser with our Viewer Settings (Partial View) ...
we see that our conditional formatting logic appears effective.
leverage cube-based conditional formatting, and enjoy the multiple
benefits we have enumerated with ease, assuming a client application that
exposes cell properties. While Reporting Services does not expose them
directly, there are other ways to make cube-based formatting just as accessible
to report authors there, as well. (I introduce variations on this theme within
articles of my MSSQL Server Reporting Services series at Database Journal).
In this article, we resumed
a discussion that is no doubt familiar to anyone who regularly reads my
articles about the integrated Microsoft business intelligence solution. We mentioned
considerations surrounding the placement of the intelligence within the layers
of the solution, and then focused upon another specific example of such a
consideration here: the placement of the logic to support conditional
formatting in the Reporting layer. We emphasized the fact that
selection among the various layers for positing business intelligence support
can become extremely important in enterprise-level design and implementation,
and that multi-level awareness and often expertise - within the team
involved is required to optimize a multi-level solution.
We noted that, because most
enterprise reporting tools (such as MSSQL Server Reporting Services)
make conditional formatting easy to put into place, and because it is within
the deployed reports that the resulting data presentations are manifest, it is
often assumed that conditional formatting is naturally handled within the Reporting
layer. Because conditional formatting expressions can become complicated
and resource intensive, processing time and other attributes of report
processing can be adversely affected, and report maintenance can become overly
As a possible means of optimizing
the overall business intelligence solution, we proposed that the Analysis
Services layer might serve as an optimal home for conditional formatting support.
We examined three examples of setting cell properties within Analysis
Services with conditional logic to support this. Finally, we
verified the effectiveness of our approach via the Cube Browser, and
discussed how we might leverage our conditionally determined cell properties
within the Reporting layer.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.