[Mondrian] RE: Conditional Formatting (Diethard Steiner)

Diethard Steiner diethard.steiner at gmail.com
Wed Aug 25 06:25:19 EDT 2010


Hi Benny and Julian,
Many thanks for your help!
I realized by looking at Benny's code that I shouldn't use double quotation
marks in the expression, so I changed my expression to:
Iif(([Measures].[New Subscriptions (Actual VS Budget)]<
-0.01),'|#0.00%|style=red|arrow=down',Iif(([Measures].[New Subscriptions
(Actual VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=green|arrow=up'))

I also downloaded the latest version of Schema Workbench which has support
for Calculated Member Property.
Everything is working fine now!

Thanks a lot,
Diddy




On Wed, Aug 25, 2010 at 2:28 AM, Julian Hyde <jhyde at pentaho.com> wrote:

> I agree, CalculatedMemberProperty is a more convenient way to specify it.
>
> But contrary to what Benny says, the format string is interpreted as an MDX
> expression. (Of course, that expression can be string literal if you happen
> to want the same format string for all cells.)
>
> Julian
>
>
> > -----Original Message-----
> > From: mondrian-bounces at pentaho.org
> > [mailto:mondrian-bounces at pentaho.org] On Behalf Of Benny Chow
> > Sent: Tuesday, August 24, 2010 6:19 PM
> > To: mondrian at pentaho.org
> > Subject: [Mondrian] RE: Conditional Formatting (Diethard Steiner)
> >
> >
> >   <CalculatedMember name="Arrow Unit Sales" dimension="Measures">
> >     <Formula>[Measures].[Unit Sales]</Formula>
> >     <CalculatedMemberProperty name="FORMAT_STRING"
> > expression="IIf([Measures].[Unit Sales] >
> > 10000,'|#,###|arrow=up',IIf([Measures].[Unit Sales] >
> > 5000,'|#,###|arrow=down','|#,###|arrow=none'))" />
> >   </CalculatedMember>
> >
> > Since you are using an MDX expression to do conditional
> > formatting, you should specify the expression via a member
> > property as shown above.  I think the formatString is treated
> > as a string literal and not actually evaluated as an MDX expression.
> >
> > Benny
> >
> > -----Original Message-----
> > From: mondrian-bounces at pentaho.org
> > [mailto:mondrian-bounces at pentaho.org] On Behalf Of
> > mondrian-request at pentaho.org
> > Sent: Wednesday, August 25, 2010 1:00 AM
> > To: mondrian at pentaho.org
> > Subject: Mondrian Digest, Vol 45, Issue 14
> >
> > Send Mondrian mailing list submissions to
> >       mondrian at pentaho.org
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> >       http://lists.pentaho.org/mailman/listinfo/mondrian
> > or, via email, send a message with subject or body 'help' to
> >       mondrian-request at pentaho.org
> >
> > You can reach the person managing the list at
> >       mondrian-owner at pentaho.org
> >
> > When replying, please edit your Subject line so it is more specific
> > than "Re: Contents of Mondrian digest..."
> >
> >
> > Today's Topics:
> >
> >    1. Re: Mondrian Performance Test Harness (Nicholas Goodman)
> >    2. Re: -----SPAM----- Mondrian + Hadoop Hive Research
> > (Calum Miller)
> >    3. Conditional Formatting (Diethard Steiner)
> >
> >
> > ----------------------------------------------------------------------
> >
> > Message: 1
> > Date: Mon, 23 Aug 2010 12:59:57 -0700
> > From: Nicholas Goodman <ngoodman at bayontechnologies.com>
> > Subject: Re: [Mondrian] Mondrian Performance Test Harness
> > To: Mondrian developer mailing list <mondrian at pentaho.org>
> > Message-ID:
> >       <8B758210-9B0F-472A-BD95-D136A4794BEA at bayontechnologies.com>
> > Content-Type: text/plain; charset="windows-1252"
> >
> >
> > On Aug 23, 2010, at 12:37 PM,
> > <jeff.s.wright at thomsonreuters.com> wrote:
> >
> > > Good references, I’ve seen those. SSB is a snowflake model
> > with a single fact table. I’d prefer to be able to test
> > Virtual cubes. I came down on the side of TPC-DS because it
> > seemed more meaty as a data model and there was code to
> > support it, even if it wasn’t finished or actively used as a
> > TPC benchmark.
> > SSB is based on a modified TPC-H benchmark.  In general, it
> > generates the TPC-H data denormalized.  I'd think that the
> > SSB could easily be extended to make a separate Orders fact
> > (a level above line items) that should zero balance with
> > lineitems but allow for virtual cube testing thru conformed
> > dimensions.
> >
> > >  >A scalable database generator is essential
> > >
> > > Unfortunately both SSB and TPC-DS use data generators
> > written in C, but we may be able to take on porting that to
> > Java as scope for this semester or a follow on project.
> > I vaguely recall, but just googled and couldn't find a java
> > based version of TPCH from the OSU OSL lab @ sourceforge.
> > But, can't find anything on any search so I might be losing
> > some marbles.
> >
> > LucidDB (and firewater) would LOVE to have a java based
> > version of the SSB generator!  In particular, we'd love to be
> > able to access the generation via straight java because that
> > could allow us to wrap it in Java and load directly from Java
> > into LucidDB without generating/persisting intermediate huge files.
> >
> > If you're genuinely interested in this, contact me and we can
> > discuss if we can also add some guidance/help for your team.
> > -------------- next part --------------
> > An HTML attachment was scrubbed...
> > URL:
> > http://lists.pentaho.org/pipermail/mondrian/attachments/201008
> 23/a3c14a82/attachment-0001.html
>
> ------------------------------
>
> Message: 2
> Date: Mon, 23 Aug 2010 22:23:13 +0100
> From: Calum Miller <calum at millersoft.ltd.uk>
> Subject: [Mondrian] Re: -----SPAM----- Mondrian + Hadoop Hive Research
> To: Jordan Ganoff <jganoff at pentaho.com>
> Cc: "mondrian at pentaho.org" <mondrian at pentaho.org>
> Message-ID: <1F7DDBAF-BB1D-49DE-BFBC-9BA28F2AD725 at millersoft.ltd.uk>
> Content-Type: text/plain; charset="utf-8"
>
> Hi Jordan,
>
> Many thanks for the feedback, which confirms my suspicions that a Hive
> Dialect for Mondrian is at least possible. I'm going to start by getting
> foodmart into hadoop to support the Mondrian test  suite via hive then
> progress onto the necessary changes to the Sql join statement.  I'll post
> updates to the specified jira.
>
> Calum
>
>
> On 23 Aug 2010, at 16:30, Jordan Ganoff <jganoff at pentaho.com> wrote:
>
> > Calum,
> >
> > The PDI team recently did some research for using Apache's Hive database
> with Mondrian.  You tweeted "Thinking of adding Hive dialect to Mondrian,
> too slow for add-hoc queries but would enable mdx reports against hadoop" on
> 11:21 AM Aug 22nd. This is an effort to give you a bit of heads up to some
> of the issues you'll encounter and the JIRA case with patches to Mondrian to
> get Hive working at a remedial level.  Research done by James Dixon, Sean
> Flatley and myself found the following issues when integrating Hive with
> Mondrian 3.1.6.13364.  We used our custom built Hive JDBC Driver[1] for
> testing:
> >
> > - Prepared Statements are mostly unimplemented in the JDBC driver from
> Apache in versions 0.5.0 and trunk (0.7.0).  We are working on implementing
> more functionality as needed to get Report Designer and Metadata Editor
> working well.  Our changes are being contributed back to the official Hive
> project but until they are accepted we're building our own version[1][2].
> >
> > - Join syntax generated by Mondrian is not compatible with Hive.  In
> short, Hive does not support multiple items in the from clause.[2]
> >
> > See the related JIRA case with attached patches to get Mondrian
> 3.1.6.13364 to work with Hive and associated test results:
> http://jira.pentaho.com/browse/PDI-4355
> >
> > [1]:
> http://ci.pentaho.com/view/Data%20Integration/job/apache-hive-0.5.0/
> > [2]: http://forums.pentaho.com/showthread.php?77826-Hive-amp-Hadoop
> > [3]: http://wiki.apache.org/hadoop/Hive/LanguageManual/Joins
> >
> > Hope this helps,
> >
> > Jordan Ganoff
> > Software Engineer
> > <pentaho-email-logo.gif>
> > Pentaho: The Commercial Open Source Alternative for Business Intelligence
> > 5950 Hazeltine National Drive, Suite 340 • Orlando, FL 32822, USA
> > +1 407 812-OPEN (6736) • 407 517-6206 • 321 848-8207
> > Get your free download today at http://www.pentaho.com.
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.pentaho.org/pipermail/mondrian/attachments/20100823/931035df/attachment-0001.html
>
> ------------------------------
>
> Message: 3
> Date: Tue, 24 Aug 2010 11:28:59 +0100
> From: Diethard Steiner <diethard.steiner at gmail.com>
> Subject: [Mondrian] Conditional Formatting
> To: mondrian at pentaho.org
> Message-ID:
>        <AANLkTim5Tu9D2bbrKHYMEJqNibZNOEj-ihH1E-7whDF9 at mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hi,
> I created some calculated members and want to apply conditional formatting.
> To test my formatting, I wrote an MDX query and created a member with the
> formatting string and all worked fine.
> In Schema Workbench, I apply the formatString to the calculated member ...
> and then when I check the results in JPivot online, I can see the
> formatString instead of the values (but period signs etc is missing). I
> opened the XML schema file in an editor and saw that workbench URL encodes
> the formatstring.
>
> If I just use a simple format like #0.00% all works fine. So I am wondering
> as to why the iif statement is not working. What exactly am I doing wrong?
>
> Here the code:
> Iif(([Measures].[Index Revenues]<
> -0.01),"|#0.00%|style='red'|arrow='down'",Iif(([Measures].[Index Revenues]<
>
> 0.01),"|#0.00%|style='yellow'|arrow='blank'","|#0.00%|style='green'|arrow='up'"))
>
> Looks like this in the Schema:
> <CalculatedMember name="Index Revenues"
> formatString="Iif(([Measures].[Index
> Revenues]&#60;
>
> -0.01),&#34;|#0.00%|style=&#39;red&#39;|arrow=&#39;down&#39;&#34;,Iif(([Measures].[Index
> Revenues]&#60;
>
> 0.01),&#34;|#0.00%|style=&#39;yellow&#39;|arrow=&#39;blank&#39;&#34;,&#34;|#0.00%|style=&#39;green&#39;|arrow=&#39;up&#39;&#34;))"
> formula="IIF([Date.Weekly Calendar].CurrentMember Is
>
> Null,Null,([Measures].[act_revenues]-[Measures].[bdgt_revenues])/(Iif([Measures].[bdgt_revenues]&#60;0,[Measures].[bdgt_revenues]*-1,[Measures].[bdgt_revenues])))"
> dimension="Measures" visible="true">
> </CalculatedMember>
>
> Thanks,
> Diddy
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.pentaho.org/pipermail/mondrian/attachments/20100824/610f6e5d/attachment-0001.html
>
> ------------------------------
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> End of Mondrian Digest, Vol 45, Issue 14
> ****************************************
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100825/99e202be/attachment.html 


More information about the Mondrian mailing list