iccube-reporting Conditional Formatting With MDX Expressions

Question

How can conditional formatting with MDX expressions be used in iccube. The goal is to add colors on basis of values. For example, divide a value from an MDX measure into ranges and apply a color per range to the background of a cell in a table.

ic3 ic3 · Accepted Answer · 2017-07-18T10:14:58

There are a few options

1 - Using MDX Cell Properties

The first step is creating an MDX expression that defines a background color for each cell of the MDX expression. The cell property used is 'BACK_COLOR' , doc.

WITH  
  MEMBER [Colored Amount] as [Amount] ,
             BACK_COLOR=InterpolateRGBColors(currentCellValue() / (1000*1000),RGB("#DBF2CC"), RGB("#C2AB4D") ) 
SELECT  
  [Product].[Product].[Article] on 0 
FROM [MyCube] WHERE  [Colored Amount] 
CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR

For building the MDX query we've been using a few functions that allow to work with colors InterpolateRGBColors and RGB and CurrentCellValues.

RGB allows to transform an RGB color to an MDX color, and the interpolate function is going to transform the measure, first parameter, to a color.

Once the MDX has been created we can use the reporting to use the colors as defined in the MDX. This can be done in the 'Color Editor Dialog'

Using different measures

Here we would leverage the new reporting features since version 6 and use multiple measures. The MDX would look similar :

WITH
  MEMBER [Measures].[Color] AS toHexColor( InterpolateRGBColors( [Amount] / (1000*1000),RGB("#DBF2CC"), RGB("#C2AB4D") ) )
SELECT
  // Measures
  {[Measures].[Amount],[Measures].[Color]} ON 0,
  // Rows
  [Product].[Product].[Article].allmembers ON 1
FROM [Sales]
CELL PROPERTIES STYLE, CLASSNAME, VALUE, FORMATTED_VALUE, FORMAT_STRING

Instead of using cell properties we define a new measure [Color] that will hold the value of the color. This MDX can also be created with the Wizard.

Once the MDX statement is ready we can use the color in the 'Color Editor Dialog' using the MDX tab

and there it is again our table with colors.

iccube-reporting Conditional Formatting With MDX Expressions

1 Answers