BPC 10 EPM Local Member Priority

[Former Member]

Hello Experts,

I had a quick Question.  I have a report with Local Members in the Columns that do a simple x*y.  I also have a Local member on the Last Row Called Total that sums up all members above.  It is Member specific to Entity because the number of entities could change.  The issue I have is when the Local Members meet, the Column LM takes priorty over the Row LM, which is causing errors in my calculations.  I want the Total (Row LM) to take Priority and I have been unable to figure it out.  Any advice would be helpful! 

Thanks,

Sean

[Former Member]

Anyone have any Ideas?  Thanks,

[JohnL]

Hi Sean,

Have you tried just ordering the local members differently in the local member list?

Thanks,

John

[Former Member]

Hi John,

I did try to move the member order around, no luck.

Thanks,

Sean

[JohnL]

Hi Sean,

In the documentation it mentions that in case of conflicts between local members on row and column axis, local members on the columns axis overrides thes ones on the row axis.

Thanks,

John

[Former Member]

Thanks John,

You are correct, the column took priority no matter what I did.  I was able to write some VBA to insert a subtotal line.  It is workign now.  Thanks for your input.

Sean

[Former Member]

Hi Sean

Could you please share your code, I need something similar, I need to do a subtotal per column, my column is a local member.

thanks in advance

Regards

[Former Member]

HI Lucio,

Sure, here is a sample of my code. I repeated it for each column I needed a subtotal on.  I used LastRow because my report can change in length.  Hope this helps.

Sean

Dim RowTP As Long
    Dim LastRow As String
   
    Dim TotalValue As Long
    Dim TempLen, RowToWrite As Integer
    ' Loop for all CCs - starting in row 10
    'RowTP = 10
LastRow = Cells(Rows.Count, "C").End(xlUp).Row

        'Do
       
        ' RowTP = RowTP + 1
         'RowToWrite = RowTP
         ActiveSheet.Cells(LastRow + 1, 2) = "Total"
         ActiveSheet.Cells(LastRow + 1, 2).Font.Bold = True
         ActiveSheet.Cells(LastRow + 1, 2).HorizontalAlignment = xlHAlignRight
         ActiveSheet.Cells(LastRow + 1, 2).Borders.ColorIndex = 2
        
            ActiveSheet.Cells(LastRow + 1, 4).Formula = "=Sum(D10:D" & LastRow & ")"
            ActiveSheet.Cells(LastRow + 1, 4).NumberFormat = "##,###,###"
          
           ActiveSheet.Cells(LastRow + 1, 6).Formula = "=Sum(F10:F" & LastRow & ")"
           ActiveSheet.Cells(LastRow + 1, 6).NumberFormat = "##,###,###"