Previous record needed in report

[dkaf]

I am writing a fuel report using Crystal Reports 10. I am grouping by vehicle and selecting fuel transactions by date. I am pulling a field called FTK_Main.meter_1 which is the odometer reading. I want to record the previous record (which is the odometer from the last time they fueled). The formula I used is: Previous ({FTK_MAIN.meter_1}). My problem: For the report for February, I want to have it record the last odometer reading for the last fueling in January. I am getting totally wrong figures for that first record ex: odometer 2/1/06 fueling was 629; the previous record is 13537. I check the prevous fueling and it should have been 620. In the rest of the report it is pulling the correct previous odometer reading.

 

[lbass]

What is your record selection formula? Do you have groups in the report? Based on what field? It would help if you showed a sample of your report.

-LB

 

[dkaf]

I do have groups in my formula. My first group is Department ie: parks dept, engineering, etc.

My second group is Vehicle number which is how we track them.

In my Details I am pulling Fuel ticket date, fuel tank number, pump price, quantity pumped, odometer reading. It is at this point I want to record the previous odometer reading. IE: for a Feb 1 - 28, 2006 report, I want the last odometer reading in January. It might be Jan 31, or the 28th or any other date or month.

My Select Expert is where I define my report dates, in this case : FTK_date is between Feb 1st and Feb 28th.

GH1 Dept.
GH2 Vehicle Number
Details FTK_date Tank Price_fuel Qty_fuel Meter_1 here is where I want the previous Meter (odometer reading)

 

[lbass]

I think the easiest method would be to insert a subreport into the VehicleNo Group Header. Link the subreport to the main report on Department and Vehicle Number and also on FTK_date. Then go into the record selection formula for the subreport and change the operator so that it reads:

{table.FTK_Date} < {?pm-table.FTK_Date}

This will return all previous dates for that department and vehicle. Go to report->sort records and add FTK_Date, ascending as a sort field. Then create a formula:

whileprintingrecords;
shared numbervar reading;

if {table.FTK_reading} = maximum({table.FTK_readin}) then
reading := {table.Meter_1};

Place this in the subreport report footer. If you want to display this in the vehicle group header, then suppress all but the subreport footer; otherwise, suppress all sections within the subreport (but not the subreport itself).

Then you can reference this in the main report by creating a formula and using it in a section below the one in which the subreport is located:

whileprintingrecords;
shared numbervar reading;

You should also place a reset formula in the vehicle number group footer:

whileprintingrecords;
shared numbervar reading := 0;

This will prevent previous values from populating the variable if there is a null subreport (no previous values).

-LB

 

[dkaf]

Congratulate me, you walked me through my first sub-report outside of the class I took last year.

However, I need to know how to get that previous odometer reading to be picked up and read by my Previous ({FTK_Main.meter.1}) formula which is my Previous Meter Column.

I need my report to show the following:

Vehicle 9142 Skid Loader
Fuel Tank # Gas unit Meter @ Previous Mileage run
Date Price Fill-up Meter since last
fill-up
2/1/06 9 $2.19 1244 1240 4
2/2/06 9 $2.19 1246 1244 2
2/9/06 9 $2.17 1250 1246 4

Any way to link so my formula is pulling from the sub-report for the previous meter for just the first record? It is pulling correctly for the rest of the month, just not the first record.

 

[lbass]

The subreport will pull the previous dates, but the shared variable formula that sets the variable equal to the maximum date will only pull the reading from the last record, which is the one you will be using.

You don't need to use a previous formula, as the shared variable already contains the previous value, so the mileage formula would look like this:

evaluateafter({@previous}):
whileprintingrecords;
shared numbervar reading;
numbervar cnt;

if cnt = 1 then
{table.meteratfillup} - reading else
{table.meteratfillup} - previous({table.meteratfillup})

For the display of the previous fillup, use:

//{@previous}:
whilepringingrecords;
shared numbervar reading;
numbervar cnt := cnt + 1;

if cnt = 1 then
reading else
previous({table.meteratfillup})

-LB

 

[dkaf]

I've been working on this for a week now and I can't get the last formulas to work. If in the
//{@previous}; I put
numbervar cnt:= 1;

if cnt = 1 then
reading else
previous ({table.meteratfillup})

