[Mondrian] Parameterized Mondrian Queries in PRD

Diethard Steiner diethard.steiner at gmail.com
Thu Jul 22 09:27:42 EDT 2010


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100722/c628c7bf/attachment.html 


More information about the Mondrian mailing list