[Solved] Data Pilot Tables - setting the 'Position' property

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

[Solved] Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Open Office.org 3.4.1 (4.4.0)
Windows 7 Home Professional
Problem as Title

Hi
The attached code takes source data from a fixed columns, variable rows table and writes a DP table to another sheet. The DP layout info is held in a named range (Weekly Averages) and is used to set the Orientation, Function and Position properties within the DP table.
The code works fine until I try to set the Position of the field within the DP table.
Cannot find any reference within documentation except in Developer Guide which shows how to do this in Java but not OO Basic.
Can anyone see where I am going wrong?
Thanks in advance
P.S. If my code is naive, am fairly new to OO basic!

Code: Select all

REM  *****  BASIC  *****

Sub DataPilot
 Dim Doc as Object
 Dim SourceSheet As Object
 Dim DestinationSheet As Object
 Dim DataCellRange As Object
 Dim RangeAddress As Object
 Dim Tables As Object
 Dim Descriptor As Object
 Dim Fields As Object
 Dim Field As Object
 Dim LayoutCellRange As Object
 
 loadingLibraries 'load xray
 
 Doc = ThisComponent
 ' Reference sheets
 SourceSheet = Doc.Sheets.getByName("Data")
 DestinationSheet = Doc.Sheets.getByName("Pivot")
 LayoutSheet = Doc.Sheets.getByName("Layouts") ' 4 rows-FieldNames-Orientation-Function-Position
 ' Size of source data table
 rows = Ubound(SourceSheet.Data, 1)
 cols = Ubound(SourceSheet.Data(0),1)
 ' Layout data
 LayoutCellRange = LayoutSheet.getCellRangeByName("WeeklyAverages")

 DataCellRange = SourceSheet.getCellRangeByPosition(0, 0, cols, rows)
 RangeAddress = DataCellRange.RangeAddress
 Tables = DestinationSheet.DataPilotTables()   'Tables has all the DataPilot Tables in the Active Sheet

 'This part of the code just removes the table if it already exists. Prevents error from running the code several times
 'If Tables.hasByName("NewDataPilot") THEN  
 '	Tables.removeByName("NewDataPilot")
 'End If
 ' Just delete the lot ' heavy
 PilotEnumeration = Tables.createEnumeration
 While PilotEnumeration.hasMoreElements()
	Tables.removeByName(PilotEnumeration.nextElement.getName) ' = ParagraphEnumeration.nextElement()
 Wend
 
 Descriptor = Tables.createDataPilotDescriptor()    'Descriptor contains the description of a DataPilot Table
 Descriptor.ShowFilterButton = False                'Don't show the Filter Button
 Descriptor.setSourceRange(RangeAddress)  			'Set source data range	
 Descriptor.RowGrand = "FALSE"   					'Turn off the Total line of the Table
 Descriptor.IgnoreEmptyRows = "TRUE" 				'Ignore empty rows
 'xray descriptor
 			  
 Fields = Descriptor.getDataPilotFields				'Get the DP fields
 'xray fields
 
 For colNo = 0 To Fields.Count - 2
 	Field = Fields.getByIndex(colNo)
 	'XRay Field
 	tmp = LayoutCellRange.getCellByPosition(colNo, 1).getValue() 
 	Field.Orientation = LayoutCellRange.getCellByPosition(colNo, 1).getValue() 
 	Field.Function = LayoutCellRange.getCellByPosition(colNo, 2).getValue()
 	'Works to here
 '!! How do I set the 'Position' property?!!
 	' ?Field.setPropertyValue("Position",  LayoutCellRange.getCellByPosition(colNo, 3).getValue() )
 	' ?this = Field.getPropertyValue("Position")
 	'Field.Position = LayoutCellRange.getCellByPosition(colNo, 3).getValue()
 Next
 
 Cell = DestinationSheet.getCellrangeByName("A1")
 Tables.insertNewByName("WeeklyAverages", Cell.CellAddress, Descriptor)
