asked on

The field has no previous or next value

Example

|Column1|Column2|
500,000 | 1
400,000 | 2
300,000 | 3
200,000 | 4
100,000 | 5
-100,000 | 6
-200,000 | 7
-300,000 | 8

I am getting Column1 by taking a SharedVar in a subreport minus a Running Total
Abs({@VarShort})-{#RTCostBasis}

I want to take the value of Column2 (6) as soon as Column1 turns negative (-100,000). When I trying to use if Column1 is < 0 and previousvalue of Column1 is > 0 then take the value of Column2.

The error Im getting is "The field has no previous or next value"

LinInDenver

This is probably happening when it hits the first or last records in your result set.

Try something like:

if onfirstrecord
then ____
else if onlastrecord
then _____
else InsertYourCalculationHere

fundsf

ASKER

Its still giving me the same error. I also added WhilePrintingRecords;

LinInDenver

where is the sub report? in detail?

If so, make sure it is a section above what you are displaying. The error you receive doesn't make sense for that being the cause, but it is worth a check to confirm.

fundsf

ASKER

Subreport is in the group header 1
Fields in question are in details.

FYI, I get the error in the Formula Editor before I can even save it

LinInDenver

Can you copy and paste into EE the exact formula you are using, the one that generates the error on check?

fundsf

ASKER

//Formula {@ShortDifference} = Abs({@VarShort})-{#RTCostBasis}
// Abs({@VarShort}) is the SharedVar in Grp1 header 1
// {#RTCostBasis} is the Running total in details
// The formula that blows up also needs to be in details.

The formula that blows up is
if {@ShortDifference} < 0
and
Previous({@ShortDifference}) > 0
then 1

As per your instructions this also errors

if onfirstrecord
then 2
else if onlastrecord
then 3
else
if {@ShortDifference} < 0
and
Previous({@ShortDifference}) > 0
then 1

tia

LinInDenver

I wonder if the Running Total is causing this...

I would play around with that a bit... see if formulas like the below work, or if they give an error. Try with each piece to see which part of the calculation is causing the issue.

if {#RTCostBasis} = previous({#RTCostBasis})
then 1
else 0

if {@VarShort} = previous({@VarShort}) //i suspect this might be the problem..
then 1
else 0

Mike McCracken

Only database fields have next and previous values. Running totals do not have them nor do most formulas.

You may have to do this with variables which save the "previous" value then update the variable.

mlmcc

fundsf

ASKER

Ive tried a manual running total which saves the previous value, but I still get the error.

// This is instead of the {#RTCostBasis}

WhilePrintingRecords;
Global numbervar ManualRTCostBasis;
ManualRTCostBasis := {sproc_ConstructiveSales;1.Cost_Basis} + ManualRTCostBasis;

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fundsf

ASKER

This is now working however the value I want, from the example above is the first instance of a negative then show the value ie -100,000 show 6.
The varaiable is in the details section. I cant get it to pass to the main report.

As a test I make the variabl in the subreport
Shared numberVar AcqPrice;
AcqPrice :=1;
This passes.

If I change it to
Shared numberVar AcqPrice;
AcqPrice :=if {sproc_ConstructiveSalesLong;1.AcqPrice} = 6 then 1;

This does not

fundsf

ASKER

Update.

I changed
Shared numberVar AcqPrice;
AcqPrice :=if {sproc_ConstructiveSalesLong;1.AcqPrice} = 6 then 1;

to
Shared numberVar AcqPrice;
AcqPrice := AcqPrice + if {sproc_ConstructiveSalesLong;1.AcqPrice} = 6 then 1;

It works now