[Solved] Multiple queries with a ?

[Hispaladin]

OK this may not be the right forum for this question but I figured it was close. I have a multiple queries that are being called from a calc spreadsheet and each one has a ? to ask the user for input to search by. I want to know if there is a way to make those queries look at the contents of a cell or to somehow make it so that I only have to enter the data once instead of 4 times. I am entering a date and two of the queries need yesterdays date and the other two want the same date only last year. The other thing is it is a stupid format for the date so a dropdown or something will not work. It is a YYMMDD format but to accommodate the change after 2000 they started putting and 'A' in front to replace the 0, 'B' for 1 ect. so 2013 is B3. (yeah and I have absolutely no way of getting that changed) Is there any way to get this to work? (I am going to post this in the Base forum also)

[Villeroy]

Code: Select all

=VALUE(IF(LEFT(A1;1)="A";"200";IF(LEFT(A1;1)="B";"201";"19"&LEFT(A1;1)))&MID(A1;4;1)&"-"&MID(A1;3;2)&"-"&MID(A1;5;2))

returns the right day number. Format as date.

[Hispaladin]

I think I understand what that will accomplish, and while it could be helpful it doesn't really answer my question. What I have right now is a spreadsheet that pulls data from 6 queries, 4 of which ask for data to filter the results. So when I run the spreadsheet I have to enter the data 4 times. Two of the queries use one date and two use another so I have to enter the same two dates two times each. I am wanting to know if there is a way to make it so that I only have to enter the info one or possibly two times(if it is possible to make the query pull the variable from a cell then I could use your example to make the correct date appear in the cell for each query.)

[thinman3]

Would be way more helpful if you upload a copy of the spreadsheet. Click on the "Upload attachment" tab, located directly under the box / field you typed your question into and follow the directions .

thinman3

[Villeroy]

Would be way more helpful if you upload a copy of the spreadsheet. Click on the "Upload attachment" tab, located directly under the box / field you typed your question into and follow the directions .

thinman3

+1
And while you are at it, the actual queries would help us to help. Oh, which database are you using, by the way? (no, Base is not a database)
I can offer a Calc macro which reads the import query from a cell and the cell concatenates SQL strings with parameters. viewtopic.php?t=1645&p=6847#p6847

[Hispaladin]

I am using Pervasive SQL with ODBC drivers to connect to it from Base. Right now I have 4 queries that are pulling data but they are in pairs and the pairs are exactly the same (so this is probably unnecessary) but I created two of each so that when I pointed the spreadsheet to them they would ask for separate dates as the first of the pairs looks up today's date and the other looks up the same date only last year. I will include the queries (I will just submit one of each, just know that there are two of each one) and I will upload the spreadsheet even though I am fairly sure there is nothing useful to it. I haven't really done much of anything with that part, it just has the queries dump into slots and that is all.
Here is the code for the first one

Code: Select all

SELECT "C01_CMM_S" AS "Commodity", SUM( "C01_TOT_NET_UNTS_D" ) AS "Bushels" FROM "A00107262012101738"."C01_TKT_INF" AS "C01_TKT_INF" WHERE "C01_IN_OUT_CD_S" = 'I' AND "C01_ENTRY_DATE_T" = ? GROUP BY "C01_TKT_INF"."C01_CMM_S"

and the second

Code: Select all

SELECT "C01_LOC_S" AS "Location", "C01_CMM_S" AS "Commodity", SUM( "C01_TOT_NET_UNTS_D" ) AS "Bushels" FROM "A00107262012101738"."C01_TKT_INF" AS "C01_TKT_INF" WHERE "C01_IN_OUT_CD_S" = 'I' GROUP BY "C01_TKT_INF"."C01_LOC_S", "C01_TKT_INF"."C01_CMM_S"

[Villeroy]

