locked
Does PowerPoint allow "calculated members" in MDX sets? RRS feed

  • Question

  • I'm trying to create a set in PowerPivot using MDX.  The reason I'm (trying to) using MDX is that I want to stick a "calculated member" in my set.  Unfortunately, I'm not getting it to work. I get the error "Data could not be retrieved from the external datasource".

    Example:
    I'm trying to add a calculated member, named "GrossMargin", to my set definition.  This is my set MDX:

     

    WITH MEMBER  [1Ledger].[GLAccount].[All].[GrossMargin] AS ( [1Ledger].[GLAccount].&[Revenue] + [1Ledger].[GLAccount].&[COGS] )
    
    { [1Ledger].[GLAccount].AllMembers }
    

     

    Why do I get the error? Can it be that PowerPoint does not support "calculated members" in MDX set definitions? 

    Any help will be greatly appreciated!

    ---Hugo

     

    Friday, December 3, 2010 5:54 AM

Answers

  • Hi, Hugo,

    My condolences and commiserations to you on your attempt to use MDX in PowerPivot. I got quite a few gray hairs trying to do the same. The good news is that I was able to successfully execute an MDX query with calculated members in PowerPivot.

    However, working with the MDX Query Import Wizard in PowerPivot proved very difficult for me. I finally gave up and started doing all of my MDX query building and validation in SQL Server Managment Studio. Once I did that, and simply pasted validated queries into PowerPivot Table Import Wizard, things became much easier.

    Looking at your code, I'm assuming you want GrossMargin as a measure, correct? If so, try something like 

    WITH MEMBER [Measures].[GrossMargin] AS ....

    Here's an example of a calculated member that works for me:

    WITH MEMBER [Measures].[Forecast Date] AS

     (

                    ParallelPeriod

    (

                                    [Register Date].[Planning Hierarchy].[Date],

                                     -364,

                                     [Register Date].[Planning Hierarchy].CurrentMember

                                     )

    .Name

    )

    , Format = "DD/MM/YYYY"

     

    I hope that helps. Good luck!  

     

    Thursday, December 9, 2010 9:10 PM

All replies

  • Hi, Hugo,

    My condolences and commiserations to you on your attempt to use MDX in PowerPivot. I got quite a few gray hairs trying to do the same. The good news is that I was able to successfully execute an MDX query with calculated members in PowerPivot.

    However, working with the MDX Query Import Wizard in PowerPivot proved very difficult for me. I finally gave up and started doing all of my MDX query building and validation in SQL Server Managment Studio. Once I did that, and simply pasted validated queries into PowerPivot Table Import Wizard, things became much easier.

    Looking at your code, I'm assuming you want GrossMargin as a measure, correct? If so, try something like 

    WITH MEMBER [Measures].[GrossMargin] AS ....

    Here's an example of a calculated member that works for me:

    WITH MEMBER [Measures].[Forecast Date] AS

     (

                    ParallelPeriod

    (

                                    [Register Date].[Planning Hierarchy].[Date],

                                     -364,

                                     [Register Date].[Planning Hierarchy].CurrentMember

                                     )

    .Name

    )

    , Format = "DD/MM/YYYY"

     

    I hope that helps. Good luck!  

     

    Thursday, December 9, 2010 9:10 PM
  • Hi Wade,

    Thanks for your response -- and thanks for your condolences ;-)  

    Unfortunately, I'm still stuck.  The MDX scenario you describe is a bit different from what I try to do ... I'm trying to use MDX in the (row) Named Set editor.  It looks like you are using MDX during import of data into PowerPoint.

    I've been searching for documentation on using MDX in PowerPivot but without success.  My conclusion for now is that in the Named Set editor only "set functions" like "hierarchize", "topcount", etc. work.  I believe "calculated members" don't work.

    Anyhow, to prevent getting more grey hair, I'll refrain from using MDX for now.

    Thanks,

    ---Hugo

     

     

     

     

     

    Tuesday, December 14, 2010 2:37 AM