Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I - Page 5

Without going too far into the details of the nature of the RGB system, let's say that a normal color is represented by a number between 0 and 16,777,215. In our example, we can define our color combination by selecting the hue (a number ranging between 0 and 255, with 255 being the brightest and 0 being the darkest within the range) and plugging that hue into the respective color variables (R, G, or B) in the following formula I derive. The resulting number is the color value, which is placed into the appropriate ForeColor and BackColor cell property box on the Advanced tab.

To simplify, all we really need to know is that the numerical components for color value are represented as Red, Green and Blue (assigned R, G and B, respectively) in the following formula.

Color Value                     =                 (R * 256 0) + (G * 256 1) + (B * 256 2)
             =     1(R) + 256(G) + 256 2 (B)
             =     R + 256G + 65536B

Eight select sample colors and their corresponding RGB hue values are summarized in Table 1.

Let's say we want to customize our exception highlighting ForeColor to have a magenta appearance. We would derive the color value as follows:

Color Value	=	R + 256G + 65536B
     		=	255 + 256 (0) + 65536 (255)
	    	=	255 + 0 + 16,711,680
	    	=	16,711,935

Now, let's derive the needed setting to make our BackColor property equal Black. This is easy enough, for we can see from the table above that all settings are zeroes.

Color Value	=	R + 256G + 65536B
    		=	0 + 256 (0) + 65536 (0)
	    	=	0

The setting, as we see, is zero.

Why, then, should we have to type anything into the BackColor property box? Although it seems that "nothing," or null, might equal "zero" in a field, the default is set for white as the color in this case, simply because this would be the most common desire in presentation.

We can also set other display characteristics on the Advanced tab. The FontFlags property, for example, can have the basic settings, or a combination of the settings, shown in Table 2.

We can add numbers together to achieve combinations of their effects. Examples might include the value 5, which represents the combination of bold and underline font effects, and the value 3, which represents the combination of bold and italic.

The FontName property allows us to set the font of the displayed calculated cell, whereas the FontSize property makes it possible to customize the font size of the calculated cell. We will leave these at the default values.

The Format String property enables the customization of the format for displaying cell values. The set of possible values within the Format String property is substantial; a user-defined format expression for numbers can have anywhere from one to four sections separated by semicolons, and its values can be different--depending on the data types involved (numeric, string, etc.). For our example, we will be dealing with a numeric value, Warehouse Cost, so we can use up to four parameters, in the format string value that we assign, to instruct Analysis Services how to handle positive numbers, zero values, negative numbers, and null values, in that order. Although potentially confusing, the option also exists to supply fewer than the four parameters: the values given will then apply to more than one of the four standard groups, creating a composite parameter, as it were. An example is illustrated in our next step.