none
Ratio to Parent by Multiple Dimensions on both Row and Column Axes

    Question

  • Hi,

    I recently read "Percentage of Parent for all dimensions" thread ...To say the least, it was eye-opening...The amount of effort and detailed explanation that Tomislav Piasevoli put in was tremendous and helpful. I'm doing the same thing for our BI requirements, and I practically copied the entire code and see that it works for dimensions that are being identified on both Axes (Column / Row). However, the code is not a perfect solution for my situation…It seems that the calculation only works for any single Dimension on each Axis.  If I select more than one dimension onto the Colum / Row axes, the results came out incorrectly for the inner dimensions.


    Note that: The possible dimensions that will be used for the “Ratio to Parent” are as follows:

    Geography

    Carrier

    Plan Type

    Time (Plan Year)

     

    The Code that I used is as follows:

     

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of columns]

     AS iif(IsError(Axis(0).Count), 0, Axis(0).Count),

    VISIBLE = 1; 

     

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of rows]

     AS iif(IsError(Axis(1).Count), 0, Axis(1).Count),

    VISIBLE = 1;  

     

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio Universal]

     AS iif( [Measures].[Count of rows] > 0,

     

    iif( Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Level.Ordinal = 0,1,

     

          [Measures].[Plan Count]/

     

         ( Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Parent,[Measures].[Plan Count] )),

     

    iif( [Measures].[Count of columns] > 0,

     

    iif( Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Level.Ordinal = 0,1,

     

        [Measures].[Plan Count] /

     

         ( Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Parent,[Measures].[Plan Count])),

    1)),

    FORMAT_STRING = "Percent",

    VISIBLE = 1  ;

     

    Sincerely thank you very much in advance for your time and assistance!

    Wednesday, September 23, 2009 12:35 PM

