Add conditional formatting to your PerformancePoint Services Analytic Grid, by defining a custom Scope calculation from within Analysis Services (SSAS)

Ever wonder how to provide conditional formatting to your PerformancePoint Services Analytic Grid? For example, you may want change the fore-color for measure values to green for positive values and red for negative values.

Here’s a quick tip on how to accomplish this task.

 

Define Custom Scope Calculation

1) Open an existing Analysis Services Project (which is currently used by a PerformancePoint Analytic Grid).

2) Expand the Cubes folder and double click the desired cube to open Cube Designer

3) Select the Calculations tab

4) From within the Script Organizer pane, place your cursor on the CALCULATE item, right click and select New Script Command

 

5) From within the Scripting Pane, enter the following code (exclude the comments):

SCOPE

([Measures].AllMembers)

// apply to all members in Measures Group

6) Add another New Script Command under the one previously created and add the following code (exclude the comments):

/* Change the cell property for the measures current member.

If measure value is less than 0, then make red, else, if measure is

greater than 0 and not equal to "NA", then make green, else do

nothing (black).

*/

FORE_COLOR (This) = IIF([Measures].CurrentMember <0,'255',

IIF([Measures].CurrentMember >0 AND

[Measures].CurrentMember <> "NA",'32768', '0'))

7)  Add another New Script Command under the one previously created and add the following code:

END SCOPE

 

Note: The FORE_COLOR and BACK_COLOR cell properties store color information for the text and the background of a cell, respectively, in the Microsoft Windows operating system red-green-blue (RGB) format.

You should see something similar:

8) Save your work and process/deploy the cube.

 

Validate Conditional Format in PerformancePoint Dashboard Designer

1) Now run your existing Analytic Grid in PerformancePoint Dashboard Designer and you should see the FORE_COLOR of the measures have changed.

 

 

Download: Analysis Services Tutorial.zip

 

Cheers!

  

 

Add comment