I have this script in MDX
:
WITH
MEMBER [Measures].[FirstDay] as
HEAD (
DESCENDANTS (
[Date].[Date - Calendar Month].CURRENTMEMBER,
[Date].[Date - Calendar Month].[Calendar Day] )
, 1
).ITEM( 0 ).member_value
MEMBER [Measures].[LastDay] as
TAIL (
DESCENDANTS (
[Date].[Date - Calendar Month].CURRENTMEMBER,
[Date].[Date - Calendar Month].[Calendar Day] )
, 1
).ITEM( 0 ).member_value
SELECT
{
[Measures].[FirstDay],
[Measures].[LastDay]
} ON COLUMNS
FROM [OurCube]
WHERE
({[Date].[Date - Calendar Month].[Calendar Year].&[2012],
[Date].[Date - Calendar Month].[Calendar Year].&[2013]})
Problem is that I'm trying to combine 2012 and 2013 in the WHERE
clause as a set: this is then being used as the context for evaluation of the CURRENTMEMBER
function in the custom measures - but this function expects a member and not a set.
How can I change the script so that it works returning 01 Jan 2012
and 31 Dec 2013
without including the dimension [Date]
on either rows or columns ?
WITH
MEMBER [Measures].[FirstDay] as
(
EXISTING [Date].[Date - Calendar Month].[Calendar Day].Members
).ITEM( 0 ).member_value
MEMBER [Measures].[LastDay] as
TAIL (
EXISTING [Date].[Date - Calendar Month].[Calendar Day].Members
, 1
).ITEM( 0 ).member_value
SELECT
{
[Measures].[FirstDay],
[Measures].[LastDay]
} ON COLUMNS
FROM [OurCube]
WHERE
({[Date].[Date - Calendar Month].[Calendar Year].&[2012],
[Date].[Date - Calendar Month].[Calendar Year].&[2013]})
should deliver that. I simplified the first calculation a bit: as ITEM(0)
already takes the first tuple, there is no need to use HEAD(..., 1)
.
See http://sqlblog.com/blogs/mosha/archive/2007/01/13/multiselect-friendly-mdx-for-calculations-looking-at-current-coordinate.aspx and the predecessor article referenced in the first sentence for some suggestions for "multiselect friendly MDX" from one of the developers of SSAS.