I am trying to create a Spreadsheet that pulls data from an MSSQL database, based on certain criteria held in two cells on the same Spreadsheet.
The cells contain a 'from date' and a 'to date'.
I have created a query in Base that works - it effectively pulls out all records from the DB, where the Date is between two specified dates e.g.
Code: Select all
select * from "AttendanceData" where "Date" >= '2018-01-01' and "Date" <= '2018-06-30'
Code: Select all
select * from "AttendanceData" where "Date" >= :d1 and "Date" <= :d2
I am trying to set the sheet up so that it can automatically populate the :d1 and :d2 from two cell values in the spreadsheet.
The date cells themselves are generated by the user selecting a 'period' from a validated list e.g. user selects 'Week 1' from the list and the two dates are vlookups to a small table within the spreadsheet, containing the 'period' in column 1, the 'from date' in column 2 and the 'to date' in column 3.
So, I have the query with the variables, I have the date cells, just no way to join the 2 together...
I have gone down a number of rabbit holes with this one, trying to find a simple way to have the query pull the variables from cell values but it just seems to be getting more complex - besides the simple solution I'm looking for, I've also looked at putting the variables and sql statement into a macro (couldn't quite get this working as I was unable to 'populate' the spreadsheet with the sql statement results - it seemed to only populate 1 cell) and I am currently looking at forms/controls based on these posts:
http://ooo-forums.apache.org/en/forum/v ... 6&p=416321
viewtopic.php?f=61&t=17876
I have managed to replicate the work done on the 'Report' tab from the first post, using my own MSSQL database (even though I don't need 4 controls, I have created them anyway as 'EmployeeName', 'Branch', 'DateFrom' and 'DateTo'), but I cannot quite get it to work - I have 2 small issues:
1) My drop down lists are often 'greyed out' for a few minutes, but do eventually come back to life with their contents (the list is a 'Grouped By' list - I'm guessing I need to make a smaller, non grouped list)
2) Sometimes, my Date Control Drop Downs do not bring up the calendar selection. Sometimes they work fine.
My main issue though, is a showstopper:
1) My 'Filter' Table (also held in the MSSQL DB) is not updating when I fill out the form and click away from the form using the 'OK' button (tied to a seperate 'Dummy' form).
Ultimately, I'd like to simply use the cell values to populate the query and that is the solution I am looking for. However, I am open to other methods i.e. forms/macros etc
I can post my work if needs be, but I'll need to anonymise it first, so just posting this request for help before I do that.
For reference though, the macro I was trying, looks like this (hopefully you'll get the idea):
Code: Select all
Sub Connect2
sheets = ThisComponent.Sheets
sheet1 = sheets.getByIndex(0)
cellC1 = sheet1.getCellByPosition(2,0)
cellC1Text = cellC1.String
cellD1 = sheet1.getCellByPosition(3,0)
cellD1Text = cellD1.String
Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
DATABASE_NAME = "Attendance"
Db = Context.getByName(DATABASE_NAME)
Conn = Db.getConnection("","")
strSQL = "SELECT ""Payweek"", ""EmployeeName"", ""EmployeeID"", SUM( ""StdHoursTotal"" ) AS ""StdHoursTotal"", SUM( ""OTHoursTotal"" ) AS ""OTHoursTotal"" FROM ""Attendance"".""dbo"".""AttendanceData"" WHERE ( ""PayWeek"" BETWEEN '" & cellC1Text & "' AND '" & cellD1Text & "' ) GROUP BY ""PayWeek"", ""EmployeeName"", ""EmployeeID"" ORDER BY ""EmployeeID"""
msgbox strSQL
Stmt = Conn.createStatement()
REM This allows forward & back scrolling & the necessary checking of 'isBeforeFirst'
Stmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
Result = Stmt.executeQuery(strSQL)
Result.next
outputValue = Result.getDouble(1)
MsgBox Result.getDouble(1)
MsgBox DATABASE_NAME
MsgBox cellC1Text & " - " & cellD1Text
outputCell = sheet1.getCellByPosition(0,4)
outputCell.String = outputValue
End Sub