Skip to Content

Grand Total of two crosstabs in SAP Analysis for Excel

Hi All,

I am using two crosstabs in my report one below the other. I am showing the Total for each crosstab. I would like to show the grand total of the two crosstabs at the end. could someone please suggest how to achieve this?

total.jpg

Thanks

total.jpg (27.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jul 19, 2017 at 09:08 AM

    Hi - you should be able to use the Excel formulas if you are on 2.2/2.3/2.4 - please try that; if that doesn't work please share which version of Analysis Office you are using

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 19, 2017 at 01:39 PM

    Hi,

    Find out the last column and row of your crosstab by using VBA,

    Dim lCols, lRows as Long

    lCols = Range(“SAPCrosstab1”).Columns.Count

    lRows = Range(“SAPCrosstab1”).Rows.Count

    then display final total in next column and row.

    Regards,

    Anand

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 21, 2017 at 08:42 AM

    Hi,

    Please find the script :

    Sub Test()
    
    
    Dim TOTAL1, TOTAL2, GRANDTOTAL As String
    Dim lCols1, lRows1, lCols2, lRows2, lCols3, lRows3 As Integer
    
    
    'Get number of row and colums of crosstab
    
    
    lCols1 = Range("SAPTAB1").Columns.Count
    lRows1 = Range("SAPTAB1").Rows.Count
    lCols2 = Range("SAPTAB2").Columns.Count
    lRows2 = Range("SAPTAB2").Rows.Count
    
    
    'Display row numbers and column numbers of crosstab - -optional
    'MsgBox (lCols1 & " By " & lRows1)
    'MsgBox (lCols2 & " By " & lRows2)
    
    
    'Get actual position of the first total
    lRows1 = lRows1 + 1
    lCols1 = lCols1 + 1
    
    
    'Get actual position of the secound total
    
    
    lRows2 = lRows2 + 18
    lCols2 = lCols2 + 1
    
    
    TOTAL1 = Cells(lRows1, lCols1)
    TOTAL2 = Cells(lRows2, lCols2)
    
    
    'ADD tow total
    
    
    GRANDTOTAL = TOTAL1 + TOTAL2
    
    
    'Display Grandtotal -optional
    'MsgBox (GRANDTOTAL)
    
    
    'Get the position for grang total to be display
    
    
    lCols3 = lCols2
    lRows3 = lRows2 + 3
    
    
    Cells(lRows3, lCols3) = GRANDTOTAL
    
    
    End Sub
    test-vba.jpg
    Add comment
    10|10000 characters needed characters exceeded