none
Problem with added calculated Measures members qualifying on YTD, in kombination with MDX parameter RRS feed

  • Question

  • Problem:
    In a dataset i add 9 calculated members to measures (using WITH MEMBER).
    Half of them are are qualifyed YTD that are part of a dimension called [Time Calculations].[YTD] and they for some reason do not show up in the visaulization when i run the Question in the Query designer. They do work fine in another dataset i use though. The big difference between the datasets are that in this i have a MDX parameter that generate a sum row based on a couple of members of the account dimension. The other one use decendants of one specific member of the account dimension on the rows.

    --------------------------
    Dataset Query:
    WITH
    MEMBER [Measures].[Budget_MTD] AS 'SUM([Category].[Category].&[2], [Measures].[Amount Finance])'         
    MEMBER [Measures].[Actual_LY_MTD]  AS SUM({[Category].[Category].[All].[Actual_LY]},[Measures].[Amount Finance])      
    MEMBER [Measures].[Actual-Budget_MTD] AS (([Measures].[Amount Finance])-SUM([Category].[Category].&[2], [Measures].[Amount Finance])) 
    MEMBER [Measures].[Actual-Actual_LY_MTD] AS (([Measures].[Amount Finance]) - SUM({[Category].[Category].[All].[Actual_LY]},[Measures].[Amount Finance]))

    MEMBER [Measures].[Actual_YTD] AS SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])  
    MEMBER [Measures].[Budget_YTD] AS SUM({[Category].[Category].&[2]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])  
    MEMBER [Measures].[Actual_LY_YTD] AS
     SUM({[Category].[Category].[All].[Actual_LY]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance]) 
    MEMBER [Measures].[Actual-Budget_YTD]
     AS ( SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])
      - SUM({[Category].[Category].&[2]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])) 
    MEMBER [Measures].[Actual-Actual_LY_YTD]
     AS (SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])
      - SUM({[Category].[Category].[All].[Actual_LY]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance]))

    SELECT NON EMPTY { [Measures].[Amount Finance]
    ,[Measures].[Budget_MTD]         
    ,[Measures].[Actual_LY_MTD]        
    ,[Measures].[Actual-Budget_MTD]        
    ,[Measures].[Actual-Actual_LY_MTD]       
    ,[Measures].[Actual_YTD]        
    ,[Measures].[Budget_YTD]         
    ,[Measures].[Actual_LY_YTD]        
    ,[Measures].[Actual-Budget_YTD]        
    ,[Measures].[Actual-Actual_LY_YTD]


     } ON COLUMNS FROM
    ( SELECT ( STRTOSET(@CostCenterCostCenter, CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( STRTOSET(@ReportingCurrencyReportingCurrency, CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( STRTOSET(@DepartmentDepartment, CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( {[Account].[Account].&[O.4000.TT]
    , [Account].[Account].&[O.5000.TT]
    ,[Account].[Account].&[O.4003.TT]
    ,[Account].[Account].&[O.6500.TT]
    ,[Account].[Account].&[O.6000.TT]
    ,[Account].[Account].&[O.7000.TT]
    ,[Account].[Account].&[O.7014.TT]
    ,[Account].[Account].&[O.4001.TT]
    ,[Account].[Account].&[O.7015.TT]
    } ) ON COLUMNS FROM
    ( SELECT ( STRTOSET(@LocationLocation, CONSTRAINED) )
    ON COLUMNS FROM ( SELECT ( STRTOSET(@PeriodsYearPeriod, CONSTRAINED) )
    ON COLUMNS FROM [Nobina_Group]))))))
    WHERE ( IIF( STRTOSET(@PeriodsYearPeriod, CONSTRAINED).Count = 1, STRTOSET(@PeriodsYearPeriod, CONSTRAINED), [Periods].[Year - Period].currentmember ),
    IIF( STRTOSET(@LocationLocation, CONSTRAINED).Count = 1, STRTOSET(@LocationLocation, CONSTRAINED), [Location].[Location].currentmember ),
    IIF( STRTOSET(@DepartmentDepartment, CONSTRAINED).Count = 1, STRTOSET(@DepartmentDepartment, CONSTRAINED), [Department].[Department].currentmember ),
    IIF( STRTOSET(@ReportingCurrencyReportingCurrency, CONSTRAINED).Count = 1, STRTOSET(@ReportingCurrencyReportingCurrency, CONSTRAINED), [Reporting Currency].[Reporting Currency].currentmember ),
    IIF( STRTOSET(@CostCenterCostCenter, CONSTRAINED).Count = 1, STRTOSET(@CostCenterCostCenter, CONSTRAINED), [Cost Center].[Cost Center].currentmember ) )
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


    --------------------------
    What have i done:

    1 Created dataset
    2 Added needed parameters
    3 Added parameter with MDX instead of equal
      The MDX expression just adding together some accounts i need to sum: 
     {[Account].[Account].&[O.4000.TT]
     ,[Account].[Account].&[O.5000.TT]
     ,[Account].[Account].&[O.4003.TT]
     ,[Account].[Account].&[O.6500.TT]
     ,[Account].[Account].&[O.6000.TT]
     ,[Account].[Account].&[O.7000.TT]
     ,[Account].[Account].&[O.7014.TT]
     ,[Account].[Account].&[O.4001.TT]
     ,[Account].[Account].&[O.7015.TT]
     }
    4 Changed to Query mode since i needed to alter the code to add calculated members of Measures
      First half is a set with [Time Calculations].[Current periods] per default from the SSAS-Cube that we use
      Second half use [Time Calculations].[YTD] to qualify and that has to be specified to override the default.
     
     WITH
     MEMBER [Measures].[Budget_MTD] AS 'SUM([Category].[Category].&[2], [Measures].[Amount Finance])'         
     MEMBER [Measures].[Actual_LY_MTD]  AS SUM({[Category].[Category].[All].[Actual_LY]},[Measures].[Amount Finance])      
     MEMBER [Measures].[Actual-Budget_MTD] AS (([Measures].[Amount Finance])-SUM([Category].[Category].&[2], [Measures].[Amount Finance])) 
     MEMBER [Measures].[Actual-Actual_LY_MTD] AS (([Measures].[Amount Finance]) - SUM({[Category].[Category].[All].[Actual_LY]},[Measures].[Amount Finance]))

     MEMBER [Measures].[Actual_YTD] AS SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])  
     MEMBER [Measures].[Budget_YTD] AS SUM({[Category].[Category].&[2]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])  
     MEMBER [Measures].[Actual_LY_YTD] AS
     SUM({[Category].[Category].[All].[Actual_LY]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance]) 
     MEMBER [Measures].[Actual-Budget_YTD]
     AS ( SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])
      - SUM({[Category].[Category].&[2]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])) 
     MEMBER [Measures].[Actual-Actual_LY_YTD]
     AS (SUM({[Category].[Category].&[1]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance])
      - SUM({[Category].[Category].[All].[Actual_LY]}*{[Time Calculations].[YTD]},[Measures].[Amount Finance]))


    5 Added them to the SELECT NON EMPTY to visaulize the.

     SELECT NON EMPTY { [Measures].[Amount Finance]
     ,[Measures].[Budget_MTD]         
     ,[Measures].[Actual_LY_MTD]        
     ,[Measures].[Actual-Budget_MTD]        
     ,[Measures].[Actual-Actual_LY_MTD]       
     ,[Measures].[Actual_YTD]        
     ,[Measures].[Budget_YTD]         
     ,[Measures].[Actual_LY_YTD]        
     ,[Measures].[Actual-Budget_YTD]        
     ,[Measures].[Actual-Actual_LY_YTD]

    6 The expected result would be every each of them visaulized when i run the query, but that is not the case
      The ones that use [Time Calculations].[Current periods]: 

     [Measures].[Amount Finance]
     ,[Measures].[Budget_MTD]         
     ,[Measures].[Actual_LY_MTD]        
     ,[Measures].[Actual-Budget_MTD]        
     ,[Measures].[Actual-Actual_LY_MTD]

      shows up just perfect. But the ones using  [Time Calculations].[YTD] to qualify:

     ,[Measures].[Actual_YTD]        
     ,[Measures].[Budget_YTD]         
     ,[Measures].[Actual_LY_YTD]        
     ,[Measures].[Actual-Budget_YTD]        
     ,[Measures].[Actual-Actual_LY_YTD]
     
      do not shown at all in the visualization window. Not as a header with null as value, it just doesnt appear.

     

     

    Tuesday, August 20, 2013 2:20 PM

All replies

  • Hi Brittoj,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Charlie Liao
    TechNet Community Support

    Monday, August 26, 2013 8:26 AM
    Moderator
  • Hi,

    this is quite a complex query. If you were able to reproduce this on AdventureWorks please post it, otherwise I suggest opening a support case where an engineer will be able to look into this.

    Thanks,
    Orsi
    Microsoft Online Community Support

    Tuesday, November 19, 2013 9:41 AM
    Answerer