End sub
Last edited by RogerDodge on Sun Sep 16, 2012 6:13 pm, edited 2 times in total.
OpenOffice 3.4 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Data Pilot Tables - setting the 'Position' property

Post by FJCC »

I don't see a Position property at all. Here are the properties I see

Code: Select all

(Name)                 (Value Type)                       (Value)              (Info.)   (Attr.)    (Handle)  
AutoShowInfo           .sheet.DataPilotFieldAutoShowInfo  -void-                         Maybevoid    0  
Function               .sheet.GeneralFunction             NONE                                        0  
GroupInfo              .sheet.DataPilotFieldGroupInfo     -STRUCT-                       Maybevoid    0  
HasAutoShowInfo        boolean                            False                                       0  
HasLayoutInfo          boolean                            False                                       0  
HasReference           boolean                            False                                       0  
HasSortInfo            boolean                            False                                       0  
ImplementationId       []byte                             -SEQUENCE-           Pseud     Read_Only       
ImplementationName     string                             ScDataPilotFieldObj  Pseud     Read_Only       
IsGroupField           boolean                            False                                       0  
Items                  .container.XIndexAccess            -INTERFACE-          Pseud     Read_Only       
LayoutInfo             .sheet.DataPilotFieldLayoutInfo    -void-                         Maybevoid    0  
Name                   string                             Data                 Pseud                     
Orientation            .sheet.DataPilotFieldOrientation   HIDDEN                         Maybevoid    0  
PropertySetInfo        .beans.XPropertySetInfo            -INTERFACE-          Pseud     Read_Only       
Reference              .sheet.DataPilotFieldReference     -void-                         Maybevoid    0  
SelectedPage           string                             ""                                          0  
ShowEmpty              boolean                            True                                        0  
SortInfo               .sheet.DataPilotFieldSortInfo      -void-                         Maybevoid    0  
Subtotals              [].sheet.GeneralFunction           -SEQUENCE-                                  0  
SupportedServiceNames  []string                           -Sequence-           Pseud     Read_Only       
Types                  []type                             -Sequence-           Pseud     Read_Only       
UseSelectedPage        boolean                            False                                       0  
The above properties belong to oObj_3 from this code.

Code: Select all

  oSheets = oInitialTarget.Sheets
  oObj_1 = oSheets.getByIndex(0)
  oDataPilotTables = oObj_1.getDataPilotTables()
  
  oObj_2 = oDataPilotTables.createDataPilotDescriptor()
  oDataPilotFields = oObj_2.getDataPilotFields()
  oObj_3 = oDataPilotFields.getByIndex(0)
What are you trying to setting with Position?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Thanks for your interest.
I think the best way to answer is if I may refer you to the developers guide pages 983 and 990/991, but in summary
Orientation property determines whether the DP field appears as a ROW or COLUMN (or HIDDEN or DATA) dimension.
Function property specifies the function of a DATA dimension (SUM, COUNT,AVERAGE etc.)
The Position property determines the order of the fields in the ROW or COLUMN dimensions.
Setting Orientation and Function (in the for loop of attached code) works fine, Position eludes me.
I can find no reference to a Position property using XRay tool on the DP Field.
There is an example similar to what I am trying to do using Java on pages 990/991 but I cant get similar to work in Basic
OpenOffice 3.4 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Data Pilot Tables - setting the 'Position' property

Post by FJCC »

I found the code you mentioned in the Developer's Guide, but I cannot find the Position property of the dimensions. I'll keep looking at this as I have time, but I'm stumped at the moment.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

A bit of closer reading and the following is my next avenue of investigation.
i.e. obtain the com.sun.star.sheet.DataPilotSourceDimension service
The com.sun.star.sheet.DataPilotSourceDimension service contains a
com.sun.star.beans.XPropertySet interface that is used for the following properties of
a dimension:
 Original (read-only) contains the dimension object from which a dimension was
cloned, or null if it was not cloned. A description of the
com.sun.star.util.XCloneable interface is described below.
 IsDataLayoutDimension (read-only) must contain true if the dimension is the "data
