[Mondrian] RE: Conditional Formatting

Julian Hyde jhyde at pentaho.com
Thu Jun 17 18:42:31 EDT 2010


It's useful to have it available open source so that other clients can use
it; and since the rules are not-quite-trivial, they can clarify them and
improve the implementation. On reflection I think that olap4j is a better
place for it than mondrian.
 
I have added a method Olap4jUtil.parseFormattedCellValue. (Remember that the
org.olap4j.impl package is a 'helpful but experimental' package not
considered part of the official olap4j API.)
 
http://olap4j.svn.sourceforge.net/viewvc/olap4j?view=revision
<http://olap4j.svn.sourceforge.net/viewvc/olap4j?view=revision&revision=320>
&revision=320
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Benny Chow
Sent: Monday, June 14, 2010 9:25 PM
To: Julian Hyde
Cc: mondrian at pentaho.org
Subject: [Mondrian] RE: Conditional Formatting



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/20100617/ddc69079/attachment.html 


More information about the Mondrian mailing list