Hi Will,<div>I was following this thread with great interest. I'd appreciate if you could share your solution on how to<span class="Apple-style-span" style="font-family: arial, sans-serif; font-size: 13px; border-collapse: collapse; "> create a dynamic set of columns for your report in PDR.</span></div>
<div><font class="Apple-style-span" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;"><br></span></font></div><div><font class="Apple-style-span" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;">I also added your example to my PRD parameter guide/reference (</span></font><a href="http://diethardsteiner.blogspot.com/2009/11/using-parameters-in-pentaho-report.html">http://diethardsteiner.blogspot.com/2009/11/using-parameters-in-pentaho-report.html</a>) as I think it will be useful for other users too (I hope this is fine with you).</div>
<div><br></div><div>Best regards,</div><div>Diethard</div><div><font class="Apple-style-span" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse; "><br></span></font><br><div class="gmail_quote">
On Wed, Jul 21, 2010 at 11:57 PM, <span dir="ltr"><<a href="mailto:waw@1783.com">waw@1783.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Thank you. That is what I needed!<br>
<br>
I have changed my query to:<br>
<br>
WITH SET [selectedset] AS ${CSVClient}<br>
select [selectedset] ON COLUMNS,<br>
<div class="im"> {[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<br>
from [Monthly Influenza Summary]<br>
</div>where ([Monthly Period].[${SelectedYear}])<br>
<br>
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.<br>
<br>
<br>
Bill W.<br>
<div><div></div><div class="h5">-----Original Message-----<br>
From: Thomas Morgner [mailto:<a href="mailto:mondrian@sherito.org">mondrian@sherito.org</a>]<br>
Sent: Wednesday, July 21, 2010 05:53 AM<br>
To: 'Mondrian developer mailing list'<br>
Subject: Re: [Mondrian] Parameterized Mondrian Queries in PRD<br>
<br>
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 <a href="http://download.oracle.com/docs/cd/E17476_01/javase/1.4.2/docs/api/java/text/MessageFormat.html" target="_blank">http://download.oracle.com/docs/cd/E17476_01/javase/1.4.2/docs/api/java/text/MessageFormat.html</a> On 20.07.10 15:18, <a href="mailto:waw@1783.com">waw@1783.com</a> 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 > <a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> > <a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a> _______________________________________________ Mondrian mailing list <a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
<br>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
</div></div></blockquote></div><br></div>