Vehicle Fuel Report

[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

[Sliderule]

You said:

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.

[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   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!

[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

[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-infrastructure-and-service-restoration/

[Salvatori15]

Cheers RoryOF will keep an eye open for when service is restored.

[Salvatori15]

Thanks MTP that seems to do the job

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[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

[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

[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>

[Salvatori15]

Hi

My OpenOffice version is 4.1.1 and there doesn't seem to be one that matches.

[Sliderule]

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

Sliderule

[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.

[RoryOF]

It may be that you need to restart OpenOffice after installation of the extension, before OpenOffice can find it.

[Salvatori15]

Even after restarting OpenOffice I get the same thing