[Mondrian] Mondrian & PRD Column Headers with Parameters

Phil Cole filcole at gmail.com
Thu Nov 5 12:48:40 EST 2009


I’m working with PRD and mondrian.  I have the following MDX query which
takes a single parameter “CalcMDXDateStr” provided by a DatePicker ->
OpenFormula combo in PRD.

select

  CROSSJOIN(

      { strToMember( Parameter("CalcMDXDateStr", STRING, "[Date].[All
Years].[2009].[Q4].[NOV].[4]" )) },

      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth],
[Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD],
[Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }

  ) ON COLUMNS,

  NON EMPTY [Area].[All Areas].Children ON ROWS

from [Enquiries]

where (

    [Activity.Activity Type].[All Types].[Created]

)

This query returns 69 rows with the following column headers:

[Area].[(All)]

[Area].[Desc]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjsLastMonth]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjs
ThisMonthLastYear]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjsYTD]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjsYTDLastYear]

[Date].[All Years].[2009].[Q4].[NOV].[4]/[Measures].[NumProjsLastYear]

Now these column headers are horrible to work with so I wanted to replace
the data parameter with a named member, i.e.

with

  member [Date].[DateParam] as 'strToMember( Parameter("CalcMDXDateStr",
STRING, "[Date].[All Years].[2009].[Q4].[NOV].[4]" ))'

select

  CROSSJOIN(

      { [Date].[DateParam] },

      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth],
[Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD],
[Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }

  ) ON COLUMNS,

  NON EMPTY [Area].[All Areas].Children ON ROWS

from [Enquiries]

where (

    [Activity.Activity Type].[All Types].[Created]

)

But this query only returns 9 rows, and all measure values are zero apart
from column [Measures].[NumProjs].  The column headers are lovely though,
e.g. [Date].[DataParm]/[Measures].[NumProjs]

I’m confused.  I’ve replaced a Member with a Member?  Is this a bug?

I’ve converting it to a set with the following MDX query, but then the same
horrible column headers are presented in PRD.

with

  set [DateParam] as '{ strToMember( Parameter("CalcMDXDateStr", STRING,
"[Date].[All Years].[2009].[Q4].[NOV].[4]" )) }'

select

  CROSSJOIN(

      [DateParam],

      { [Measures].[NumProjs], [Measures].[NumProjsLastMonth],
[Measures].[NumProjsThisMonthLastYear], [Measures].[NumProjsYTD],
[Measures].[NumProjsYTDLastYear],  [Measures].[NumProjsLastYear] }

  ) ON COLUMNS,

  NON EMPTY [Area].[All Areas].Children ON ROWS

from [Enquiries]

where (

    [Activity.Activity Type].[All Types].[Created]

)

I’m confused!!

Phil

 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: phillip.cole at uk.coltgroup.com | Web:
www.coltinfo.co.uk

Colt has received LABC accreditation for the Colt Shaft System, the Colt
Cyclone and the Colt Jetstream Car Park Ventilation fans.
For further information on Colt and LABC, please click
here<http://www.coltinfo.co.uk/news/news_LABC.html>
.

For Colt's email confidentiality and monitoring policy, usage restrictions,
or for company registration and regulatory status information, please visit
http://www.coltinfo.co.uk/legal-notice.html

 Hi,



-- 
--
Phil
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20091105/59b8ece6/attachment.html 


More information about the Mondrian mailing list