OK, the attached file has a set of macros in library "DBQ". This library can be exported to other files or to a global macro container.
A1 is a named cell "DBQ1" which concatenates your first query (the one with the ?-date). The ? is substituted with the date from cell $A$3. The cell displays the query string as it would be shown in the query editor with names in double-quotes and literal strings in single quotes.
This is how I build that formula: I replace all double-quotes with 2 double-quotes. Then I prepend =" and append another double-quote. Finally I replace the ? parameter with a concatenated reference in single quotes: '"&$A$3&"' (single quote, double-quote, concatenation with $A$3, double-quote, single quote).
The hyperlink in A2 calls a macro. Broken down to multiple lines, the formula looks like this:

Code: Select all

=HYPERLINK(
"vnd.sun.star.script:DBQ.DBQ.RefreshDBQByURL?language=Basic&location=document
  &dbRange=Import1
  &Source=OOreportPub
  &SQLCell=DBQ1"
;"Refresh")

The HYPERLINK function generates a hyperlink with an URL and a label ("Refresh").
The URL consists of a mandatory part addressing the basic macro embedded in this document:
vnd.sun.star.script:DBQ.DBQ.RefreshDBQByURL?language=Basic&location=document
The other URL parameters are evaluated by the called macro.
They specify
-- the database range name to be refreshed: &dbRange=Import1
-- the data source name &Source=OOreportPub
-- the name of the cell having the SQL query &SQLCell=DBQ1

Cell A3 converts today's date to your special date string.

Like all documents having embedded macros, this document has to be stored in a trusted directory (if you trust me). Specify your trusted directories under Tools>Options>Security>Macro Security...

It would be a little bit surprising if all this worked ad hoc on your system. I can not test this without having the same database and the same drivers.

[Hispaladin]

@Villeroy
This is fantastic and I hate to say it, but.... I have to run the report using the last complete business day which in most cases would just mean subtracting 1 from the day but on Mondays it would have to be Friday so it kinda messes that up a little. I am still picking over how it works and I am truly fascinated and a little confused as to how your formula get the final date format but it is cool.

I am thinking what could work (by the way the refresh button will make the whole thing work like I want, I think) is have a single field where the user can enter the date of the day they want to pull in normal format YYYYMMDD then have a formula that will convert that data into the screwy format the database wants for this year and another for last year. Then have the formula that pulls the data look at the converted date field that corresponds to the year it is supposed to show.

OK all that being said the more I look at the document you sent me the more I realize I have no idea what or how you got it to work like that. It does look like it could work out and do what I want but I have no clue how you did what you did so I don't even know where to start making it do what I want. Man I wish I knew a little more about how you did all that so I could even take it and tailor it a little to what I want.

[Villeroy]

First of all, does the query work as designed? Does it query the correct data based on today's date?

Put the day you want into any other cell, say B3, and modify A3 accordingly replacing TODAY() with the cell reference:

Code: Select all

=CHAR(YEAR($B$3)-2000+53)&RIGHT(YEAR($B$3);1)&TEXT(MONTH($B$3);"00")&TEXT(DAY($B$3);"00")

[Hispaladin]

When I open the spreadsheet it asks me if I want to rerun the queries and I chose yes so that I could see that it is pulling the correct data (which it does, I also tried it without rerunning the queries with the same result) when I hit the refresh button it blanks out the grain intake today columns. I tried changing the formula as you said and pointing it to another cell and putting the date in that cell and it gave me a Err:502 so I am not sure what I did wrong there. I even copied and pasted the code you showed in your last post and it gave me the same thing. Just for the heck of it here is the formula that is in the cell

Code: Select all

=CHAR(YEAR($C$3)-2000+53)&RIGHT(YEAR($C$3);1)&TEXT(MONTH($C$3);"00")&TEXT(DAY($C$3);"00")

[Villeroy]

Please enter a valid date into the spreadsheet cell.
20131125 is not a date. It is an 8-digit integer number.

