PDA

View Full Version : [Mondrian] VBA functions / IIF



Matt Campbell
02-09-2007, 12:30 PM
The client tool we're using (Cognos) issues some vba functions in its MDX
occasionally: INSTR, MID, LEN, etc. We've been considering how best to
deal with it. We could just create some UDFs that mimic what the vba
function does, but we could also create them as builtin Mondrian functions.
Does anyone have an opinion?

We also plan on overriding IIF to support some additional parameter types.
Right now IIF supports iif( <logical expression>, <string>,<string>) and
iif(<logical expression>,<number>,<number>). We'd like to add
iif(exp,value, str), iif(exp,str, value), iif(exp,value, value). All of
these permutations are supported by AS2K.

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Julian Hyde
02-12-2007, 09:40 PM
Matt,

Can you give me a list of functions you need? Already on my list (see
http://mondrian.pentaho.org/documentation/roadmap.html#Feature_Standard_func
tions) are:

*

Cint(String) returns Number *
*

CStr(Number) returns String *
*

Power(Number, Number) returns Number
*

Log(Number) returns Number
*

Sqrt(Number) returns Number
*

Substring(String, Integer) returns String
*


Substring(String, Integer, Integer) returns String
*

Trim

* CInt and CStr are subsumed by the Cast operator which I implemented
recently, but will help people to run MDX queries unchanged.

See further comments inline.

Julian


_____

From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
Behalf Of Matt Campbell
Sent: Friday, February 09, 2007 8:26 AM
To: Mondrian developer mailing list
Subject: [Mondrian] VBA functions / IIF




The client tool we're using (Cognos) issues some vba functions in its MDX
occasionally: INSTR, MID, LEN, etc. We've been considering how best to
deal with it. We could just create some UDFs that mimic what the vba
function does, but we could also create them as builtin Mondrian functions.
Does anyone have an opinion?

I'd prefer that functions like this are implemented as UDFs.
mondrian.udf.ValUdf is an example (implemented by Gang Chen) of a VB
function implemented in this way.

Even though they are implemented as UDFs, these functions would be available
out-of-the-box. Include them in
src/main/META-INF/services/mondrian.spi.UserDefinedFunction and they will be
picked up automatically.

As usual, we need a handful of testcases in FunctionTest for each function.



We also plan on overriding IIF to support some additional parameter types.
Right now IIF supports iif( <logical expression>, <string>,<string>) and
iif(<logical expression>,<number>,<number>). We'd like to add
iif(exp,value, str), iif(exp,str, value), iif(exp,value, value). All of
these permutations are supported by AS2K.


You're welcome to add other overloads to Iif. Iif is currently a builtin
function, and it should stay that way.

Julian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Matt Campbell
02-13-2007, 11:31 AM
The functions we've identified as required are:

LEFT
LEN
UCASE
INSTR
mid

Since we're implementing MID and LEFT I expect we'd also handle Substring
while we're at it, since they provide the same functionality.


On 2/12/07, Julian Hyde <julianhyde (AT) speakeasy (DOT) net> wrote:
>
> Matt,
>
> Can you give me a list of functions you need? Already on my list (see
> http://mondrian.pentaho.org/documentation/roadmap.html#Feature_Standard_functions)
> are:
>
> - Cint(String) returns Number *
> - CStr(Number) returns String *
> - Power(Number, Number) returns Number
> - Log(Number) returns Number
> - Sqrt(Number) returns Number
> - Substring(String, Integer) returns String
> - Substring(String, Integer, Integer) returns String
> - Trim
>
> * CInt and CStr are subsumed by the Cast operator which I implemented
> recently, but will help people to run MDX queries unchanged.
>
> See further comments inline.
>
> Julian
>
> ------------------------------
> *From:* mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
> *On Behalf Of *Matt Campbell
> *Sent:* Friday, February 09, 2007 8:26 AM
> *To:* Mondrian developer mailing list
> *Subject:* [Mondrian] VBA functions / IIF
>
>
> The client tool we're using (Cognos) issues some vba functions in its MDX
> occasionally: INSTR, MID, LEN, etc. We've been considering how best to
> deal with it. We could just create some UDFs that mimic what the vba
> function does, but we could also create them as builtin Mondrian functions.
> Does anyone have an opinion?
>
> I'd prefer that functions like this are implemented as UDFs.
> mondrian.udf.ValUdf is an example (implemented by Gang Chen) of a VB
> function implemented in this way.
>
> Even though they are implemented as UDFs, these functions would be
> available out-of-the-box. Include them in
> src/main/META-INF/services/mondrian.spi.UserDefinedFunction and they will be
> picked up automatically.
>
> As usual, we need a handful of testcases in FunctionTest for each
> function.
>
>
>
> We also plan on overriding IIF to support some additional parameter
> types. Right now IIF supports iif( <logical expression>, <string>,<string>)
> and iif(<logical expression>,<number>,<number>). We'd like to add
> iif(exp,value, str), iif(exp,str, value), iif(exp,value, value). All of
> these permutations are supported by AS2K.
>
>
> You're welcome to add other overloads to Iif. Iif is currently a builtin
> function, and it should stay that way.
>
> Julian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian