[Mondrian] Dinamically build members in a MDX query?

Pedro Alves pmgalves at gmail.com
Fri Sep 4 15:10:55 EDT 2009



Hello.


I need to do a mdx query that outputs in rows the elapsed days from a 
certain date; (I want to study the uptake of firefox versions since 
their release)


For this I only have the date dimension; I can do what I want if I 
specify the members one by one, but this is really a major hack; Is 
there any alternative approach? Can I define a range of calculated 
members dinamically ?


Here's the query (/me is ashamed):



with set [versions] as '{[Products].[All].[Firefox].[3.5].[3.5.1], 
[Products].[All].[Firefox].[3.5].[3.5.2]}'
   member [Measures].[Diff] as '([versions].Item(1.0) - 
[versions].Item(0.0))'
   set [dates1] as '([Date].[All].[2009].[7].[15].[2009-07-15] : 
[Date].[All].[2009].[7].[15].[2009-07-15].Lag((- 24.0)))'
   set [dates2] as '([Date].[All].[2009].[7].[31].[2009-07-31] : 
[Date].[All].[2009].[7].[31].[2009-07-31].Lag((- 24.0)))'
   member [Locations].[-4] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(0.0), [Measures].[Uptake]), 
([dates2].Item(0.0), [Measures].[Uptake]))'
   member [Locations].[-3] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(1.0), [Measures].[Uptake]), 
([dates2].Item(1.0), [Measures].[Uptake]))'
   member [Locations].[-2] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(2.0), [Measures].[Uptake]), 
([dates2].Item(2.0), [Measures].[Uptake]))'
   member [Locations].[-1] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(3.0), [Measures].[Uptake]), 
([dates2].Item(3.0), [Measures].[Uptake]))'
   member [Locations].[0] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(4.0), [Measures].[Uptake]), 
([dates2].Item(4.0), [Measures].[Uptake]))'
   member [Locations].[1] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(5.0), [Measures].[Uptake]), 
([dates2].Item(5.0), [Measures].[Uptake]))'
   member [Locations].[2] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(6.0), [Measures].[Uptake]), 
([dates2].Item(6.0), [Measures].[Uptake]))'
   member [Locations].[3] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(7.0), [Measures].[Uptake]), 
([dates2].Item(7.0), [Measures].[Uptake]))'
   member [Locations].[4] as 'IIf(([Products].CurrentMember.Name = 
[versions].Item(0.0).Name), ([dates1].Item(8.0), [Measures].[Uptake]), 
([dates2].Item(8.0), [Measures].[Uptake]))'
[Measures].[Uptake]), ([dates2].Item(24.0), [Measures].[Uptake]))'
   member [Measures].[Uptake] as '((100.0 * ([Products].CurrentMember, 
[Measures].[Total Requests])) / ([Products].Parent, [Measures].[Total 
Requests]))'
select NON EMPTY [versions] ON COLUMNS,
   NON EMPTY {[Locations].[-4], [Locations].[-3], [Locations].[-2], 
[Locations].[-1], [Locations].[0], [Locations].[1], [Locations].[2], 
[Locations].[3], [Locations].[4], [Locations].[5], [Locations].[6], 
[Locations].[7], [Locations].[8], [Locations].[9], [Locations].[10], 
[Locations].[11], [Locations].[12], [Locations].[13], [Locations].[14], 
[Locations].[15], [Locations].[16], [Locations].[17], [Locations].[18], 
[Locations].[19], [Locations].[20]} ON ROWS
from [BlockList Analysis]



More information about the Mondrian mailing list