OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Datapilot Reference Type

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
GOrias
Newbie
Newbie


Joined: 21 Feb 2012
Posts: 2
Location: Santaigo, Chile

PostPosted: Tue Feb 21, 2012 9:54 am    Post subject: [SOLVED] Datapilot Reference Type Reply with quote

Hello all

At work we have recently moved from MSOffice to Open Office and I'm still trying to get used to programming my macros for Calc.

What I mostly use are Datapilots and though I can already work with the basics, I'm having troubles to present the Data as "Row Percentage" or "Column Percentage" which is how I usually have to present my reports.

I found references to a ReferenceType "struct" but the how to use these "structs" eludes me. Can somebody please help me to find examples?

This is a sample code I'm using:


Code:

Sub mypivot
   
'Define objects   
   Document = ThisComponent
    AllSheets = Document.getSheets()
    a = allsheets.count-1 'sheet number
    MySheet = AllSheets.getByIndex(a)
   
   AllSheets.removeByName( "Tablas") 'Deletes the sheet with the datapilot
      
    oDestAddress = ThisComponent.sheets.insertNewbyName("Tablas", ThisComponent.Sheets.getCount())
    oDPTables = ThisComponent.getSheets().getByIndex(0).getDataPilotTables()     
'   Remove the DataPIlot if it  is already created
    'oDPTables.RemoveByName("DataPilot1")

'  Setup various parameters
    oDataP = ThisComponent.getSheets().getByName("database").getDataPilotTables().createDataPilotDescriptor() 
    oDataP.setPropertyValue("IgnoreEmptyRows",1)  =1     
    oDataP.setPropertyValue("ShowFilterButton",0) = 0
    oDataP.setProperTyValue("RowGrand",0) = 0

' Set the DataSource range    - First & Last Column  - Rows 1 to 2521
    Dim aSourceAddress As New com.sun.star.table.CellRangeAddress
    aSourceAddress.Sheet =0
    aSourceAddress.StartColumn = 0
    aSourceAddress.StartRow = 0
    aSourceAddress.EndColumn = 81
    aSourceAddress.EndRow = 2520
    oDataP.setSourceRange(aSourceAddress)
     
'Defines and sets up the fields
    oFields = oDataP.getDataPilotFields()
     
    oField0 = oFields.getbyindex(4)
   oField0.SetPropertyValue("ShowEmpty",0) = 0
    oField0.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
   
   
    oField1 = oFields.getbyindex(0)
    oField1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
    oField1.Function = com.sun.star.sheet.GeneralFunction.COUNT      'This data I need to display as Row Percentage
   oField1.SetPropertyValue("ShowEmpty",0) = 0
   
   oField2 = oFields.getbyindex(72)
   oField2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
   oField2.SetPropertyValue("ShowEmpty",0) = 0    

' Setup the destination address
   Dim aDestAddress As New com.sun.star.table.CellAddress   
   aDestAddress.Sheet = a       'Hoja de Destino
    aDestAddress.Column = 0     'Columna de Destino
    aDestAddress.Row = 0      'Fila de Destino

    oDPTables.insertNewByName("DataPilot1", aDestAddress, oDataP)

End Sub


Thank you in advance


Last edited by GOrias on Tue Feb 21, 2012 1:32 pm; edited 1 time in total
Back to top
View user's profile Send private message
GOrias
Newbie
Newbie


Joined: 21 Feb 2012
Posts: 2
Location: Santaigo, Chile

PostPosted: Tue Feb 21, 2012 1:04 pm    Post subject: Reply with quote

In the end it was easier than I thought. I am a self taught "programmer" and I learned by trial and error in Excel, so I am not familiarized with other forms of programming, and so I am learning to program here.

For anyone who might be interested, the end code is as follows. The fix is in lines 44 to 48:

Code:

Sub mypivot
   
'Define objetos   
   Document = ThisComponent
    AllSheets = Document.getSheets()
    a = allsheets.count-1 'nĂºmero de hoja
    MySheet = AllSheets.getByIndex(a)
   
   AllSheets.removeByName( "Tablas")
      
    oDestAddress = ThisComponent.sheets.insertNewbyName("Tablas", ThisComponent.Sheets.getCount())
    oDPTables = ThisComponent.getSheets().getByIndex(0).getDataPilotTables()     
'   Remove the DataPIlot if it  is already created
    'oDPTables.RemoveByName("DataPilot1")

'  Setup various parameters
    oDataP = ThisComponent.getSheets().getByName("database").getDataPilotTables().createDataPilotDescriptor() 
    oDataP.setPropertyValue("IgnoreEmptyRows",1)  =1     
    oDataP.setPropertyValue("ShowFilterButton",0) = 0
    oDataP.setProperTyValue("RowGrand",0) = 0
    oDataP.setProperTyValue("ColumnGrand",0) = 0

' Set the DataSource range    - First & Last Column  - Rows 1 to 2521
    Dim aSourceAddress As New com.sun.star.table.CellRangeAddress
    aSourceAddress.Sheet =0
    aSourceAddress.StartColumn = 0
    aSourceAddress.StartRow = 0
    aSourceAddress.EndColumn = 81
    aSourceAddress.EndRow = 2520
    oDataP.setSourceRange(aSourceAddress)
     
'Define y setup fields
    oFields = oDataP.getDataPilotFields()
     
    oField0 = oFields.getbyindex(4)
   oField0.SetPropertyValue("ShowEmpty",0) = 0
    oField0.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
   
   oField1 = oFields.getbyindex(0)
    oField1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
    oField1.Function = com.sun.star.sheet.GeneralFunction.COUNT      
   oField1.SetPropertyValue("ShowEmpty",0) = 0
   'struct to display the data as row percentage
   Dim oRT as New com.sun.star.sheet.DataPilotFieldReference
   oRT.ReferenceType = 5
   oField1.Reference = oRT
   
   
   oField2 = oFields.getbyindex(72)
   oField2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
   oField2.SetPropertyValue("ShowEmpty",0) = 0    

' Setup the destination address
   Dim aDestAddress As New com.sun.star.table.CellAddress   
   aDestAddress.Sheet = a       'Destination Sheet
    aDestAddress.Column = 0     'Destination Column
    aDestAddress.Row = 0      'Destination Row

    oDPTables.insertNewByName("DataPilot1", aDestAddress, oDataP)

End Sub
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group