index > SQL Server Analysis Services > Ratio to Parent by Multiple Dimensions on both Row and Column Axes

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

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!

SSASNewBee Wednesday, September 23, 2009 12:35 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 youthat same scriptadjusted towork in OWC aka Cube Browser. Note: it only works correctly inBIDS 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 youbecause you're on 2005.I mightmake 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 bySSASNewBee Monday, September 28, 2009 6:25 PM
Unmarked As Answer bySSASNewBee Monday, September 28, 2009 6:26 PM
Marked As Answer bySSASNewBee Tuesday, September 29, 2009 4:19 PM

Tomislav Piasevoli Monday, September 28, 2009 6:20 AM

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 boththe 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

SSASNewBee 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 notsome 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

Tomislav Piasevoli Thursday, September 24, 2009 7:48 PM

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

Unmarked As Answer bySSASNewBee Thursday, September 24, 2009 9:55 PM
Marked As Answer bySSASNewBee Thursday, September 24, 2009 9:47 PM

SSASNewBee Thursday, September 24, 2009 9:36 PM

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 bySSASNewBee Monday, September 28, 2009 6:25 PM
Unmarked As Answer bySSASNewBee Monday, September 28, 2009 6:26 PM
Marked As Answer bySSASNewBee Tuesday, September 29, 2009 4:19 PM

Tomislav Piasevoli Monday, September 28, 2009 6:20 AM

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

SSASNewBee 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

Tomislav Piasevoli Sunday, October 25, 2009 7:32 AM

You can use google to search for other answers

Custom Search

More Threads

• Aggregating Data, and accessing it !

• How to generate Database from PDW cube

• Named Query for the Datasource View -- Concatenating two char fields

• Errors after changing an Analysis Services Data Source connection string in Management Studio

• Retrive all SQL during Cube processing

• usage-based optimization

• Basic "RANK" question: how to stop null records appearing.

• How to use SUM in MDX query for calculation

• Cumulative calculation on fact level

• SSAS and SQL instance