[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