In all spreadsheets you enter dates like this:
25/ enters this month's 25th day.
25/11 enters this year's November the 25th (in US context 11/25).
25/11/2012 enters 25th November in 2012 (in US context 11/25/2012).

You can also use variants such as 25 Nov, 25 November, 2013/11/25 and many more which all give the exact same date, no matter how you entered the value.
The exact details depend on the locale context defined in Tools>Options>LanguageOptions>Languages>Locale.

For 2-digit year entries (25/11/12) there is another option under Tools>Options>OpenOffice>General. By default they refer to years between 1929 and 2030.

[Hispaladin]

Ya know those moments when you realize that something makes so much sense that you feel a little bit dumb for not figuring it out? yeah this is one of those moments, I'll try your spreadsheet again with the correct date.

[Hispaladin]

Well that fixed the issue of the function converting the date to the correct format but the refresh button still returns no data. I have looked over the code in cell A1 and as far as I can tell it looks to be referencing all the right cells and the like but I if I knew what it should look like I probably wouldn't be here asking for help with it. But I am a stubborn nerd so I am going to keep looking and hoping

[Hispaladin]

WOOHOO!! I fixed it! The code in cell A1 was looking at the refresh button for a date instead of at the date in cell A3. I noticed when rereading your comments above that you said it should read the date in A3 but the code was looking at A2 so I changed it just to see and it worked

Now the snag I am running into is that I don't understand the code used to make the macro so replicating it for the other 3 queries isn't going to happen, I am fairly sure I could write the code for the SQL query in the cell but the macro is beyond me. Is there any chance I could beg you to modify the macro and refresh button to reference import 1-4? I would be extremely grateful.

[Villeroy]

You do not need to modify that macro. Keep it as is. What you can and should do:
Open my Daily Grain report2.ods
Tools>Macros>Organize>Basic, button [Organizer], tab Libraries, Location Daily Grain report2.ods
Pick the DBQ library from the list and click [Export...]
[x] Export as extension
Specify a folder and some name, say DBQ
Now you have an extension DBQ.oxt which you may distribute and install by double-clicking the file or via Tool>Extensions... [Add]
Install the new extension, close my file and you will find the Basic macro under Tools>Macros>Organize>Basic>My Macros>DBQ>DBQ>RefreshDBQByURL without having my document loaded.

Composing the clickable hyperlink formula:
=HYPERLINK("vnd.sun.star.script:DBQ.DBQ.RefreshDBQByURL?language=Basic&location=document&dbRange=Import1&Source=OOreportPub&SQLCell=DBQ1";"Refresh")
turns into
=HYPERLINK("vnd.sun.star.script:DBQ.DBQ.RefreshDBQByURL?language=Basic&location=application&dbRange=Import2&Source=OOreportPub&SQLCell=OtherCellName";"Refresh")
because the macro can't be found in your document anymore. It is found in your application wide settings (in the dialogs shown as "My Macros"), in library DBQ, module DBQ, the routine named RefreshDBQByURL

Pointing the macro to another import range "Import2" is easy: &dbRange=Import2 where Import2 is just the name of the existing database range you want to fill with query data. One cell will suffice for the first import. It will expand automatically. Database range names are organized under Data>Define...

Pointing the macro to another query is not too difficult: &SQLCell=OtherCellName where OtherCellName is the "normal" range name of a single cell (select one cell and type the name into the name box left of the formula bar). These names are organized under Insert>Names>Define... [Ctrl+F3]. The cell name needs to refer to an absolute reference with a $ in front of sheet, row and column: $SheetName.$X$12

The name of your data source remains the same: &Source=OOreportPub
The macro language remains the same: &language=Basic
The protocol remains the same vnd.sun.star.script:
You can compose the whole URL in some extra cell, say X1, which may be easier to edit. Then =HYPERLINK($X$1;"Refresh") gives the clickable hyperlink with "Refresh" as visible text.