I get the last odometer from the previous month to print fine but it just repeats. It is NOT recording the odometer from the previous fill up for the current month. When I write it as
Numbervar cnt:= cnt +1

I get a unidentifiable number for the first odometer. It is not recording the odometer reading from the last month. It IS recording the odometer for the previous fill up for the curret month.

What am I doing wrong?

 

[lbass]

I cannot troubleshoot this without knowing the exact steps you took to implement this, including in what section you placed the subreport, what formulas you created and in what sections you placed them.

It would also help if you provided some sample results and what they should be.

-LB

 

[dkaf]

My report looks like this:

[group 1] Department number and name

[group 2] vehicle number and description

I have placed my fuel supreport into GH2 and it is reporting correctly. In my subreport in report footer I have placed the shared number variable--@shared number variable (number)

I wrote it as follows:

whileprintingrecords;
shared numbervar reading;

if {ftk_main.ftk_dat} = maximum ({ftk_main.ftk_date}) then
reading :={ftk_main.meter_1}

it is reading the correct reading.

I have put it in the main report by creating a formula

@shared number variable (number)

whileprintingrecords;
shared numbervar reading

I placed this in my main report in details.

I placed my reset formula and placed it in GF1. My formula:

whileprintingrecords;
shared numbervar reading:=0

It is putting a 0 balance in my main report.

Here is where I start having problems.

The mileage formula I placed in the details section of my main report. The formula:

evaluateafter ({@shared numberval reading});
whileprintingrecords;
{@shared numberval reading};
numberVar count := 1;

if count = 1 then
{ftk_main.meter_1} - {@shared numberval reading} else
{Ftk_main.meter_1} - previous ({ftk_main.meter_1})

the report subtracts the fuel at this fillup from the previous odometer reading from last month (just what I wanted) but the second record is subtracting the odometer at that fillup from the last reading from last month. It is not subtracting this fillup from the first reading for the month. The third fillup, forth fillup and so on all subtract from the last fillup of last month. I want each to subtract from the previous fillup they made this month.

Second problem:

For the display of the previous fillup I have placed it in the details section of the main report. My formula:

whilepritingrecords;
{@shared numberval reading};
numbervar meleagecount;
Mileagecount :=1;

if mileagecount = 1 then
{@shared numberval reading} else
previous ({ftk_main.meter_1})


Written like this it only displays the last reading from the previous month.

Example:
Fuel odometer reading 2/1/06 102616 Previous 101877
2/4/06 102714 101877
2/8/06 102804 101877

If I write it :

whileprintingrecords;
{@shared numberval reading};
numbervar mileagecount;
mileagecount : = mileagecount +1;

if mileagecount = 1 then
{@shared numberval reading} else
previous ({ftk_main.meter_1})


The first record will show the first record as a unrelated number. The second, third etc are reading the previous meter correctly.

An example:

Fuel odometer at 2/1/06 102,616 Previous 14,645
2/4/06 102714 102616
2/8/06 102804 102714

The first record should read Previous 101877

Hope this explaines my problem

 

[lbass]

I just created a mock report and tested it, and it works.

//{@shared var} in subreport report footer:
whileprintingrecords;
shared numbervar reading;

if {ftk_main.ftk_date} = maximum ({ftk_main.ftk_date}) then
reading :={ftk_main.meter_1};

In the main report:

//{@previous} to be placed in the detail section to show the previous value:
whileprintingrecords;
shared numbervar reading;
numbervar cnt := cnt + 1;

if cnt = 1 then
reading else
previous({ftk_main.meter_1});

//{@mileage} to be placed in the detail section:
evaluateafter({@previous});
whileprintingrecords;
shared numbervar reading;
numbervar cnt;

if cnt = 1 then
{ftk_main.meter_1} - reading else
{ftk_main.meter_1} - previous({ftk_main.meter_1})

//{@reset} to be placed in the Group #2 footer (NOT in GF1):
whileprintingrecords;
shared numbervar reading := 0;
numbervar cnt := 0;

You should be able to paste these into your report as is. Note that you had the reset in the wrong footer, and that I had forgotten to add a reset for the counter. Note also that you should be using the variable itself in the formulas, not the names of other formulas.

-LB

 

[dkaf]

YES, that worked perfectly, thanks much.