<div class="gmail_quote"><div><div><div><font face="Arial" size="2"><div>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-gb"><font face="Arial" size="2">I</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">m working with PRD and mondrian.  I have the following MDX query</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> which takes a single parameter</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">“</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">CalcMDXDateStr</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">”</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> provided by a DatePicker -&gt; OpenFormula combo in PRD.</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span style="font-family: courier new,monospace;" lang="en-gb"><font size="2">select </font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  CROSSJOIN( </font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">      { strToMember( Parameter(&quot;CalcMDXDateStr&quot;, STRING, &quot;[Date].[All Years].[2009].[Q4].[NOV].[4]&quot; )) },</font></span></p>


<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth], [Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD], [Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }</font></span></p>


<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  ) ON COLUMNS,</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  NON EMPTY [Area].[All Areas].Children ON ROWS</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">from [Enquiries]</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">where (</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">    [Activity.Activity Type].[All Types].[Created]</font></span></p>

<p dir="ltr"><span style="font-family: courier new,monospace;" lang="en-gb"><font size="2">)</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">This query returns 69 rows with the following column headers:</font></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Area].[(All)]</font></span></p>

<p dir="ltr"><span lang="en-gb"><font face="Arial" size="2">[Area].[Desc]</font></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs]</font></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">LastMonth</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">ThisMonthLastYear</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">YTD</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">YTD</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">LastYear</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">LastYear</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">Now these column headers are horrible to work with so I wanted to replace the</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> data parameter</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> with a</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">named member, i.e.</font></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span style="font-family: courier new,monospace;" lang="en-gb"><font size="2">with </font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  member [Date].[DateParam] as &#39;strToMember( Parameter(&quot;CalcMDXDateStr&quot;, STRING, &quot;[Date].[All Years].[2009].[Q4].[NOV].[4]&quot; ))&#39;</font></span></p>


<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">select </font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  CROSSJOIN( </font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">      { [Date].[DateParam] },</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth], [Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD], [Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }</font></span></p>


<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  ) ON COLUMNS,</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">  NON EMPTY [Area].[All Areas].Children ON ROWS</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">from [Enquiries]</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">where (</font></span></p>

<p style="font-family: courier new,monospace;" dir="ltr"><span lang="en-gb"><font size="2">    [Activity.Activity Type].[All Types].[Created]</font></span></p>

<p dir="ltr"><span style="font-family: courier new,monospace;" lang="en-gb"><font size="2">)</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">But this query only returns</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">9</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> rows</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">, and</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">all measure</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">values are zero apart from column</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"> <font face="Arial" size="2">[Measures].[NumProjs]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">.  The column headers are lovely though, e.g.</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2"> [Date].[DataParm]/[Measures].[NumProjs]</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-gb"><font face="Arial" size="2">I</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">m confused.  I</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">ve replaced a Member with a Member</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">?  Is this a bug?</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>


<p dir="ltr"><span lang="en-gb"><font face="Arial" size="2">I</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">ve converting it to a set with the following MDX query, but then the same horrible column headers are presented in PRD.</font></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span style="font-family: comic sans ms,sans-serif;" lang="en-gb"><font size="2">with </font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">  set [DateParam] as &#39;{ strToMember( Parameter(&quot;CalcMDXDateStr&quot;, STRING, &quot;[Date].[All Years].[2009].[Q4].[NOV].[4]&quot; )) }&#39;</font></span></p>


<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">select </font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">  CROSSJOIN( </font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">      [DateParam],</font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth], [Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD], [Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }</font></span></p>


<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">  ) ON COLUMNS,</font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">  NON EMPTY [Area].[All Areas].Children ON ROWS</font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">from [Enquiries]</font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">where (</font></span></p>

<p style="font-family: comic sans ms,sans-serif;" dir="ltr"><span lang="en-gb"><font size="2">    [Activity.Activity Type].[All Types].[Created]</font></span></p>

<p dir="ltr"><span style="font-family: comic sans ms,sans-serif;" lang="en-gb"><font size="2">)</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">I</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">m confused!!</font></span></p>


<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">Phil</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

<p dir="ltr"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span></p>

</div>
<p style="font-size: 10pt; font-family: Arial;">Phillip Cole | Business Systems Analyst | Colt International Limited | New Lane | Havant | Hants PO9 2LY | Tel: +44 23 9249 1308 | Mobile: +44 7767 230366 | Fax: +44 23 9248 5346 | Mail: <a href="mailto:phillip.cole@uk.coltgroup.com" target="_blank">phillip.cole@uk.coltgroup.com</a> | Web: <a href="http://www.coltinfo.co.uk" target="_blank">www.coltinfo.co.uk</a></p>

<div><br></div></font><font size="2"><font face="Arial">Colt has received LABC accreditation for the Colt Shaft System, the Colt Cyclone and the Colt Jetstream Car Park Ventilation fans.<br>For further information on Colt and LABC, please click <a href="http://www.coltinfo.co.uk/news/news_LABC.html" target="_blank">here</a>.</font></font></div>

<div><font face="Arial" size="2"><br></font></div></div>
<p style="font-size: 10pt; font-family: Arial;">For Colt&#39;s email confidentiality and monitoring policy, usage restrictions, or for company registration and regulatory status information, please visit <a href="http://www.coltinfo.co.uk/legal-notice.html" target="_blank">http://www.coltinfo.co.uk/legal-notice.html</a></p>

<div><br></div>
</div>





<span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"><font face="Arial" size="2">Hi,</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-gb"></span>
</div><br><br clear="all"><br>-- <br>--<br>Phil<br><br>