Vehicle Fuel Report

Getting your data onto paper - or the web - Discussing the reports features of Base

Vehicle Fuel Report

Postby Salvatori15 » Mon Jul 20, 2015 4:12 pm

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 111 times
Apache OpenOffice 4.1.1
Windows 7 Professional
Salvatori15
 
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Postby Sliderule » Mon Jul 20, 2015 7:49 pm

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
Sample Report Using Oracle Report Writer
(99.93 KiB) Downloaded 102 times

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

Re: Vehicle Fuel Report

Postby MTP » Mon Jul 20, 2015 8:00 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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 104 times
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Vehicle Fuel Report

Postby Salvatori15 » Tue Jul 21, 2015 9:31 am

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

Re: Vehicle Fuel Report

Postby RoryOF » Tue Jul 21, 2015 9:38 am

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-infrastructure-and-service-restoration/
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29574
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Vehicle Fuel Report

Postby Salvatori15 » Tue Jul 21, 2015 10:06 am

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

Postby Salvatori15 » Tue Jul 21, 2015 10:12 am

Thanks MTP that seems to do the job
Apache OpenOffice 4.1.1
Windows 7 Professional
Salvatori15
 
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm

Re: Vehicle Fuel Report

Postby Sliderule » Thu Jul 23, 2015 3:59 am

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/project/oracle-report-builder

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Postby Salvatori15 » Thu Jul 23, 2015 6:06 pm

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

Re: Vehicle Fuel Report

Postby Sliderule » Thu Jul 23, 2015 6:29 pm

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

Re: Vehicle Fuel Report

Postby Villeroy » Thu Jul 23, 2015 6:41 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27217
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vehicle Fuel Report

Postby Salvatori15 » Thu Jul 23, 2015 6:54 pm

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

Re: Vehicle Fuel Report

Postby Sliderule » Thu Jul 23, 2015 6:59 pm

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

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

Re: Vehicle Fuel Report

Postby Salvatori15 » Fri Jul 24, 2015 9:37 am

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

Re: Vehicle Fuel Report

Postby RoryOF » Fri Jul 24, 2015 9:45 am

It may be that you need to restart OpenOffice after installation of the extension, before OpenOffice can find it.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29574
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Vehicle Fuel Report

Postby Salvatori15 » Fri Jul 24, 2015 10:26 am

Even after restarting OpenOffice I get the same thing
Apache OpenOffice 4.1.1
Windows 7 Professional
Salvatori15
 
Posts: 19
Joined: Thu Jul 16, 2015 5:19 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests