[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1505
  • Last Modified:

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"
0
fundsf
Asked:
fundsf
  • 6
  • 4
  • 2
1 Solution
 
LinInDenverCommented:
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

0
 
fundsfAuthor Commented:
Its still giving me the same error. I also added WhilePrintingRecords;
0
 
LinInDenverCommented:
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.
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
fundsfAuthor Commented:
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
0
 
LinInDenverCommented:

Can you copy and paste into EE the exact formula you are using, the one that generates the error on check?
0
 
fundsfAuthor Commented:
//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
0
 
LinInDenverCommented:
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
0
 
mlmccCommented:
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

0
 
fundsfAuthor Commented:
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;
0
 
mlmccCommented:
You would need to do it this way

In the report header add a formula
WhilePrintingRecords;
Global numbervar ManualRTCostBasis;
Global numbervar previousManualRTCostBasis;

// This is instead of the {#RTCostBasis}

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

Now you have both the current and previous values available.

As I stated only database fields have previous/next values.

mlmcc
0
 
fundsfAuthor Commented:
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
0
 
fundsfAuthor Commented:
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



0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now