i wrote a macro in oooBasic to create a simple pivot, but i'am not able to put subtotals in top of each item and put the empty row under each subtotal.
(manually right click on a pivot, edit layout, doubleclick on the first row field, Option, layouts TOP and check "Empty line after each item")
I would like to set in the first field the LayoutMode to "OUTLINE_SUBTOTALS_TOP" and set the "AddEmptyLines", but i'm stuck here: http://www.openoffice.org/api/docs/comm ... tInfo.html
Can someone help me ?
Thank you
Code: Select all
Sub Main
'delete previus pivot
Tables = ThisComponent.Sheets(0).DataPilotTables()
PilotEnumeration = Tables.createEnumeration
While PilotEnumeration.hasMoreElements()
Tables.removeByName(PilotEnumeration.nextElement.getName)
Wend
oDataP = ThisComponent.Sheets(0).getDataPilotTables().createDataPilotDescriptor()
Dim aSourceAddress As New com.sun.star.table.CellRangeAddress
aSourceAddress.Sheet = 0
aSourceAddress.StartColumn = 0
aSourceAddress.StartRow = 0
aSourceAddress.EndColumn = 2
aSourceAddress.EndRow = 11
oDataP.setSourceRange(aSourceAddress)
oFields = oDataP.getDataPilotFields()
oField1 = oFields.getbyindex(0)
oField1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
'enable subtotals
Dim gFSeqAuto(0)
gFSeqAuto(0)=com.sun.star.sheet.GeneralFunction.AUTO
oField1.Subtotals = gFSeqAuto
oField2 = oFields.getbyindex(1)
oField2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
oField3 = oFields.getbyindex(2)
oField3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
oField3.Function = com.sun.star.sheet.GeneralFunction.SUM
Dim aDestAddress As New com.sun.star.table.CellAddress
aDestAddress.Sheet = 0
aDestAddress.Column = 0
aDestAddress.Row = 15
ThisComponent.Sheets(0).DataPilotTables.insertNewByName("DataPilot", aDestAddress, oDataP)
End Sub