Monday, 9 December 2013

Truncating Timestamp in MDX

This can be done either by using CDate Or Left function as shown in examples below

Example 1:

WITH MEMBER [Measures].[FDATE]
AS
CDate([Date].[Calendar].CURRENTMEMBER.MEMBER_Value)

SELECT
    {[Measures].[FDATE],[Measures].[Sales Amount]} ON 0
    ,{[Date].[Calendar].[Date].MEMBERS} ON 1
FROM
(
    SELECT [Date].[Calendar].[Calendar Year].&[2008] ON COLUMNS
    FROM [Adventure Works]
);

Example 2:

WITH MEMBER [Measures].[FDATE]
AS
LEFT([Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION,10)

SELECT
    {[Measures].[FDATE],[Measures].[Sales Amount]} ON 0
    ,{[Date].[Calendar].[Date].MEMBERS} ON 1
FROM
(
    SELECT [Date].[Calendar].[Calendar Year].&[2008] ON COLUMNS
    FROM [Adventure Works]
)


No comments:

Post a Comment