[Mondrian] Parameterized Mondrian Queries in PRD

Diethard Steiner diethard.steiner at gmail.com
Fri Jul 23 06:02:01 EDT 2010


Hi Bill,
Thanks a lot! I had a look at it and adjusted it a bit as I have all my data
on MySQL. Well done so far. The part that I was actually interested in was
on how to hide the empty columns.

I experimented a bit with your sample and tried to work with the visibility
=NOT(ISEMPTYDATA()) on the data fields. The problem is that field still
takes up space if it is invisible.

My next step:
I set the layout of the details band to "row" and deleted all x and y values
of all fields. I thought that that the fields would be automatically left
aligned if one has a height and width of 0. I used the formula
=IF(ISEMPTYDATA();0;63) to set the width and a similar one for the height.
But this didn't work either.

So, I haven't found an easy way yet (and I really have to focus on some
other work now). If you come up with a good solution, please share it.

Thanks,
Diethard






On Thu, Jul 22, 2010 at 5:14 PM, <waw at 1783.com> wrote:

> Ok, see attached. This is more about PRD than Mondrian, but since I started
> in this group and others were interested, I am sending my initial solution.
>
> A couple of notes:
> 1) A query is used to get a list of distinct product lines:
> ListProductLines.
> select '[Product].[All Products].['||productline||']' as productmember
> from products
> group by productmember
>
> 2) A parameter multi-select is set up for pulling the query values:
> pProductLine
> The user can select one or more values to display
>
> 3) A final parameter variable is set for turning the selected values into
> an MDX query component: pProductArray
> The trick is the formula - ="{" & CSVTEXT([pProductLine];false(); ",") &
> "}"
>
> This formula comma separates the values and adds the curly braces required
> for MDX. This can probably be done in a single parameter, but I split it for
> illustration purposes.
>
> That's it. The next step is to provide a dynamic set of columns on the
> report itself. I am testing with indexed array variables in PRD. This may
> require some javascripting so that I can assure array members and not get
> outside the index value.
>
> This is nothing fancy at this time. I just needed to get the basics so that
> I can move onto the next portion, but it does serve as a very basic example
> of controlled ad hoc reporting with MDX.
>
> BR/Bill W.
>
> -----Original Message-----
> *From:* Diethard Steiner [mailto:diethard.steiner at gmail.com]
> *Sent:* Thursday, July 22, 2010 09:27 AM
> *To:* 'Mondrian developer mailing list'
> *Subject:* Re: [Mondrian] Parameterized Mondrian Queries in PRD
>
> Thanks a lot Bill! Much appreciated.
> Best regards,
> Diethard
>
> On Thu, Jul 22, 2010 at 12:46 PM, Bill Wimsatt <waw at 1783.com> wrote:
>
>> I sure will. I will create a version that uses the SampleData so that
>> anyone can follow it.
>>
>>
>>   Best Regards
>> Bill Wimsatt
>> 1783 Productions, LLC
>> 720 318 5550 (c)
>>
>>
>>
>>
>>
>>   On Jul 22, 2010, at 2:26 AM, Diethard Steiner wrote:
>>
>>   Hi Will,
>> I was following this thread with great interest. I'd appreciate if you
>> could share your solution on how to create a dynamic set of columns for
>> your report in PDR.
>>
>> I also added your example to my PRD parameter guide/reference (
>> http://diethardsteiner.blogspot.com/2009/11/using-parameters-in-pentaho-report.html)
>> as I think it will be useful for other users too (I hope this is fine with
>> you).
>>
>> Best regards,
>> Diethard
>>
>>
>> On Wed, Jul 21, 2010 at 11:57 PM, <waw at 1783.com> wrote:
>>
>>> Thank you. That is what I needed!
>>>
>>> I have changed my query to:
>>>
>>> WITH SET [selectedset] AS ${CSVClient}
>>> select [selectedset] ON COLUMNS,
>>>  {[Measures].[Count of Employees], [Measures].[Count of Declined],
>>> [Measures].[Count No Record], [Measures].[Count Requested But No Vaccination
>>> Record], [Measures].[Count Vaccinated], [Measures].[Count Vaccinated
>>> Elsewhere], [Measures].[% Vaccinated], [Measures].[% Declined],
>>> [Measures].[% No Record]} ON ROWS
>>> from [Monthly Influenza Summary]
>>> where ([Monthly Period].[${SelectedYear}])
>>>
>>> I am using PRD parameters to get the set of customers in a multi-select,
>>> then I turn that set into a formatted array which I use in the query. The
>>> basics are there now. Now, I need to get PRD to create a dynamic set of
>>> columns for my report. I have seen someone using tables as a parameter, so I
>>> will get that part figured out.
>>>
>>>
>>> Bill W.
>>>  -----Original Message-----
>>> From: Thomas Morgner [mailto:mondrian at sherito.org]
>>> Sent: Wednesday, July 21, 2010 05:53 AM
>>> To: 'Mondrian developer mailing list'
>>> Subject: Re: [Mondrian] Parameterized Mondrian Queries in PRD
>>>
>>> String parameters are passed into Mondrian without any postprocessing by
>>> the reporting engine (if the parameter is a java.lang.String, otherwise we
>>> will throw a very visible exception). You are using a StrToMember which
>>> probably does not like the "{..}" syntax of the sets. Try a StrToSet
>>> instead, if you intend to pass a set. You can also skip the parameter
>>> function and use the classical ${...} parametrization syntax (PRD 3.6 or
>>> newer), which performs a simple replacement on the MDX string. To prevent
>>> code injections you can specify a subformat along with those parameters:
>>> ${param} will include the parameter without any modification.
>>> ${param,string} will quote the parameter value as MDX string (slap double
>>> quotes around it and escape all inner double quote chars)
>>> ${param,formattype,formatstyle} will treat the parameter as a message format
>>> request, so that you can format numbers, dates and so on properly. For the
>>> grammar of formattype and formatstyle have a look at
>>> http://download.oracle.com/docs/cd/E17476_01/javase/1.4.2/docs/api/java/text/MessageFormat.htmlOn 20.07.10 15:18,
>>> waw at 1783.com wrote: > After looking through Julian's Jira note and
>>> looking through MDX notes a > bit more, I am wondering if the Parameter
>>> function is working in PRD. I > have created a parameter query that creates
>>> a member string : {[Client > Location].[Clients].[CLIENT1]}. The string is
>>> part of the PRD MDX Query > like the following > select
>>> strToMember(Parameter("SetofClients", STRING, "[Client >
>>> Location].[Clients].[All Clients]"))) ON COLUMNS, >
>>> Crossjoin(Hierarchize({[Monthly Period].[2010]}), {[Measures].[Count of >
>>> Employees], [Measures].[Count of Declined], [Measures].[Count No > Record],
>>> [Measures].[Count Requested But No Vaccination Record], > [Measures].[Count
>>> Vaccinated], [Measures].[Count Vaccinated Elsewhere], > [Measures].[%
>>> Vaccinated], [Measures].[% Declined], [Measures].[% No > Record]}) ON ROWS >
>>> from [Monthly Influenza Summary] > The query works fine when not
>>> parameterized. So, looking at Jira and the > forums, I am wondering if I am
>>> chasing ghosts. The Advanced OLAP option > in PRD is not well documented, so
>>> I am not sure how to build a > completely dynamic MDX query in PRD. Any
>>> thoughts or direction is > greatly appreciated. > BR/Bill W. > > > >
>>> _______________________________________________ > 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
>>>
>>
>> _______________________________________________
>>
>> 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/20100723/f45fc667/attachment.html 


More information about the Mondrian mailing list