[Mondrian] Re: Mondrian & PRD Column Headers with Parameters

Phillip Cole filcole at gmail.com
Thu Nov 5 18:00:14 EST 2009


Replying to my own post is bad form but others may be interested.  The 
solution is to use SOLVE_ORDER to ensure that [Date].[DateParm] is 
evaluated before the rest of the calculated measures.

WITH
  Member [Date].[DateParm] as 'Parameter("CalcMDXDateStr", [Date], 
[Date].[All Years].[2009].[Q4].[NOV].[4] )'*, SOLVE_ORDER=100*
select
  CROSSJOIN(
      { [Date].[DateParm] },
      { [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 now get usable column headers/field names within PRD.

Phil

p.s. Thanks to Kaapa for help removing the unnecessary strToMember().

Phil Cole wrote:
>
> 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].[NumProjsThisMonthLastYear]
> [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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20091105/e07b066f/attachment.html 


More information about the Mondrian mailing list