Conditional Formatting in jaspersoft’s OLAP view (mondrian jPivot)
- Open {js-install}\apache-tomcat\webapps\jasperserver-pro\jpivot\table\mdxtable.css and {js-install}\apache-tomcat\webapps\jasperserver-pro\j\mdxtable.css.
- 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;*/
}
- 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;*/
}
- 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;
}
- 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]
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]
Best Open Source Business Intelligence Software Helical Insight is Here
