Vehicle Fuel Report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Vehicle Fuel Report

Post by Salvatori15 »

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
Attachments
Fuel Drawings.odb
(63.41 KiB) Downloaded 265 times
Apache OpenOffice 4.1.1
Windows 7 Professional
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Post by Sliderule »

You said:
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 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.

I have included, a SAMPLE report, with the name of: All Fuel Drawing Rpt
Fuel Drawings.odb
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.
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Vehicle Fuel Report

Post by MTP »

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

Code: Select all

UPDATE "All Fuel Drawings" SET "Vehicle" = RTRIM("Vehicle")
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:

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"
Hopefully this will help you get set up as you want!
Attachments
Fuel Drawings (new form).odb
(101.87 KiB) Downloaded 255 times
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

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
Apache OpenOffice 4.1.1
Windows 7 Professional
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Vehicle Fuel Report

Post by RoryOF »

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/
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

Cheers RoryOF will keep an eye open for when service is restored.
Apache OpenOffice 4.1.1
Windows 7 Professional
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

Thanks MTP that seems to do the job
Apache OpenOffice 4.1.1
Windows 7 Professional
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Post by Sliderule »

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.
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

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
Apache OpenOffice 4.1.1
Windows 7 Professional
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Post by Sliderule »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vehicle Fuel Report

Post by Villeroy »

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>
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
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

Hi

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Post by Sliderule »

On the link I gave you ( http://extensions.openoffice.org/en/pro ... rt-builder ) . . . click on the GREEN button . . . Download extension

Sliderule
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

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.
Attachments
Capture.JPG
Apache OpenOffice 4.1.1
Windows 7 Professional
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Vehicle Fuel Report

Post by RoryOF »

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
Salvatori15
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Post by Salvatori15 »

Even after restarting OpenOffice I get the same thing
Apache OpenOffice 4.1.1
Windows 7 Professional
Post Reply