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