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

Keyboard macros or custom scripts

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

Postby RogerDodge » Tue Sep 04, 2012 4:09 pm

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   Expand viewCollapse view
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
RogerDodge
 
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

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

Postby FJCC » Tue Sep 04, 2012 6:17 pm

I don't see a Position property at all. Here are the properties I see
Code: Select all   Expand viewCollapse view
(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   Expand viewCollapse view
  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?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby RogerDodge » Wed Sep 05, 2012 10:59 am

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
RogerDodge
 
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

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

Postby FJCC » Thu Sep 06, 2012 4:19 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby RogerDodge » Thu Sep 06, 2012 12:31 pm

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

Postby RogerDodge » Thu Sep 06, 2012 12:57 pm

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
RogerDodge
 
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

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

Postby RoryOF » Thu Sep 06, 2012 1:05 pm

Apache OpenOffice 4.1.10 on Xubuntu 20.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32974
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby RPG » Thu Sep 06, 2012 1:41 pm

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
RPG
Volunteer
 
Posts: 2209
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

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

Postby RogerDodge » Thu Sep 06, 2012 1:43 pm

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

Postby RogerDodge » Thu Sep 06, 2012 1:58 pm

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

Postby RogerDodge » Sat Sep 08, 2012 3:07 pm

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
RogerDodge
 
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm

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

Postby RPG » Sat Sep 08, 2012 5:08 pm

Hello

Maybe this code can help you to start.
Code: Select all   Expand viewCollapse view
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
RPG
Volunteer
 
Posts: 2209
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

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

Postby RogerDodge » Sat Sep 08, 2012 9:15 pm

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

Postby RogerDodge » Sat Sep 08, 2012 9:34 pm

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

Postby RogerDodge » Sun Sep 16, 2012 6:11 pm

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
RogerDodge
 
Posts: 10
Joined: Wed Jul 11, 2012 3:49 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 6 guests