Skip to Content
0

Grand Total of two crosstabs in SAP Analysis for Excel

Jul 19, 2017 at 08:42 AM

178

avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Tammy Powlas
Jul 19, 2017 at 09:08 AM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi- I am on 2.2 version.

I have used an excel formula but the values are coming up as a separate column i.e, summing up each Resource Cost and each Fund Cost. I was not able to achieve the overall cost(Resource Cost Total + Fund Cost Total)

Also. both the tables are dynamic,the rows count keeps changing.

0

Which SP of 2.2? Older versions of 2.2 had issues with formulas; please update to the latest patch

0

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.

0
Anand Kumar Jul 19, 2017 at 01:39 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Anand,

But we need to initially place the formula in one cell. Could you please tell me where I can insert the forumla for grand total of two crosstabs.

0

hi,

You have to use VBA.

Get the total of the two cross tab and add the two total to get grand total then place the grand total in cell by using VBA script.

Regards,

Anand

0

Could you pls help with the script to achieve this.

0
Anand Kumar Jul 21, 2017 at 08:42 AM
0

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

test-vba.jpg (126.0 kB)
Show 8 Share
10 |10000 characters needed characters left characters exceeded

Hi Anand,

Thank you for the detailed code.

I placed this code in a separate module and saved it. I get values, but when I run the next time, the placing of the grand total is not changing dynamically. It is showing in the cell where I placed for the first time. Could you please tell what I am missing.

Is there anything that I have to initialize in "This workbook" ?

Thanks

0

hi,

can you post your code and screen shots of the total.

Regard,

Anand

0

Hi Anand,

Below are the steps followed.

Step 1: Execute the report for a particular selection.(Output before adding code ) Cross tab 1 is inserted on A1 and Cross tab 2 is inserted from A10000)

Step2: Alt+F11 and insert a new module and use the given code(after.jpg) I get the correct output.

Step3: I change the selection and run the report. I still get the same grand total and also in the same cell it appeared for the first time. The value and the cell is not changing dynamically. ( same-result.jpg)

Code used.:

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("SAPCROSSTAB1").Columns.Count
lRows1 = Range("SAPCROSSTAB1").Rows.Count
lCols2 = Range("SAPCROSSTAB2").Columns.Count
lRows2 = Range("SAPCROSSTAB2").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 + 10000
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 - 1
lRows3 = lRows2 + 3
Cells(lRows3, lCols3) = GRANDTOTAL
End Sub
after.jpg (97.0 kB)
same-result.jpg (61.9 kB)
before.jpg (121.3 kB)
0

Hi,

Make this change in the code and check the result :

Dim TOTAL1, TOTAL2, GRANDTOTAL As Double

Try to debug and check the debug line by line with result.

Regards,

Anand

0

Hi,

Made the change and its the same.When I run the report for a different selection, grand total value is not changing,

While debugging I did not come across any error message.

0

Hi,

Did you create a button in workbook and assign macro to the button?

macro.jpg

Regards,

Anand

macro.jpg (27.9 kB)
0

I haven't created a button as I was assuming that the code will work dynamically without any additional clicks.

Could you please suggest if that Is doable?

0

code will not work dynamically. You have to create additional macro to run it dynamically.

0