Extending with the filter function

[destiny21]

Hello to all. I am in need of a filter function for the datapilot through the use of macro.

I am referring to extending the function of the datapilot macro show here : http://user.services.openoffice.org/en/ ... 8&p=146827

What I managed to find is using the following way to add a filter function but it did not work for me.

e.g--------------------------------------------------------
oFilters = oTDescriptor.getFilterDescriptor()
bFilterFields(0).Field = 61 ' first column
bFilterFields(0).IsNumeric = false ' numeric comparison
bFilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
bFilterFields(0).StringValue = "Y" ' value each row is to be compared with
oFilters.setFilterFields (bFilterFields())
-------------------------------------------------------------

Thanks in advance.

[FJCC]

Here is a modification of the code that adds a filter to the datapilot and then removes the filter. The attached file demonstrates the macro.

Code: Select all

Dim FilterFields(0) as New com.sun.star.sheet.TableFilterField
oController = ThisComponent.CurrentController
oSheetObj = oController.ActiveSheet
DataCellRange = oSheetObj.getCellRangeByName("A1:C10")
RangeAddress = DataCellRange.RangeAddress
Tables = oSheetObj.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

Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = False                   'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress)  			  
Fields = Descriptor.getDataPilotFields				
Field1 = Fields.getByIndex(0)   'The first column of the data range has index 0

'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW												

Field2 = Fields.getByIndex(1)
Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN	
Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.AVERAGE
Descriptor.RowGrand = "FALSE"   'Turn off the Total line of the Table
Cell = oSheetObj.getCellrangeByName("A15")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)
Table = Tables.getByName("NewDataPilot")
wait 2000
FDes = Table.getFilterDescriptor
FilterFields(0).Field = 0
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = True
FilterFields(0).NumericValue = 2
FDes.FilterFields = FilterFields
wait 2000
FDes.FilterFields = Array()

[destiny21]

Hey thanks i will try it out and provide a reply

[destiny21]

I am able to run your code but when i use it on my side it is having problems

The things that i changed are:
(Doc is the URL of the file i pass in ->starDeskTop.loadComponentFromUrl (UrlMY, "_blank", 0, Array()))

Sub DataPilot(Doc as Object)
oController = Doc.CurrentController
'
'
FilterFields(0).IsNumeric = False
FilterFields(0).StringValue = "Game"

I don't understand that why at your code, it is able to work but when i only modify a few info it become unable to filter. Thanks. really sorry to trouble

[FJCC]

I don't see an error in the few lines you posted. Do you get any kind of error message? Did you include the line

Code: Select all

Dim FilterFields(0) as New com.sun.star.sheet.TableFilterField

that is near the top of my code?
Could you post more of your code? You can use the Code button that is above the box where you type in a reply. Just paste your code between the

Code: Select all

 and 

symbols that appear in you message.

[destiny21]

Hi sorry for the late reply. here is the code

Code: Select all

Sub DataPilot(docName as Object,sheetName as String)
	Dim FilterFields(0) as New com.sun.star.sheet.TableFilterField
	'activateSheet("Temp")
	activateSheetNewDoc(docName,"Temp")
		
	oController = docName.CurrentController
	oSheetObj = oController.ActiveSheet
	
	rowNumber = goToBottom(0)
	
	if (rowNumber <> 65535) Then
	
		DataCellRange = oSheetObj.getCellRangeByName("A1:BK" & rowNumber+1)'10)
		RangeAddress = DataCellRange.RangeAddress
		Tables = oSheetObj.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
		
		Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
		Descriptor.ShowFilterButton = False                   'Don't show the Filter Button
		Descriptor.setSourceRange(RangeAddress)  			  
		Fields = Descriptor.getDataPilotFields			
		
		Field0 = Fields.getByIndex(39)'4)
		Field0.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
		Field0.Function = com.sun.star.sheet.GeneralFunction.SUM	
		
		Field4 = Fields.getByIndex(4)
		Field4.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
			
		Field1 = Fields.getByIndex(49)   'The first column of the data range has index 0
		
		'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField
		Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW												
		
		Field2 = Fields.getByIndex(24)
		Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW	
		Field3 = Fields.getByIndex(54)
		Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
		'Field3.Function = com.sun.star.sheet.GeneralFunction.SUM
		Descriptor.RowGrand = "TRUE"   'Turn on the Total line of the Table
		
		oSheetObj = docName.getSheets.getByName( sheetName )'thisComponent.getSheets.getByName( sheetName )
		Cell = oSheetObj.getCellrangeByName("B1")
		Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)

		FDes = Tables.getFilterDescriptor() '<=============================== Error at here
		FilterFields(0).Field = 0
		FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
		FilterFields(0).IsNumeric = False
		FilterFields(0).StringValue = "Game"
                FilterFields(1).Field = 0
		FilterFields(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
		FilterFields(1).IsNumeric = False
		FilterFields(1).StringValue = "Game2"
		FDes.FilterFields = FilterFields
		
	Else
		oSheetObj = docName.getSheets.getByName( sheetName )
		Cell = oSheetObj.getCellrangeByName("A1")
		Cell.String = "No Data Match for this Tab"
		Cell.CharWeight = com.sun.star.awt.FontWeight.BOLD
		Cell.CharHeight=12
	End If
End sub 

The error keeps appearing at here. "FDes = Tables.getFilterDescriptor()" thanks.

[destiny21]

Just in case, i have modified your code a little to have the output like my datapilot. By clicking the button, it shd be finding "qwer" text but it did not display it out.

As attached is the file. Thanks

[FJCC]

It turns out that the column labeling in the FilterDescriptor refers to the source data, not to the datapilot table. Therefore, the "company" column is Field 3. The working code is below.
The error you get is because you try to get the filter descriptor of Tables which is a collection of tables. You need to get the filter descriptor of a particular table

Code: Select all

FDes = Table.getFilterDescriptor()

Code: Select all

Sub DataPilot
Dim FilterFields(0) as New com.sun.star.sheet.TableFilterField
oController = ThisComponent.CurrentController
oSheetObj = oController.ActiveSheet
DataCellRange = oSheetObj.getCellRangeByName("A1:D10")
RangeAddress = DataCellRange.RangeAddress
Tables = oSheetObj.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

Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = TRUE                  'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress)  			  
Fields = Descriptor.getDataPilotFields				

Field0 = Fields.getByIndex(3)
Field0.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW

Field1 = Fields.getByIndex(0)   'The first column of the data range has index 0

'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField

Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW												

'Field2 = Fields.getByIndex(1)
'Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN	
Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.SUM
Descriptor.RowGrand = "FALSE"   'Turn off the Total line of the Table
Cell = oSheetObj.getCellrangeByName("A15")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)
Table = Tables.getByName("NewDataPilot")
wait 2000
FDes = Table.getFilterDescriptor
FilterFields(0).Field = 3
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = False
'FilterFields(0).NumericValue = 2
FilterFields(0).StringValue = "qwer"
FDes.FilterFields = FilterFields
wait 2000
FDes.FilterFields = Array()

