| View previous topic :: View next topic |
| Author |
Message |
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
 Posted: Thu Nov 08, 2007 1:11 pm Post subject: |
 |
|
bump...
can someone please help me out on this... |
|
| Back to top |
|
 |
AGran
Newbie
Joined: 09 Nov 2007
Posts: 1
Location: Aachen, Germany
|
| Posted: Fri Nov 09, 2007 7:48 am Post subject: |
|
|
Hi,
i have exactly the same problem as you. no idea why the reports ignore the filter settings of the querys. I use the following to as a workaround. Thats quite a kludge, but sofar the cleanest solution for me.
| Code: |
Sub gerateInRaum
sCommand = "SELECT #Geräte#.#Barcode#, #Geräte#.#Marke#, #Geräte#.#Modell#, #Geräte#.#Beschreibung#,"+ _
" #Raum#.#Name# AS #Raum#, FROM #Geräte#, #Raum# WHERE (( #Geräte#.#RaumID# = #Raum#.#RaumID# ) " + _
" AND ( #Geräte#.#Ausgemustert# = False )) AND (#Raum#.#Name# = '" + _
ThisComponent.DrawPage.getForms.getByIndex(0).getByName("RaumBox").Text + _
"') ORDER BY #Raum# ASC, #Geräte#.#Barcode# ASC"
showReportFilter("Geräte in Raum", sCommand)
End sub
Sub showReportFilter(formName,command)
Dim prop(2) as New com.sun.star.beans.PropertyValue
Dim dbContext As Object
Dim oDataSource As Object
dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = dbContext.GetByName("Inventar")
oReports = oDatasource.DatabaseDocument.ReportDocuments
oQuery = oDatasource.QueryDefinitions.getByName("tempMacroQuery")
oQuery.command = Replace(command,"#",Chr(34))
rem Load report
prop(0).Name="ActiveConnection"
prop(0).Value=oDataSource.getConnection("","")
prop(1).Name="OpenMode"
prop(1).Value="open"
prop(2).Name = "MacroExecutionMode"
prop(2).Value = com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE_NO_WARN
oDoc = oReports.loadComponentFromURL(formName,"_blank",63,prop())
End sub
Function Replace(Source As String, Search As String, NewPart As String)
Dim Result As String
Dim StartPos As Long
Dim CurrentPos As Long
Result = ""
StartPos = 1
CurrentPos = 1
If Search = "" Then
Result = Source
Else
Do While CurrentPos <> 0
CurrentPos = InStr(StartPos,Source,Search)
If CurrentPos <> 0 Then
Result = Result + Mid(Source, StartPos, _
CurrentPos - StartPos)
Result = Result + NewPart
StartPos = CurrentPos + Len(Search)
Else
Result = Result + Mid(Source, StartPos, Len(Source))
End If ' Position <> 0
Loop
End If
Replace = Result
End Function
|
So basically I created a query that my showReportFilter function modifies on the fly with a given command. drawback is that the whole query command must be specified in the calling function. (one could stack several queries to keep that simple, of course). As I find it extremly ugly to use the Chr() funtion to print a " (no idea why oobasic doesn't allow escaping..), I use # and the a replace method.
HTH
Alex |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Fri Nov 09, 2007 7:51 am Post subject: |
|
|
Because the report reads the query from the file anew - after you set the new filter you must save it...that's all.
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
| Posted: Fri Nov 09, 2007 7:58 am Post subject: |
|
|
AGran, thanks for the post... I may try this as an alternative if I cannot get the 'save-method' to work properly.
Drew, so you are saying - once I set the filter property of the query -- I will need to save it.
Is the save attribute/command available from the BASIC macro language?
I will x-ray the query in the meantime to explore... |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Fri Nov 09, 2007 8:00 am Post subject: |
|
|
yes - sorry, right now I have to actually do some work...damn this real life...LOL...anyway, you can find an example on the forum...don't have the link right handy, but if I get freed up in the next little bit I'll dig it out for you...
Sorry, I'm getting old or I would just rattle the syntax right off the top of my head..
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
| Posted: Fri Nov 09, 2007 8:36 am Post subject: |
|
|
I guess I'll wait to see your method...
I can't find the method or property from within x-ray...
it's strange because the 'preview' of the query in BASE document view will change the thumbnail information of what's included in the filtered query whenever I apply the filter from within the BASIC macro... but for some reason the report still does not reflect this updated query. |
|
| Back to top |
|
|
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
| Posted: Sun Nov 11, 2007 6:12 pm Post subject: |
|
|
bump...
does anyone have a few mins to help solve this problem... |
|
| Back to top |
|
|
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
| Posted: Tue Nov 27, 2007 11:27 am Post subject: |
|
|
bump...
yet again... anyone with some experience know where my problem might reside? |
|
| Back to top |
|
|
poovs
General User
Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX
|
| Posted: Mon Dec 03, 2007 7:44 am Post subject: |
|
|
...
workaround found...
applying of filter only seems to affect the display results of the query...
the filtered query results are not populated into the report...
when changing to an actual sql COMMAND versus a FILTER, the report shows the correct data.
wish I could have had this pointed out to me earlier... |
|
| Back to top |
|
|
|
