# [Mondrian] Using parameters in Pentaho Report Designer 3.5 -- issue with formulas

Diethard Steiner diethard.steiner at gmail.com
Wed Nov 4 06:40:01 EST 2009

```As Thomas pointed out, passing parameters for a hierarchy like
All].[Year].[Quarter].[Month].[Day] isn't very easy, but now it is finally
possible.
All].[2009].[Q3].[Jul].[2]

I had now a look at the latest build of PRD and I spend quite a long time
writing a formula that would prepare the above hierarchy from a date picker
input. The formula looks like:

=("[Login Date.Monthly Calendar].[Monthly Calendar All].[" & YEAR([my_date])
& "].[" & "Q" & IF(MOD(MONTH([my_date]);3 )=0; MONTH([my_date])/3;
INT(MONTH([my_date])/3)+1) & "].[" &
IF(MONTH([my_date])=1;"Jan";IF(MONTH([my_date])=2;"Feb";IF(MONTH([my_date])=3;"Mar";IF(MONTH([my_date])=4;"Apr";IF(MONTH([my_date])=5;"May";IF(MONTH([my_date])=6;"Jun";IF(MONTH([my_date])=7;"Jul";IF(MONTH([my_date])=8;"Aug";IF(MONTH([my_date])=9;"Sep";IF(MONTH([my_date])=10;"Oct";IF(MONTH([my_date])=11;"Nov";"Dec")))))))))))
& "].[" & DAY([my_date]) & "]")

While it is a huge advantage now that his works in PRD, I am a bit
wondering, if this is really that user friendly. Anyhow, I am happy for now
that I got this working and that I can finally use parameters with more
complex MDX queries.

I'll update my documentation now and publish it later on.

Best regards,
Diddy

On Wed, Nov 4, 2009 at 11:09 AM, Thomas Morgner <mondrian at sherito.org>wrote:

> For OLAP4J, we rely on the OLAP4J driver to provide the
> parameter functionality. This works great for Mondrian
> connections, but does not work at all for XML/A connections,
> as the "parameter" function is a Mondrian specific extension.
>
> There seem to be no "global" parameter concept, like JDBC
> introduced. I would already be happy if we can declare
> that the "Parameter" function is a part of the OLAP4J
> core. Drivers for sources with no native parameter-function
> would then be able to parse the MDX query and to do the
> parametrization on the client side.
>
> This would free me, a poor application programmer, to have
> special cases for Mondrian and non-Mondrian parametrization.
>
>
> And secondly:
> To me, he Formula-post-processing is just a patchy solution
> to non-user-friendly parameters in Mondrian.
>
> There is the common use-case that you have a single Date as input
> and want to map it into a hierarchy, on a query like this
>
> "Select [Time].[year].[qtr].[month].Children on 0"
>
> Replace "year", "month" and "day" with real values and
> it works. But I'm unable to trick Mondrian into accepting
> three parameters here.
>
>
> And so far, I cannot even remotely see a way to feed this
> case from a single date-parameter.
>
> Any light you (or any other MDX pro) can shed on this case
> would be great. Having some more documentation on the
> parametrization of MDX queries would be nice too.
>
>
> Pedro Alves schrieb:
>
>
>> It's already fixed in CI. Thomas implemented post-execution formulas that
>> can be used exactly for this kind of stuff.
>>
>> (that being said, no harm done in having more resources here)
>>
>>
>> -pedro
>>
>>
>> On 09/11/04 08:22, Julian Hyde wrote:
>>
>>> Thomas,
>>> Did you see this blog post? And the 'Open' issue that you can't evaluate
>>> a parameter?
>>>
>>> http://diethardsteiner.blogspot.com/2009/11/using-parameters-in-pentaho-report.html
>>> What would you need from olap4j to fix this? Would it help if there were
>>> methods to get parameters as well as set them? E.g. a new method
>>> OlapPreparedStatement.getObject(int) as a converse to
>>> PreparedStatement.setOject(int, Object).
>>> If the parameter was not set, then its default value would need to be
>>> executed.
>>> Feel free to log a feature request if something is missing from mondrian
>>> or olap4j.
>>> Julian
>>>
>>>
>>>
>>> _______________________________________________
>>> 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
>>
>>
> _______________________________________________
> 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/20091104/5ca15951/attachment.html
```