Cube Design - Professional SQL Server Analysis Services 2005 with MDX - page 261

5. You have the option of specifying certain additional properties
for the calculated measures you have created based on an MDX
expression. By default all the calculated measures created are
visible. You can specify color, font, and format strings for the
calculated measures based on certain conditions. For example, if
you want to highlight the profit in red if the amount is less than
one million dollars and in green if it is greater than or equal to
one million, you can do so by specifying the appropriate back-
ground color for the calculated member. Enter the following MDX
expression for the background color:
iif (
[Measures].Profit < 1000000, 255 /*Red*/,
65280 /*Green*/)
The MDX expression uses the IIF function. This function takes
three arguments. The first argument is an expression that
should evaluate to true or false. The return value of the iif func-
tion is either the second or the third argument passed to the
function. If the result of the expression is true, the IIF function
returns the second argument; if the expression is false, it re-
turns the third argument. The first argument passed to the IIF
function is to see if the profit is less than one million. If second
and third arguments passed to the function are the values for
the colors red and green. The values for the colors can be selec-
ted by clicking the color icon next to the background color text
box.
6. To see the effect of the calculations you have created, go to
the Cube Browser tab and deploy the project. As soon as the
deployment is complete you will be asked to reconnect to the
server in the Cube Browser. Reconnect to the server. If you ex-
pand the Measures folder you will see the calculated measures
you have created. Drag and drop the measure Profit to the OWC
detail area, hierarchy English Country Region name of the Dim
Geography dimension on rows and hierarchy Style of the Dim
Product dimension on columns. You will see the background col-
or for the cells are either red or green based on the Profit value
as shown in Figure 6-29 .