End sub 

[destiny21]

Hey FJCC thanks. It is some what working. But i need to filter more than 1 info. But it is giving me error when i added in a new field

e.g

Code: Select all

FilterFields(0).Field = 3
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = False
'FilterFields(0).NumericValue = 2
FilterFields(0).StringValue = "qwer"

FilterFields(0).Field = 3
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = False
'FilterFields(0).NumericValue = 2
FilterFields(0).StringValue = "asdf"

FDes.FilterFields = FilterFields

Is this even possible, because i will be putting in more filtering conditions. and the type of filtering i want is shown in the image attached rather then only filter with one condition. I need it to be flexible.

[FJCC]

Sorry for the delayed reply. My computer died on Thursday and I've been busy trying to save my data and catch up at work. You can have a second filter condition using code something like

Code: Select all

FilterFields(0).Field = 3
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = False
'FilterFields(0).NumericValue = 2
FilterFields(0).StringValue = "qwer"

FilterFields(1).Field = 3
FilterFields(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(1).IsNumeric = False
FilterFields(1).StringValue = "asdf"
FilterFields(1).Connection = com.sun.star.sheet.FilterConnection.OR
FDes.FilterFields = FilterFields

I haven't checked this code, as I'm at my local library. The documentation for the Connection property can be found at these two links
http://api.openoffice.org/docs/common/r ... Field.html
http://api.openoffice.org/docs/common/r ... ction.html

[Villeroy]

All this (and much more) can be done with a database and Calc without macros or with a little bit of Basic glue.
http://user.services.openoffice.org/en/ ... 427#p96427

The screenshot outputSelection.JPG looks like a re-invention of the form based filter in Base.

[destiny21]

Hey Thks FJCC. I will try it out first. Thanks for helping even though you are busy with your stuff.

Villeroy thks for the links but i am more on working with grabbing data from files and compile them into pivot tables and from there will have a clear views of the various data.

[destiny21]

Hi FJCC, sad to say the code did not work. I think it is used for matching condition on various columns to get a output.

It did not work for the same field i guess. Is there any other way to solve it. Thanks.

[FJCC]

This code works for me

Code: Select all

Sub DataPilot2
Dim FilterFields(1) as New com.sun.star.sheet.TableFilterField
oController = ThisComponent.CurrentController
oSheetObj = oController.ActiveSheet
DataCellRange = oSheetObj.getCellRangeByName("A1:D10")
RangeAddress = DataCellRange.RangeAddress
Tables = oSheetObj.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

Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = TRUE                  'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress)             
Fields = Descriptor.getDataPilotFields            

Field0 = Fields.getByIndex(3)
Field0.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW

Field1 = Fields.getByIndex(0)   'The first column of the data range has index 0

'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField

Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                    

'Field2 = Fields.getByIndex(1)
'Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN   
Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.SUM
Descriptor.RowGrand = "FALSE"   'Turn off the Total line of the Table
Cell = oSheetObj.getCellrangeByName("A15")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)
Table = Tables.getByName("NewDataPilot")
wait 2000
FDes = Table.getFilterDescriptor
FilterFields(0).Field = 3
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).IsNumeric = False
'FilterFields(0).NumericValue = 2
FilterFields(0).StringValue = "qwer"

FilterFields(1).Field = 3
FilterFields(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(1).IsNumeric = False
FilterFields(1).StringValue = "asdf"
FilterFields(1).Connection = com.sun.star.sheet.FilterConnection.OR

FDes.FilterFields = FilterFields
wait 2000
FDes.FilterFields = Array()

End sub