Answers

  • Hi Rhonda,

    I've already sent you the MDX script that works with SSMS and other tools/front-ends over the weekend. You can paste it here if you want.

    Today, I'm sending you that same script adjusted to work in OWC aka Cube Browser. Note: it only works correctly in BIDS 2005, not 2008. BIDS 2008 has some problems with it, some functions were implemented differently. I'm mentioning this for others, it doesn't affect you because you're on 2005. I might make it work in 2008 also some day. The script I sent you over the weekend works, however, in 2005 and in 2008, just not in OWCs.

    As you can see, we did it before the deadline.

    Create Member CurrentCube.[Measures].[Where are measures in OWC] AS
        case
            when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
            when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
            when NOT IsError(Extract( Axis(2), Measures ).Count) then 2
            else -1  -- should not be possible
        end
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Ratio measure] AS
        -- specify a measure you want to have a ratio for, or
        -- use default measure, or
        -- use dynamic expression, that selects either first measure on an axis or
        -- the default measure in case no measure is selected
    
        /* -- option 1 */
        [Measures].[Sales Amount]
        /* -- option 2
        [Measures].DefaultMember
        */
        /* -- option 3
          iif(Extract( Axis( [Measures].[Where are measures in OWC] ),
                       Measures ).Item(0).Item(0).UniqueName =
              '[Measures].[Universal ratio %]',
              [Measures].DefaultMember,
              Extract( Axis([Measures].[Where are measures in OWC]), Measures ).Item(0)
             )
        */
        , Visible = 0
    ; 
     
    Create Member CurrentCube.[Measures].[Test Empty] AS
        IsEmpty( [Measures].[Ratio measure] )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Count of columns] AS
        iif( IsError( Axis(1).Count ),
             0,
             iif( [Measures].[Where are measures in OWC] = 1,
                  Axis(0).Item(0).Count,
                  Axis(1).Item(0).Count )
           )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Bitmap Index as String] AS
        iif( [Measures].[Where are measures in OWC] = 1,
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(0).Item(0).Item(L.CurrentOrdinal - 1).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  ),
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(1).Item(0).Item(L.CurrentOrdinal - 1).Hierarchy.CurrentMember.Level.Ordinal = 0)
              )
         )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Reversed Bitmap Index as String] AS
        iif( [Measures].[Where are measures in OWC] = 1,
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(0).Item(0).Item([Measures].[Count of columns] - 
    				     L.CurrentOrdinal).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  ),
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(1).Item(0).Item([Measures].[Count of columns] - 
    				     L.CurrentOrdinal).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  )
           )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Rightmost non root position] AS
        InStr([Measures].[Reversed Bitmap Index as String], '0')
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Inner 100%] AS
        CInt([Measures].[Bitmap Index as String]) = 1
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Universal ratio %] AS
        iif( [Measures].[Test Empty],
             null,
             iif( [Measures].[Rightmost non root position] = 0 OR
                  [Measures].[Inner 100%],
                  1,        
                  iif( [Measures].[Where are measures in OWC] = 1,
                       -- check division by zero
                       iif( ( Axis(0).Item(0).Item([Measures].[Count of columns] -
                                                   [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                              [Measures].[Ratio measure] ) = 0,
                            0,
                            [Measures].[Ratio measure]        
                            /
                            ( Axis(0).Item(0).Item([Measures].[Count of columns] -
                                                   [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                              [Measures].[Ratio measure] )),
                        -- check division by zero
                        iif( ( Axis(1).Item(0).Item([Measures].[Count of columns] -
                                                    [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                               [Measures].[Ratio measure] ) = 0,
                               0,
                               [Measures].[Ratio measure]        
                               /
                               ( Axis(1).Item(0).Item([Measures].[Count of columns] -
                                                      [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                                 [Measures].[Ratio measure] ) )
                     )              
                )
           ),
        FORMAT_STRING = "percent"
    ;



    Detailed explaination coming soon on my blog.

    Best regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    • Marked as answer by SSASNewBee Monday, September 28, 2009 6:25 PM
    • Unmarked as answer by SSASNewBee Monday, September 28, 2009 6:26 PM
    • Marked as answer by SSASNewBee Tuesday, September 29, 2009 4:19 PM
    Monday, September 28, 2009 6:20 AM
    Answerer

All replies

  • Hi,

    Since I posted the question yesterday, I've tried many different approaches...combining other codes from a couple of threads that Tomislav Piasevoli (wondering if he sees my post this time...)  participated...And I think I'm closer to the results that I want....At least now, it returns the percentages at both the innermost and outermost dimensions... However, (1) some results...in the innermost dimension displays incorrect percentages relatively to others in the same grouping...and it's the same for other years (for trending purpose)...(2) The GrandTotal Percentage display as "1.INF"...and the number should be "100.00%" NOT the infinite indicator...Of course w/ my limited knowledge about MDX scripting has made it more challenging...The code that I used below...



    CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of Dim]AS
    Axis(1).Item(0).Count,
    VISIBLE = 1;

     

    CREATE

     

    MEMBER CURRENTCUBE.[MEASURES].[Ratio Universal] AS
    IIF ( Axis(1).Item(0).Item( [Measures].[Count of Dim] -1).Hierarchy.CurrentMember.Level.Ordinal = 0,
    [Measures].[Plan Count] / (
    Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Parent,[Measures].[Plan Count] ),
    [Measures].[Plan Count] / (
    Axis(1).Item(0).Item( [Measures].[Count of Dim] -1).Hierarchy.CurrentMember.Parent,[Measures].[Plan Count] )
         ),
    FORMAT_STRING = "Percent",
    VISIBLE = 1;

     

     

     

    I really really really appreciate it if anyone can help me before Monday...!!!

    rhonda

    Thursday, September 24, 2009 6:02 PM
  • I'm seeing it Rhonda. Just letting others to participate, since I had a feeling you deliberately discontinued our discussion in the previous thread in order to get some fresh ideas by others. Guess you're stuck with me.

    Ok, here's what we'll do. I need a detailed example of current results (paste them here formatted), what is wrong in which places, what should be there instead. It's only a matter of fine-tuning the calculation. You see, there can be many variations of it. I need to know exactly what you want. For each cell or combination.

    Besides that, there were many calculations in previous thread, why not some of the latest? I believe they are improved.

    Regarding 1.INF. It can be fixed using additional iif statements. Regarding innermost dimension. You should know that OWC shows 100 when collapsed and something else when expanded. I doesn't depend on MDX, it is the way it presents result. But, after you explain what you need, I'll see what can be done.

    It can be done before Monday, but I need you to focus, be precise in your explaination and keep me informed the best you can preferably using vivid examples. There's always a mail, in case of emergency (since some notifications fail).

    To be continued ...


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, September 24, 2009 7:48 PM
    Answerer
  • Hello Tomislav,

    I'm so glad you join me again...As a matter of fact, I feel relief that you do!!! Because I've learned lots from your codes and explanations...But first, I didn't want to bother you right now since you did mention that you'd been busy during this period. Second, I discontinued the last thread because you did answer the original question...and I thought it wouldn't be right to expand my problem just because I have a moving target requirement (which sometimes we can't control)...lol

    Below is the sample of the dataset....oops...I can't seem to paste the results that I copied from Excel...It's too big...I'll post to the next post.

    As always, your time and effort is greatly appreciated!

    rhonda

    • Marked as answer by SSASNewBee Thursday, September 24, 2009 9:47 PM
    • Unmarked as answer by SSASNewBee Thursday, September 24, 2009 9:55 PM
    Thursday, September 24, 2009 9:36 PM
  • Hi Rhonda,

    I've already sent you the MDX script that works with SSMS and other tools/front-ends over the weekend. You can paste it here if you want.

    Today, I'm sending you that same script adjusted to work in OWC aka Cube Browser. Note: it only works correctly in BIDS 2005, not 2008. BIDS 2008 has some problems with it, some functions were implemented differently. I'm mentioning this for others, it doesn't affect you because you're on 2005. I might make it work in 2008 also some day. The script I sent you over the weekend works, however, in 2005 and in 2008, just not in OWCs.

    As you can see, we did it before the deadline.

    Create Member CurrentCube.[Measures].[Where are measures in OWC] AS
        case
            when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
            when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
            when NOT IsError(Extract( Axis(2), Measures ).Count) then 2
            else -1  -- should not be possible
        end
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Ratio measure] AS
        -- specify a measure you want to have a ratio for, or
        -- use default measure, or
        -- use dynamic expression, that selects either first measure on an axis or
        -- the default measure in case no measure is selected
    
        /* -- option 1 */
        [Measures].[Sales Amount]
        /* -- option 2
        [Measures].DefaultMember
        */
        /* -- option 3
          iif(Extract( Axis( [Measures].[Where are measures in OWC] ),
                       Measures ).Item(0).Item(0).UniqueName =
              '[Measures].[Universal ratio %]',
              [Measures].DefaultMember,
              Extract( Axis([Measures].[Where are measures in OWC]), Measures ).Item(0)
             )
        */
        , Visible = 0
    ; 
     
    Create Member CurrentCube.[Measures].[Test Empty] AS
        IsEmpty( [Measures].[Ratio measure] )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Count of columns] AS
        iif( IsError( Axis(1).Count ),
             0,
             iif( [Measures].[Where are measures in OWC] = 1,
                  Axis(0).Item(0).Count,
                  Axis(1).Item(0).Count )
           )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Bitmap Index as String] AS
        iif( [Measures].[Where are measures in OWC] = 1,
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(0).Item(0).Item(L.CurrentOrdinal - 1).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  ),
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(1).Item(0).Item(L.CurrentOrdinal - 1).Hierarchy.CurrentMember.Level.Ordinal = 0)
              )
         )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Reversed Bitmap Index as String] AS
        iif( [Measures].[Where are measures in OWC] = 1,
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(0).Item(0).Item([Measures].[Count of columns] - 
    				     L.CurrentOrdinal).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  ),
            Generate( Head(Measures.AllMembers, [Measures].[Count of columns] ) AS L,
                      -(Axis(1).Item(0).Item([Measures].[Count of columns] - 
    				     L.CurrentOrdinal).Hierarchy.CurrentMember.Level.Ordinal = 0)
                  )
           )
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Rightmost non root position] AS
        InStr([Measures].[Reversed Bitmap Index as String], '0')
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Inner 100%] AS
        CInt([Measures].[Bitmap Index as String]) = 1
        , Visible = 0
    ;
    
    Create Member CurrentCube.[Measures].[Universal ratio %] AS
        iif( [Measures].[Test Empty],
             null,
             iif( [Measures].[Rightmost non root position] = 0 OR
                  [Measures].[Inner 100%],
                  1,        
                  iif( [Measures].[Where are measures in OWC] = 1,
                       -- check division by zero
                       iif( ( Axis(0).Item(0).Item([Measures].[Count of columns] -
                                                   [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                              [Measures].[Ratio measure] ) = 0,
                            0,
                            [Measures].[Ratio measure]        
                            /
                            ( Axis(0).Item(0).Item([Measures].[Count of columns] -
                                                   [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                              [Measures].[Ratio measure] )),
                        -- check division by zero
                        iif( ( Axis(1).Item(0).Item([Measures].[Count of columns] -
                                                    [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                               [Measures].[Ratio measure] ) = 0,
                               0,
                               [Measures].[Ratio measure]        
                               /
                               ( Axis(1).Item(0).Item([Measures].[Count of columns] -
                                                      [Measures].[Rightmost non root position]).Hierarchy.CurrentMember.Parent,
                                 [Measures].[Ratio measure] ) )
                     )              
                )
           ),
        FORMAT_STRING = "percent"
    ;



    Detailed explaination coming soon on my blog.

    Best regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    • Marked as answer by SSASNewBee Monday, September 28, 2009 6:25 PM
    • Unmarked as answer by SSASNewBee Monday, September 28, 2009 6:26 PM
    • Marked as answer by SSASNewBee Tuesday, September 29, 2009 4:19 PM
    Monday, September 28, 2009 6:20 AM
    Answerer
  • Hi Tomislav,

    YES, YOU DID IT!!! As always, you've saved the day...in this case is MONDAY!  Throughout my 11 years as an IT professional, I've never seen or met anyone who has the patience and persistence that you possess!!!  It's really admirable and respectable!!!

    Kudos to you and the lightspeed turnaround!

    rhonda
    Tuesday, September 29, 2009 4:19 PM
  • Here's a direct link to the article on my blog: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!436.entry .

    Explanation included.



    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Sunday, October 25, 2009 7:32 AM
    Answerer
  • Hello Tomislav,

    I studied your solutions calculating the ratio to parent. But i always have problems, when i use excel as the client tool with filters. It seems, that the calculation in excel is based on the entire dimension set, instead of the filtered one. When i use the cube browser it works fine.

    Don't you had that problem?

    Tuesday, October 26, 2010 9:48 PM
  • Hi,

    if you filter by one member per hierarchy only, then I believe it should work in Excel also. When you check multiple members, Excel throws them in the subselect part of the query and that's beyond the .CurrentMember function. In that case, the function evaluates for the root member which is something you're experiencing I believe. Check the MDX with the Profiler.

    I don't use neither Excel nor OWC, I merely provide complex calculations for various people given the constrains regarding the tool they use and the requests imposed for a particular case. I try to push the limits but I can do that only as much a particular tool and case allow me.

    Regards,



    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, October 26, 2010 10:32 PM
    Answerer
  • Thanks for that hint, i think the subselect is the problem. If you know any workaround or another way how to get or access the grand total with mdx, please let me know.

    Thanks!

    Wednesday, October 27, 2010 7:27 AM