[Mondrian] Parameterized Mondrian Queries in PRD

waw at 1783.com waw at 1783.com
Thu Jul 22 12:14:31 EDT 2010


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.html On 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.orghttp://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/d5f0cfab/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: TestSampleData.prpt
Type: application/octet-stream
Size: 5369 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100722/d5f0cfab/attachment.obj 


More information about the Mondrian mailing list