<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7654.12">
<TITLE>RE: Conditional Formatting</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Julian,<BR>
<BR>
I'm not sure what you mean by adding it to Mondrian since the parsing is done by the OLAP client who reads the cell formatted value. I've added unit tests to Analyzer already.<BR>
<BR>
Here's the snippet from Analyzer:<BR>
<BR>
String formattedValue = cell.getFormattedValue(); // Apply format defined on measure such as $#,##0.00<BR>
if (formattedValue.startsWith("|"))<BR>
{<BR>
String[] strs = formattedValue.substring(1).split("\\|");<BR>
formattedValue = strs[0]; // original value<BR>
for (int i = 1; i < strs.length; i++) {<BR>
String propName = null;<BR>
String propValue = null;<BR>
Matcher m = regex1.matcher(strs[i]);<BR>
if (m.matches()) {<BR>
propName = m.group(1); // property name<BR>
propValue = m.group(2); // property value<BR>
properties.put(propName, propValue);<BR>
continue;<BR>
}<BR>
<BR>
m = regex2.matcher(strs[i]);<BR>
if (m.matches()) {<BR>
propName = m.group(1); // property name<BR>
propValue = m.group(2); // property value<BR>
properties.put(propName, propValue);<BR>
continue;<BR>
}<BR>
<BR>
// it is not a key=value pair<BR>
// we add the String to the formadded value<BR>
formattedValue += strs[i];<BR>
}<BR>
}<BR>
<BR>
The two regex are as follows:<BR>
<BR>
private Pattern regex1;<BR>
private Pattern regex2;<BR>
...<BR>
<BR>
regex1 = Pattern.compile("\\s*([a-zA-Z][\\w\\.]*)\\s*=\\s*'([^']*)'");<BR>
regex2 = Pattern.compile("\\s*([a-zA-Z][\\w\\.]*)\\s*=\\s*([^\\s]*)");<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: Julian Hyde [<A HREF="mailto:jhyde@pentaho.com">mailto:jhyde@pentaho.com</A>]<BR>
Sent: Tue 6/15/2010 6:23 AM<BR>
To: Benny Chow<BR>
Cc: mondrian@pentaho.org<BR>
Subject: RE: Conditional Formatting<BR>
<BR>
Great.<BR>
<BR>
If you feel like throwing the parsing code over the wall to me, I will add<BR>
it to mondrian. Maybe add a couple of unit tests too. "The tests are the<BR>
specificiation" and all that good stuff.<BR>
<BR>
Julian<BR>
<BR>
<BR>
_____ <BR>
<BR>
From: Benny Chow [<A HREF="mailto:bchow@pentaho.com">mailto:bchow@pentaho.com</A>]<BR>
Sent: Monday, June 14, 2010 7:41 AM<BR>
To: Julian Hyde<BR>
Cc: mondrian@pentaho.org<BR>
Subject: RE: Conditional Formatting<BR>
<BR>
<BR>
<BR>
I looked at the JPivot source code and indeed it looks for a leading "|".<BR>
After that, JPivot splits whatever Mondrian returns from<BR>
cell.getFormattedValue using "|" as the separator. JPivot then applies a<BR>
regular expression to find name value pairs separated by a "=". The name<BR>
part corresponds to "style", "link", "arrow" or "image" as described here:<BR>
<A HREF="http://jpivot.sourceforge.net/news.html">http://jpivot.sourceforge.net/news.html</A> -> Member Properties<BR>
<BR>
<BR>
<BR>
I'm going to keep the same standard for Analyzer since it means people can<BR>
re-use their JPivot mondrian schemas with Analyzer and keep their cell<BR>
formatting.<BR>
<BR>
<BR>
<BR>
Benny<BR>
<BR>
<BR>
<BR>
<BR>
_____ <BR>
<BR>
<BR>
From: Julian Hyde [<A HREF="mailto:jhyde@pentaho.com">mailto:jhyde@pentaho.com</A>]<BR>
Sent: Sunday, June 13, 2010 12:04 AM<BR>
To: Benny Chow<BR>
Cc: mondrian@pentaho.org<BR>
Subject: RE: Conditional Formatting<BR>
<BR>
<BR>
<BR>
Yes, there is a semi-standard way of formatting values to include style<BR>
information. I believe that it started with Micrsoft OLAP Services, and for<BR>
JPivot we adopted the same standard. (Although now when I search, most of<BR>
the examples using this are Mondrian-related.)<BR>
<BR>
<BR>
<BR>
I believe that the leading '|' is the clue that style information is<BR>
present. But the standard is essentially arbitrary, and I don't know whats<BR>
supposed to happen if the formatted value actually starts with a '|'.<BR>
<BR>
<BR>
<BR>
You could start a new standard. It's definitely possible to return the style<BR>
in a different calc member. As long as you document what analyzer is<BR>
expecting.<BR>
<BR>
<BR>
<BR>
Julian<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
_____ <BR>
<BR>
<BR>
From: Benny Chow [<A HREF="mailto:bchow@pentaho.com">mailto:bchow@pentaho.com</A>]<BR>
Sent: Saturday, June 12, 2010 7:41 AM<BR>
To: Julian Hyde<BR>
Subject: Conditional Formatting<BR>
<BR>
Hi Julian,<BR>
<BR>
<BR>
<BR>
I'm looking into some conditional formatting for Analyzer and I tried out<BR>
your suggestion in this forum post you made:<BR>
<BR>
<BR>
<BR>
<A HREF="http://forums.pentaho.org/showthread.php?t=47222">http://forums.pentaho.org/showthread.php?t=47222</A><BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<CalculatedMember<BR>
name="Profit"<BR>
dimension="Measures"<BR>
formula="[Measures].[Store Sales] - [Measures].[Store Cost]"><BR>
<CalculatedMemberProperty name="FORMAT_STRING" expression="<BR>
Iif([Measures].[Profit] < 100000, '|$#,##0.00|style=green',<BR>
'|$#,##0.00|style=red')"/><BR>
</CalculatedMember><BR>
<BR>
<BR>
<BR>
<BR>
<BR>
For the cell that has this member as its context, suppose Profit is 100.<BR>
Will cell.getFormattedValue return $100.00 or |$100|style=green ? I am<BR>
currently getting the ladder. If the ladder is correct, is JPivot doing<BR>
some post processing by parsing the formatted cell value and then showing<BR>
the $100 in HTML and then applying the other stuff like style=green as a CSS<BR>
attribute? <BR>
<BR>
<BR>
<BR>
Maybe there is some special handshake that's going on between Mondrian cell<BR>
formatting and JPivot that makes the conditional formatting work. <BR>
<BR>
<BR>
<BR>
Any insight would be much appreciated.<BR>
<BR>
<BR>
<BR>
Thanks<BR>
<BR>
Benny<BR>
<BR>
<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>