layout" dimension, otherwise false.
 Orientation controls how a dimension is used in the DataPilot table. If it contains
the com.sun.star.sheet.DataPilotFieldOrientation enum values COLUMN or ROW, the
dimension is used as a column or row dimension, respectively. If the value is DATA,
the dimension is used as data (measure) dimension. The PAGE designates a page
dimension, but is not currently used in OpenOffice.org API Calc. If the value is
HIDDEN, the dimension is not used.
 Position contains the position of the dimension within the orientation. This
controls the order of the dimensions. If a dimension's orientation is changed, it is
added at the end of the dimensions for that orientation, and the Position property
reflects that position.
 Function specifies the function that is used to aggregate data for a data
dimension.
 UsedHierarchy selects which of the dimension's hierarchies is used in the
DataPilot table. See the section on hierarchies below.
 Filter specifies a list of filter criteria to be applied to the source data before
processing. It is currently not used by OpenOffice.org API Calc.
OpenOffice 3.4 on Windows 7
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Have looked at this and I am stuck to find from where I obtain a reference to
com.sun.star.sheet.DataPilotSourceDimension service
OpenOffice 3.4 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Data Pilot Tables - setting the 'Position' property

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Data Pilot Tables - setting the 'Position' property

Post by RPG »

Hello

Are you aware of the function getpivotdata?
In this link you can find an explanation.
http://wiki.openoffice.org/wiki/Documen ... TPIVOTDATA

I don't know if it is good to use and also maybe it does not work for your purpose.

Getpivotdata is not easy to use but maybe less difficult then macros.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Big help!
Now I must get to grips with API Docs
Can you give me a one liner as to 'instantiate' the aforementioned service?
TKS
OpenOffice 3.4 on Windows 7
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

TKs RPG
I want explicit control over setting up the DP table fields that I think can only be done using macro. Will have another look at getPivotData but it may be a bit general for what I aspire to do
Ever moving forward
Roger
OpenOffice 3.4 on Windows 7
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Am still at a loss to how to instantiate the service. Tried things like this
dim oFA as object
oFA = createInstance( "com.sun.star.sheet.DataPilotSourceDimension" )
Intuitively it dont seem right. This must be a fairly standard thing to do. If i can get over this, issue should (I hope) be [SOLVED}
OpenOffice 3.4 on Windows 7
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Data Pilot Tables - setting the 'Position' property

Post by RPG »

Hello

Maybe this code can help you to start.

Code: Select all

sub proef
dim oDoc,oSheets
dim oSheet
dim oDataTable
oDoc=thiscomponent
'mri oDoc
oSheets=oDoc.Sheets
oSheet=oSheets.getbyname("Data")
oDataTable=oSheet.DataPilotTables

dim oTable ,otableRow
oTable= oDataTable.createEnumeration 
do while oTable.hasmoreelements
otableRow=oTable.NextElement
print otableRow.name
loop
end sub
When you start with macro it is important to understand how to work with the exported services by the object. You object seems me a spreadsheet document.
There I have never worked with macros for a datapilot I can tell not much more.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

Tks
Have to instantiate the service and then set the properties. No other way to do it as far as I can see. How to I get a reference to this remains the question. How do I Do this?
This must be a standard kind of thing to do. Don;t think its in any way specific to PataPilpt
OpenOffice 3.4 on Windows 7
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

meant Data Pilot
OpenOffice 3.4 on Windows 7
RogerDodge
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

Re: Data Pilot Tables - setting the 'Position' property

Post by RogerDodge »

The solution is to set up the dimension fields in the order required rather than try to change the field ordering after.
This is ok as I create the DP from scratch each time rather then modifying an existing DP table.
Generating a new table is more or less instantaneous.
If you iterate over existing DP tables and .refresh each one it takes ages (I found over 1 minute to refresh 16 DP tables on a fairly quick machine) .
Clearly I have a lot more reading to do.
Tks to the forum for your help.
OpenOffice 3.4 on Windows 7
Post Reply