SQL query from Calc?

[larsj]

Hello

Is it possible to do an SQL query from Calc?

I have a Base database from which I would like to import selected data into a Calc spreadsheet. At present, I do this by setting up queries in my database and create a link in the Calc spreadsheet (as explained by Villeroy in this fine Tutorial: viewtopic.php?f=75&t=18511). This works fine, with only one disadvantage: I have to set up a new query for each new set of data.

So I have been thinking: Perhaps I could set up a query in my Calc spreadsheet, which should read certain keywords from cells in order to be easily changed. E.g., writing "10" in the cell, the query would be set up to find the data belonging to my data set number 10. If I then change 10 to 11 and refresh the data import, I would get my data set number 11.

Why not just do it in Base? Because I want to plot the data. And I want to save the data (not the plot) as .csv file. I can do these things from Calc, but do not think I can do it Base.

Is my idea possible? If not, is there a different approach?

--Lars J

[Villeroy]

Download this: download/file.php?id=21951
The spreadsheet has a database range (which may be linked to some database or not) and some form controls that are linked to cells on the second sheet.
You can edit the form controls and click the "Refresh" hyperlink or call the equivalent Refresh command from the Data menu while the cell cursor is within the list.
This is how it is tied together:
On the second sheet call menu:Insert>Names>Define... and click the "Criteria" entry wich refers to $Sheet2.$A$3:$D$4. Under [More Options] you see that it has also the "Filter" flag set.
The formulas in that filter range should be self-explaining. They refer to the list's header row and to the linked cells of the form controls.
Back to sheet1, click any cell in the list and call menu:Data>Filter>AdvancedFilter...
The filter criteria are read from cell range $Sheet2.$A$3:$D$4 which can be set with 2 clicks from the list box on the left where you can pick "Criteria". Here you could pick filter criteria from other named ranges analog to database queries.

######################################################

If you are familiar with the concept of "power filters" using a dedicated database table with filter criteria, you could also point your input form to these filter criteria and import a query which is filtered by these criteria.

Download download/file.php?id=23688 and register this thing as "FilterData".
The attached spreadsheet has a form which is linked to a specific row of filter criteria in a dedicated database table.

The form is linked to row #1 of the table named "Filter" where 2 dates and 2 integers can be stored stored.
The form does not allow deletion of records.
When you tab through the form, you won't tab into the new record because the form does not allow new records.
The form does not show any navigation toolbar for this single record.
The form controls allow Null entries.
The import range is linked to a query named "qReport" in data soure "FilterData" which selects the following record set:

Code: Select all

SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" 
WHERE "C"."ID" = "D"."CID" 
  AND "P"."ID" = "D"."PID" 
  AND "F"."FID" = 1 
  AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) 
  AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL ) 
  AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL ) 
  AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL ) 
ORDER BY "Date" DESC

The database document contains several queries, forms and reports demonstrating the same technique or similar techniques.

[larsj]

Thanks, this seems to be what I was looking for. However, I am a bit confused about the initial description:

Download this: download/file.php?id=21951
The spreadsheet has a database range (which may be linked to some database or not) and some form controls that are linked to cells on the second sheet.

(italics added)

The link gives me a database file (Filterdata3.odb). And the attached spreadsheet has only one sheet. Am I overlooking something?

The second, more advanced, solution I have gotten to work: Downloading the database (Filterdata4.odb), registering it as "FilterData", and using it together with the attached spreadsheet (Filterdata4.ods). I can then define a date range and person name and category, and press "Refresh Import".

To be able to create a filter of my own, however, I would prefer to go for the simpler solution first. So a reply to my question above would be much appreciated.

Best regards,
Lars J

[Villeroy]

Install the "Oracle Report Builder" extension. It lets you build reports with charts.