Apache OpenOffice Community Forum

What I am needing to accomplish is to pull data from a single table but I need it formatted in two different ways. The table contains the shipping tickets for bulk grain and I am pulling all the inbound tickets for the day. I have managed to get all the data I want but I have to use two different reports to get it and I have a feeling I can do it with a single report. In the first report I sort the data by location code, then by commodity, then sum the net values and the report outputs the totals of each commodity that each location brought in for the day. The next report takes the same data but only sorts it based on commodity and sums the totals for a company wide intake for the day. While it will work like it is, I am sure it would be much nicer if we only had a single report with all the data. The way I am doing it now the queries are sorting and summing the data (which is the way I assumed it was supposed to be done). I need the data sorted in two different ways to make the different reports but I can't figure out if it is possible to pull in two queries into a single report (so far it seems that it is not possible).

Thanks for any help
David

You can put them side by side on a Calc sheet.
In Calc hit F4, drag the first query icon from the left pane onto cell A1 and the other query besides. Leave a blank column between them. It will help you to adjust the layout.
Call Data>Define... button [More Options] and check extra options #2 and #3 and may be #4 for both import ranges.
Apply cell styles, page styles, conditional formattings, calculations and charts to your liking.
Data>Refresh refreshes the selected import range.

OK now doing that, is there a way to change the criterion that the query uses without going into edit the query? It will work if its not possible but would be really nice if it could. Also is this a report that can be saved and pulled up again every day with new data or will it have to be rebuilt every day?

*Edit*
Basically I was wanting to know if the user could change the date that is being looked up without having to edit the query directly every time. My user is completely capable of doing that but if I could give him a report that he doesn't have to do that with it would be great.

This is called a parameter query. A named parameter starts with a colon and has no spaces.
Enter dates in ISO format YYYY-MM-DD
Every time you access a parameter query, you will be prompted for the parameter(s).

There is a more elegant solution:
Base-menu:Tools>SQL... This creates a filter table with one record having the row number #1 and an empty date field.)
menu:View>Refresh Tables makes it visible in the GUI.

Draw a date control onto your spreadsheet (toolbar "Form Controls")
Right-click>Form... tab "Data"
Data Source: your registered database name
Source type: SQL
Source: SELECT * FROM "Filter" WHERE "ID"=1
Disallow everything except modification.
Right-click>Control..., tab "Data"
Linked Field: D (the date field)
You may add a drop-down calendar on the "General" tab.
Add a push button with Action=Save Record
You may make both form controls unprintable.
Now you can enter a date into row #1 of that filter table from that little spreadsheet form.

Make your queries like this:

OK most of that was over my head but I will work on it and do my best. Thank you for your input. One thing that I am worried will cause a problem is that the database that I am drawing data out of does not use a standard date stamp. for some stupid reason it lists the date as B30829 for today's date. YYMMDD with a B instead of a 1 for 13. again I do not understand that at all but it is how they (Agris) set the system up. Even still it looks like I could use the first suggestion to make it work.
Again thanks and I will do what I can with it.

*Edit* Also I just remembered, due to Agris being rather paranoid I cannot push anything into the database, I have read only rights to the tables and the database as a whole.

Hispaladin wrote:OK most of that was over my head but I will work on it and do my best. Thank you for your input. One thing that I am worried will cause a problem is that the database that I am drawing data out of does not use a standard date stamp. for some stupid reason it lists the date as B30829 for today's date. YYMMDD with a B instead of a 1 for 13. again I do not understand that at all but it is how they (Agris) set the system up. Even still it looks like I could use the first suggestion to make it work.
Again thanks and I will do what I can with it.

This can be fixed in the spreadsheet but I have no idea how to handle this in a parameter query. You've got to try out if B30829 returns the right record set.

*Edit* Also I just remembered, due to Agris being rather paranoid I cannot push anything into the database, I have read only rights to the tables and the database as a whole.

This makes the elegant solution obsolete. You did not mention the type of database and I assumed some "ordinary" database.

I tried the parameter query but I am getting an error(attached the screenshot of the error), not sure if this is due to the strange format or something else. If I pull the data into the spreadsheet and then sort it I will have way to much data. The table I am pulling has hundreds of thousands of entries. I am sure that some of this stuff is redundant and I only ask you be patient with me I am very new to this.

While in query design mode (the SQL text editor for queries), make sure that menu:Edit>"Run SQL Directly" is turned OFF.
It seems as if the query is passed over to the ODBC driver which can not handle the named parameter. With that option turned off, Base will interprete the query.

Double checked "Run SQL Directly" is OFF, was the whole time. Still get the same error. Copying your code exactly and only replacing the table and column names I get that error.

OK I think the issue might be with pervasive not liking the expression :WhichDate but when I changed it to just a single ? it worked.
I also finally got the multiple queries to update successfully in the spreadsheet. I had to make the changes in the define field section one query at a time and save between each one. Thanks a bunch
David