[Mondrian] RE: Conditional Formatting

[Benny Chow]

Julian,

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.

Here's the snippet from Analyzer:

        String formattedValue = cell.getFormattedValue();  // Apply format defined on measure such as $#,##0.00
        if (formattedValue.startsWith("|")) 
        {
            String[] strs = formattedValue.substring(1).split("\\|");
            formattedValue = strs[0]; // original value
            for (int i = 1; i < strs.length; i++) {
                String propName = null;
                String propValue = null;
                Matcher m = regex1.matcher(strs[i]);
                if (m.matches()) {
                    propName = m.group(1); // property name
                    propValue = m.group(2); // property value
                    properties.put(propName, propValue);
                    continue;
                } 
                
                m = regex2.matcher(strs[i]);
                if (m.matches()) {
                    propName = m.group(1); // property name
                    propValue = m.group(2); // property value
                    properties.put(propName, propValue);
                    continue;
                }
                
                // it is not a key=value pair
                // we add the String to the formadded value
                formattedValue += strs[i];
            }
        }

The two regex are as follows:

private Pattern regex1;
private Pattern regex2;
...

        regex1 = Pattern.compile("\\s*([a-zA-Z][\\w\\.]*)\\s*=\\s*'([^']*)'");
        regex2 = Pattern.compile("\\s*([a-zA-Z][\\w\\.]*)\\s*=\\s*([^\\s]*)");



-----Original Message-----
From: Julian Hyde [mailto:jhyde at pentaho.com]
Sent: Tue 6/15/2010 6:23 AM
To: Benny Chow
Cc: mondrian at pentaho.org
Subject: RE: Conditional Formatting
 
Great.
 
If you feel like throwing the parsing code over the wall to me, I will add
it to mondrian. Maybe add a couple of unit tests too. "The tests are the
specificiation" and all that good stuff.
 
Julian


  _____  

From: Benny Chow [mailto:bchow at pentaho.com] 
Sent: Monday, June 14, 2010 7:41 AM
To: Julian Hyde
Cc: mondrian at pentaho.org
Subject: RE: Conditional Formatting



I looked at the JPivot source code and indeed it looks for a leading "|".
After that, JPivot splits whatever Mondrian returns from
cell.getFormattedValue using "|" as the separator.  JPivot then applies a
regular expression to find name value pairs separated by a "=".  The name
part corresponds to "style", "link", "arrow" or "image" as described here:
http://jpivot.sourceforge.net/news.html -> Member Properties

 

I'm going to keep the same standard for Analyzer since it means people can
re-use their JPivot mondrian schemas with Analyzer and keep their cell
formatting.

 

Benny

 


  _____  


From: Julian Hyde [mailto:jhyde at pentaho.com] 
Sent: Sunday, June 13, 2010 12:04 AM
To: Benny Chow
Cc: mondrian at pentaho.org
Subject: RE: Conditional Formatting

 

Yes, there is a semi-standard way of formatting values to include style
information. I believe that it started with Micrsoft OLAP Services, and for
JPivot we adopted the same standard. (Although now when I search, most of
the examples using this are Mondrian-related.)

 

I believe that the leading '|' is the clue that style information is
present. But the standard is essentially arbitrary, and I don't know whats
supposed to happen if the formatted value actually starts with a '|'.

 

You could start a new standard. It's definitely possible to return the style
in a different calc member. As long as you document what analyzer is
expecting.

 

Julian

 

 


  _____  


From: Benny Chow [mailto:bchow at pentaho.com] 
Sent: Saturday, June 12, 2010 7:41 AM
To: Julian Hyde
Subject: Conditional Formatting

Hi Julian,

 

I'm looking into some conditional formatting for Analyzer and I tried out
your suggestion in this forum post you made:

 

http://forums.pentaho.org/showthread.php?t=47222

 

 

<CalculatedMember 
name="Profit" 
dimension="Measures" 
formula="[Measures].[Store Sales] - [Measures].[Store Cost]"> 
<CalculatedMemberProperty name="FORMAT_STRING" expression=" 
Iif([Measures].[Profit] < 100000, '|$#,##0.00|style=green',
'|$#,##0.00|style=red')"/> 
</CalculatedMember>

 

 

For the cell that has this member as its context, suppose Profit is 100.
Will cell.getFormattedValue return $100.00 or |$100|style=green ?  I am
currently getting the ladder.  If the ladder is correct, is JPivot doing
some post processing by parsing the formatted cell value and then showing
the $100 in HTML and then applying the other stuff like style=green as a CSS
attribute?  

 

Maybe there is some special handshake that's going on between Mondrian cell
formatting and JPivot that makes the conditional formatting work.  

 

Any insight would be much appreciated.

 

Thanks

Benny

 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100614/bd20fb98/attachment.html