### 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)
10|10000 characters needed characters exceeded

• 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

10|10000 characters needed characters exceeded
• Poojitha Sukumaran Tammy Powlas

Hi Tammy,

I have got the formula working to get the Grand Total,but I am unable to identify and place the formula in the last cell below Table2 since the data is dynamic.

• 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

10|10000 characters needed characters exceeded
• Could you pls help with the script to achieve this.

• Jul 21, 2017 at 08:42 AM

Hi,

```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)

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

test-vba.jpg (126.0 kB)