Got a question from a person. She wanted a running total only to show up when a certain limit or a multiple of that limit was crossed. In my example, this limit is 5000, Running Total is showing every time 5000 or a multiple of 5000 is crossed:
The example is based on the table Order_Details from the NorthWind database. First, I created a formula field Revenue:
{Order_Details.Quantity}*{Order_Details.UnitPrice}
In only managed to create the Running Total by by creating a Formula Field Running Total:
currencyvar curTotal;
currencyvar curSubtotal;
numberVar numCounter;
curTotal:=curTotal + {@Revenue};
//numCounter:= curTotal\{?RunningTotalLimit};
(
if {@Revenue} < {?RunningTotalLimit} then
(
if curTotal \ {?RunningTotalLimit} = numCounter + 1 and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=numCounter + 1
)
else
(
if curTotal \ {?RunningTotalLimit} >= numCounter + 1 and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=curTotal \ {?RunningTotalLimit}
)
else
curSubtotal:= 0;
)
)
else
(
if curTotal \ {?RunningTotalLimit} = numCounter + {@Revenue}\{?RunningTotalLimit} and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=numCounter + {@Revenue}\{?RunningTotalLimit}
)
else
curSubtotal:= 0;
)
);
curSubtotal
Communicating about it, I found out this formula is not working perfectly.
So I created two counters.
numCounter1:
currencyvar curTotal1;
numberVar numCounter1;
curTotal1:=curTotal1 + {@Revenue};
numCounter1:= curTotal1\{?RunningTotalLimit};
numCounter1
numCounter2:
currencyvar curTotal2;
numberVar numCounter2;
numCounter2:= (curTotal2- {?RunningTotalLimit})\{?RunningTotalLimit};
curTotal2:=curTotal2 + {@Revenue};
numCounter2
Last but not least I created a simple running Total on @Revenue and suppressed it with:
{@numCounter1}-{@numCounter2}=1 or {#RunningTotal}<2*{?RunningTotalLimit}
Only one flawleft. The first time the RunningTotalLimit is crossed is not shown.
You can download the example CrystalRunningtotal.rpt via:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
The example is based on the table Order_Details from the NorthWind database. First, I created a formula field Revenue:
{Order_Details.Quantity}*{Order_Details.UnitPrice}
In only managed to create the Running Total by by creating a Formula Field Running Total:
currencyvar curTotal;
currencyvar curSubtotal;
numberVar numCounter;
curTotal:=curTotal + {@Revenue};
//numCounter:= curTotal\{?RunningTotalLimit};
(
if {@Revenue} < {?RunningTotalLimit} then
(
if curTotal \ {?RunningTotalLimit} = numCounter + 1 and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=numCounter + 1
)
else
(
if curTotal \ {?RunningTotalLimit} >= numCounter + 1 and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=curTotal \ {?RunningTotalLimit}
)
else
curSubtotal:= 0;
)
)
else
(
if curTotal \ {?RunningTotalLimit} = numCounter + {@Revenue}\{?RunningTotalLimit} and curSubtotal = 0 then
(
curSubtotal:= curTotal;
numCounter:=numCounter + {@Revenue}\{?RunningTotalLimit}
)
else
curSubtotal:= 0;
)
);
curSubtotal
Communicating about it, I found out this formula is not working perfectly.
So I created two counters.
numCounter1:
currencyvar curTotal1;
numberVar numCounter1;
curTotal1:=curTotal1 + {@Revenue};
numCounter1:= curTotal1\{?RunningTotalLimit};
numCounter1
numCounter2:
currencyvar curTotal2;
numberVar numCounter2;
numCounter2:= (curTotal2- {?RunningTotalLimit})\{?RunningTotalLimit};
curTotal2:=curTotal2 + {@Revenue};
numCounter2
Last but not least I created a simple running Total on @Revenue and suppressed it with:
{@numCounter1}-{@numCounter2}=1 or {#RunningTotal}<2*{?RunningTotalLimit}
Only one flawleft. The first time the RunningTotalLimit is crossed is not shown.
You can download the example CrystalRunningtotal.rpt via:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Reacties