[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