Wednesday, 17 September 2014

CoalesceEmpty in MDX

WITH
   MEMBER [Measures].[Order Percent by Category] AS
   CoalesceEmpty
   (
      (
        [Claim Received Date].[Year - Month - Date].CurrentMember,
        [Measures].[Billed Amount])
        /
        (
           Ancestor
           ( [Claim Received Date].[Year - Month - Date].CurrentMember,
             [Claim Received Date].[Year - Month - Date].[Year]
           )
           , [Measures].[Billed Amount]
       )
       , 0
   ), FORMAT_STRING='Percent'
SELECT
   {
      [Measures].[Billed Amount],
      [Measures].[Order Percent by Category]
   } ON COLUMNS,
EXCEPT
(
    [Claim Received Date].[Year - Month - Date].Members
    , [Claim Received Date].[Year - Month - Date].[All]
) ON ROWS
FROM (SELECT ({[Claim Received Date].[Year - Month - Date].[Year].&[2014]}) ON 0
FROM [SampleCompany]
)

No comments:

Post a Comment