Conditional Formatting in jaspersoft’s OLAP view (mondrian jPivot)

 Conditional Formatting in jaspersoft’s OLAP view (mondrian jPivot)

  1. Open {js-install}\apache-tomcat\webapps\jasperserver-pro\jpivot\table\mdxtable.css and {js-install}\apache-tomcat\webapps\jasperserver-pro\j\mdxtable.css.
  2. In these files find the following section

/* data cells */

td.cell-even,

td.cell-odd,

td.cell-red,

td.cell-yellow,

td.cell-green,

td.cell-rot,

td.cell-gelb,

td.cell-gruen,

td.property-value,

td.property-span {

font-family: Arial, Helvetica, Tahoma, sans-serif;

font-size: 80%;

/*color : Black;*/

color : #033669;

text-align: right;

/*border-bottom: solid 1px #000033;

border-right: solid 1px #000033;

border-top: solid 1px #000033;

border-left: solid 1px #000033;*/

}

  1. Add 3 styles for 3 different colors as shown below

/* data cells */

td.cell-even,

td.cell-odd,

td.cell-red,

td.cell-yellow,

td.cell-green,

td.cell-rot,

td.cell-gelb,

td.cell-gruen,

td.cell-below_avg_red,

td.cell-avg_orange,

td.cell-above_avg_green,

td.property-value,

td.property-span {

font-family: Arial, Helvetica, Tahoma, sans-serif;

font-size: 80%;

/*color : Black;*/

color : #033669;

text-align: right;

/*border-bottom: solid 1px #000033;

border-right: solid 1px #000033;

border-top: solid 1px #000033;

border-left: solid 1px #000033;*/

}

  1. Then a little down you will see some blocks that look like this. This one in particular is for green.td.cell-gruen,
    td.cell-green,
    span.cell-gruen,
    span.cell-green
    {
    background-color: #66FF66;
    }

    So, now just copy this one, and make it for 3 new styles added like this:

 td.cell-below_avg_red,

span.cell-below_avg_red

{

background-color: #FF0000;

}

 

td.cell-avg_orange,

span.cell-avg_orange

{

background-color: #FFBF00;

}

 td.cell-above_avg_green,

span.cell-above_avg_green

{

background-color: #04B404;

}

  1. Now, within the MDX query you can reference these styles as shown below:

 a)      with member [Measures].[Profit] as ‘([Measures].[Store Sales] – [Measures].[Store Cost])’, FORMAT_STRING = CASE WHEN (([Measures].[Profit] <= 300000.0) AND ([Measures].[Profit] > 100000.0)) THEN “|#|style=above_avg_green” WHEN (([Measures].[Profit] <= 100000.0) AND ([Measures].[Profit] > 50000.0)) THEN “|#|style=avg_orange” ELSE “|#|style=below_avg_red” END select {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS, {[Product].CurrentMember.Children} ON ROWS from [Sales]

2

b)      with member [Measures].[Profit] as ‘([Measures].[Store Sales] – [Measures].[Store Cost])’, FORMAT_STRING = CASE WHEN (([Measures].[Profit] <= 101.0) AND ([Measures].[Profit] > 75.0)) THEN “|#|style=above_avg_green” WHEN (([Measures].[Profit] <= 75.0) AND ([Measures].[Profit] > 50.0)) THEN “|#|style=avg_orange” ELSE “|#|style=below_avg_red” END

select {([Time].[2012].[Q1], [Measures].[Profit]), ([Time].[2012].[Q2], [Measures].[Profit]), ([Time].[2012].[Q3], [Measures].[Profit]), ([Time].[2012].[Q4], [Measures].[Profit])} ON COLUMNS,

{[Product].[Drink].[Beverages].[Carbonated Beverages].[Soda].[Excellent].[Excellent Cola], [Product].[Drink].[Beverages].[Carbonated Beverages].[Soda].[Excellent].[Excellent Cream Soda], [Product].[Drink].[Beverages].[Carbonated Beverages].[Soda].[Excellent].[Excellent Diet Cola], [Product].[Drink].[Beverages].[Carbonated Beverages].[Soda].[Excellent].[Excellent Diet Soda], [Product].[Drink].[Beverages].[Pure Juice Beverages].[Juice].[Excellent].[Excellent Apple Juice], [Product].[Drink].[Beverages].[Pure Juice Beverages].[Juice].[Excellent].[Excellent Berry Juice], [Product].[Drink].[Beverages].[Pure Juice Beverages].[Juice].[Excellent].[Excellent Cranberry Juice], [Product].[Drink].[Beverages].[Pure Juice Beverages].[Juice].[Excellent].[Excellent Orange Juice]} ON ROWS

from [Sales]

1