Vehicle Fuel Report
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Vehicle Fuel Report
Hi
I have created a table that contains information of the fuel drawings of a number of vehicles over a period of months from
various different sources. I would like to now run a report that tells me how many litres have been drawn in total per vehicle
and break it down by source for a given period.
I have tried to create one using the wizard but it doesn't seem to do what I want and it also shows the date/period as a 5 digit number
not the date.
Attached is the table if its any use.
Sorry if I seem a bit dim or if this has been covered already.
Thanks
I have created a table that contains information of the fuel drawings of a number of vehicles over a period of months from
various different sources. I would like to now run a report that tells me how many litres have been drawn in total per vehicle
and break it down by source for a given period.
I have tried to create one using the wizard but it doesn't seem to do what I want and it also shows the date/period as a 5 digit number
not the date.
Attached is the table if its any use.
Sorry if I seem a bit dim or if this has been covered already.
Thanks
- Attachments
-
- Fuel Drawings.odb
- (63.41 KiB) Downloaded 299 times
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
You said:
I have included, a SAMPLE report, with the name of: All Fuel Drawing Rpt
It uses the Oracle Report Writer extension, so, if you do not have that extension installed, you would first have to install it from the web site:
http://www.openoffice.org/extensions/index.html
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
I am sorry, but, I really do not understand what you want. That is, you have NOT provided a report you started, that might need to be modified to add/correct/change the way it is displayed. For me to GUESS what or how to display information is, impossible.Salvatori15 wrote: I would like to now run a report that tells me how many litres have been drawn in total per vehicle
and break it down by source for a given period.
I have included, a SAMPLE report, with the name of: All Fuel Drawing Rpt
It uses the Oracle Report Writer extension, so, if you do not have that extension installed, you would first have to install it from the web site:
http://www.openoffice.org/extensions/index.html
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Vehicle Fuel Report
Sliderule has provided some excellent information on the Oracle Report Builder (which I agree is way better than the default Base reports). I also made a sample file, with the approach of using a "form" to get your report data.
This is a somewhat complicated setup in Base. I've done several things to your file:
1. Some of your vehicle IDs had trailing spaces. The database will 'see' these as two different vehicles. I fixed this by going to Tools->SQL and entering the command
2. I opened the table "All Fuel Drawings" and for each date column right-clicked on the column name (the gray bar at the top) and chose "Format", then selected a date format. After making these changes, I switched to the main Base window and saved the file. It should now show everything correctly as dates. (The 5-digit number you saw before is the number count of days from January 1, 1900 as day 1 to whatever date it was representing.)
3. I added a table "Filter" and made a form with date filtering based on the instructions in this tutorial: [Example #1] Filter/Search with Forms (leveraging SubForms)
4. I made a query to get the information you want, to be used in the form. Because of some special syntax in the query, I decided to save it as a "View" (so you see it in the list of "Tables"). You can right-click on the view and choose "Edit in SQL View" if you want to change it. The query (view) is:
Hopefully this will help you get set up as you want!
This is a somewhat complicated setup in Base. I've done several things to your file:
1. Some of your vehicle IDs had trailing spaces. The database will 'see' these as two different vehicles. I fixed this by going to Tools->SQL and entering the command
Code: Select all
UPDATE "All Fuel Drawings" SET "Vehicle" = RTRIM("Vehicle")
3. I added a table "Filter" and made a form with date filtering based on the instructions in this tutorial: [Example #1] Filter/Search with Forms (leveraging SubForms)
4. I made a query to get the information you want, to be used in the form. Because of some special syntax in the query, I decided to save it as a "View" (so you see it in the list of "Tables"). You can right-click on the view and choose "Edit in SQL View" if you want to change it. The query (view) is:
Code: Select all
SELECT "Vehicle", "Source", SUM( "Quantity" ) AS "TotalLitres" FROM "All Fuel Drawings" INNER JOIN "Filter" ON "Filter"."FilterID" = 0 WHERE "All Fuel Drawings"."Drawing Date" >= "Filter"."StartDate" AND "All Fuel Drawings"."Drawing Date" <= "Filter"."EndDate" GROUP BY "Vehicle", "Source"
UNION ALL
SELECT "Vehicle", ' Vehicle Total' AS "Source", SUM( "Quantity" ) AS "TotalLitres" FROM "All Fuel Drawings" INNER JOIN "Filter" ON "Filter"."FilterID" = 0 WHERE "All Fuel Drawings"."Drawing Date" >= "Filter"."StartDate" AND "All Fuel Drawings"."Drawing Date" <= "Filter"."EndDate" GROUP BY "Vehicle", "Source"
- Attachments
-
- Fuel Drawings (new form).odb
- (101.87 KiB) Downloaded 296 times
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Hi Sliderule,
Sorry you didn't understand what I wanted I thought I explained it, but not to worry.
I have clicked on your link to download the Oracle extension but it just keeps returning "No Data found"
and does the same no matter which way I go to the website so at the moment I can't open your
report.
Thanks anyway
Sorry you didn't understand what I wanted I thought I explained it, but not to worry.
I have clicked on your link to download the Oracle extension but it just keeps returning "No Data found"
and does the same no matter which way I go to the website so at the moment I can't open your
report.
Thanks anyway
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
SourceForge servers are undergoing a major rebuild at present; they are running a reduced service, providing the only most popular downloads as an interim measure. Extensions and Templates repositories are not currently available pending completion of the rebuild. Further details are at
http://sourceforge.net/blog/sourceforge ... storation/
http://sourceforge.net/blog/sourceforge ... storation/
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Cheers RoryOF will keep an eye open for when service is restored.
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Thanks MTP that seems to do the job
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
As of the date and time I am writing this, sourceforge is back online, and, the Oracle Report Builder extension can be downloaded from:
http://extensions.openoffice.org/en/pro ... rt-builder
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
http://extensions.openoffice.org/en/pro ... rt-builder
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Hi Sliderule
I have downloaded the extension but when I try to add it to OpenOffice even though I click on the file and the file size is 3375KB it
says it doesn't exist ?
Any ideas ?
Thanks
I have downloaded the extension but when I try to add it to OpenOffice even though I click on the file and the file size is 3375KB it
says it doesn't exist ?
Any ideas ?
Thanks
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
From the OpenOffice / Libre Office menu:
Tools -> Extension Manager... -> Add...
and follow the instructions. Remember, you have to Enable that extension ( and any other extension you want to use ) and restart OpenOffice / LibreOffice ( and the Quick Starter ) if you are using that, for the Extension to be recognised by the Office suite.
You can also look in the help files ( F1 ) for extensions;Extension Manager
Sliderule
Tools -> Extension Manager... -> Add...
and follow the instructions. Remember, you have to Enable that extension ( and any other extension you want to use ) and restart OpenOffice / LibreOffice ( and the Quick Starter ) if you are using that, for the Extension to be recognised by the Office suite.
You can also look in the help files ( F1 ) for extensions;Extension Manager
Sliderule
Re: Vehicle Fuel Report
Download the version that matches your OpenOffice version from http://extensions.openoffice.org/en/pro ... rt-builder and nowhere else.
a) Open the downloaded file with OpenOffice4 (right-click>Open With...)
OR
b) Start OpenOffice and then open that file via menu:File>Open... or via menu:Tools>Extensions>[Add...]
OR
c) Install it from the command line using unopkg.exe in the OpenOffice program folder: unopkg add <downloaded file>
a) Open the downloaded file with OpenOffice4 (right-click>Open With...)
OR
b) Start OpenOffice and then open that file via menu:File>Open... or via menu:Tools>Extensions>[Add...]
OR
c) Install it from the command line using unopkg.exe in the OpenOffice program folder: unopkg add <downloaded file>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Hi
My OpenOffice version is 4.1.1 and there doesn't seem to be one that matches.
My OpenOffice version is 4.1.1 and there doesn't seem to be one that matches.
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
On the link I gave you ( http://extensions.openoffice.org/en/pro ... rt-builder ) . . . click on the GREEN button . . . Download extension
Sliderule
Sliderule
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Hi Sliderule
I am clicking on your link going to the website clicking on the green button, the file is downloading I am clicking
on it and the extension Manager box opens up says it is adding Oracle report builder then the box shown in the attached
file appears.
I am clicking on your link going to the website clicking on the green button, the file is downloading I am clicking
on it and the extension Manager box opens up says it is adding Oracle report builder then the box shown in the attached
file appears.
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional
Re: Vehicle Fuel Report
It may be that you need to restart OpenOffice after installation of the extension, before OpenOffice can find it.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 19
- Joined: Thu Jul 16, 2015 5:19 pm
Re: Vehicle Fuel Report
Even after restarting OpenOffice I get the same thing
Apache OpenOffice 4.1.1
Windows 7 Professional
Windows 7 Professional