Composing the SQL query in a spreadsheet cell:
What should we put into the "OtherCellName"? This is a little bit tricky if you feel unfamiliar with text formulas in spreadsheets.

Normally we can concatenate text snippets in a spreadsheet formula like this:

Code: Select all

="SELECT x FROM table WHERE x="&A1

which gives the cell text
SELECT x FROM table WHERE x=[whatever we have in A1]
This is not a valid SQL query because our field name "x" and table name "table" need to be in double-quotes but double-quotes have a special meaning in formulas.
The literal text in that formula between the = and the concatenation operator & is in "double-quotes". The quotes specify the beginning and the end of a literal string. If the string itself contains double-quotes we've got to use double double-quotes:

Code: Select all

="SELECT ""x"" FROM table WHERE ""x""="&A1

would give the valid query string with double-quotes:
SELECT "x" FROM table WHERE "x"=[whatever we have in A1]
The double double-quotes around the names do not terminate the literal string. They are taken as literal double-quotes within the double-quoted string.

Having a working parameter query in Base, you copy the query string, replace all existing double-quotes with double double-quotes, terminate the literal parts with double-quotes and replace the variable parameters with cell references surrounded by the concatenation operator &. I use to do this in a text editor and copy the resulting formula with a leading = into a Calc cell.

Code: Select all

="SELECT ""blah"", ""blurb"" FROM ""somewhere"" WHERE ""blah""='"&X1&"' AND ""blurb"">"&X2

with parameter cells X1 having XYZ and X2 having 13.98, the formula would display our query string as:
=SELECT "blah", "blurb" FROM "somewhere" WHERE "blah"='XYZ' AND "blurb">13.98
Please notice the single quotes (apostrophes) around 'XYZ' which terminate a literal string in SQL. The single quotes may be part of your formula or part of the referenced cell text in X1 but they need to be there for any text values (such as your special dates) and for SQL dates such as '2013-11-30'.
The decimal number 13.98 in parameter cell X2 always needs to have a decimal point in SQL. If your spreadsheet works with decimal commas, you've got to enter any decimals as literal text values. In this example you would enter '13.98 with a leading apostrophe into cell X2. Alternatively you format the cell as text (number format @), so any new entry into the cell will be taken as literal text rather than number or formula.

Now you only edit the parameter cells X1 and X2, which affects the SQL query in our cell named "OtherCellName" which is used by our macro because we specified &SQLCell=OtherCellName as the cell to read our query from. The query result will be dumped to the database range named "Import2" because we specified &dbRange=Import2. The request goes to the right database because we specified &Source=OOreportPub.
The only difficulty lies in the single cell where we concatenate the parameter cells into a valid SQL string with double-quotes and single quotes. But once we managed to do that, we can lock the formula cells for editing and leave them alone.

[Hispaladin]

This is awesome Villeroy, thank you for taking the time to make this up for me. With this I can make this whole thing much simpler for anyone else who needs to run the report (as it is I am basically the only one who knows how to run it). I do have one more question and I think it is fairly simple. You mentioned that I could define the refresh function in one cell and hyperlink to it with the code you listed, could that hyperlink code run multiple cells (multiple slightly different refresh functions) with a single click? As it is I will need to put in 4 refresh buttons, which if that is what we have to do it will still be much MUCH easier to explain to people than what I have to do now.

[Villeroy]

@Villeroy
I have to run the report using the last complete business day which in most cases would just mean subtracting 1 from the day but on Mondays it would have to be Friday so it kinda messes that up a little.

That would be: =TODAY()-CHOOSE(WEEKDAY(TODAY());2;3;1;1;1;1;1)

[Hispaladin]

Finally! I got the report to look good and be easy to use, thanks again and again Villeroy for the help and the walkthroughs to make this work like I want. Now people can open the file, enter the date in a format that makes sense to them one time and hit refresh (well 4 buttons but it makes sense) and they are done. Thanks again and I am going to keep this info on hand as I am sure it will get used again fairly soon