Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 14, 2007

Positing the Intelligence: Conditional Formatting in the Analysis Services Layer - Page 3

By William Pearson

Examine Conditional Formatting at Work in the Browser

Let’s set up the browser axes to approximate the proposed report display, so as to verify the intended results of our conditional formatting handiwork.

1.  Click the Reconnect button in the Browser toolbar.

2.  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 Fields Here.

3.  Expand the Sales Territory dimension in the cube metadata tree, and drag the Sales Territory Group attribute hierarchy into the area marked Drop Column Fields Here.

4.  Expand Measures, 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:

  • Internet Order Quantity
  • Internet Sales Amount
  • Internet Gross Profit Margin

A partial view of our newly assembled and populated browser grid is depicted in Illustration 4.

Illustration 4: Reconnect: The Browser with our Viewer Settings (Partial View) ...

And so we see that our conditional formatting logic appears effective.

We can 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 cumbersome.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM