[Solved] Calc MSSQL Query with Variables from Cell Values

[beersuser]

Hi,
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'

To create this with variables, I have used the query:

Code: Select all

select * from "AttendanceData" where "Date" >= :d1 and "Date" <= :d2

This prompts me for the two dates, which I can input manually, and the results are shown.

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

I'm a bit stuck now.

[beersuser]

To answer my own question:
I think I have a solution. It involves a macro to update the filter table and refresh an import range (more on the import range further down):

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")
	DBName = "Attendance"
	DB = Context.getByName(DBName)
	Conn = DB.getConnection("","") 
	Stmt = Conn.createStatement()
	Stmt.executeupdate("update ""Filter"" set ""D1"" = '" &  cellC1Text & "', ""D2"" = '" &  cellD1Text & "' where ""FID"" = '1'")
	ThisComponent.DatabaseRanges.getByName("Import1").refresh()
End Sub

Create a query against the main data table using LibreOffice base, referencing the filter table:

Code: Select all

SELECT "A"."Payweek", "A"."Branch", "A"."EmployeeName", "A"."StdHoursTotal", "A"."OTHoursTotal" FROM "AttendanceData" AS "A", "Filter" AS "F" WHERE "F"."FID" = 1 AND ( "A"."Payweek" >= "F"."D1" OR "F"."D1" IS NULL ) AND ( "A"."Payweek" <= "F"."D2" OR "F"."D2" IS NULL ) ORDER BY "Payweek" DESC

Drag this query from the data ranges window onto the worksheet (If no other named ranges are present, calc will assign the name 'Import1' to the range)

Tie them together with a button that runs the macro and voila! Click the button and your data appears.

Looks good to me - hope this helps someone else. Thanks to all the other posts that brought me to this solution. Not sure it's the right way to do it, but it's 'a way' at least.

[Villeroy]

Without macro or with one line of Basic code: viewtopic.php?t=88516&p=416210#p416210