I need to calculate how much cars has been left parking and how many arrived.
How do you compare two lines between them? How do I write expression?
Example
| date | occupied |
| 73 | |
| 74 | |
| 73 |
result
left car : 1
(73-74)
arrived: 1
(74-73)
thank you.
Best regards.
R.
1 Answer:
Posted on November 4, 2015 at 2:48am
You will need to use Groups and Variables if you do not want to use SQL.
- Create a group with an expression : $V{REPORT_COUNT}%2+1
- Create a variable : ($V{REPORT_COUNT}%2==1)?$F{occupied}:-$F{occupied} (assuming occupied is an Integer)
- Calculation: SUM
- Incrementation : Group
- Reset : Report
Then Drag the Variable into the Group footer (or Detail) set the Evaluation Time to Group you will get the expected result.
FYI $V{REPORT_COUNT} is like a row count .
Oups I did not ask if you were trying to achieve this using Jaspersoft Studio?
If there’s data recording multiple days of information of the usage of a parking garage (for example, 73,74,73,70 for a certain day), and we need each day’s numbers of cars that get into and out the garage, we can do the calculation this way: Group the data by dates; in each group we subtract the number of parked cars in each member from this number in the next member (73,1,-1,-3), get the remainders from the second member to the last (1,-1,-3), and then, to get the number of cars that get into the garage, calculate the sum of the numbers that are greater than 0 (1=1), and to have the number of cars that go out of it, calculate the sum of the numbers that are less than 0 and get the opposite number ((-1-3)*-1=4).
This is relatively complicated, so we’d better generate a two-dimensional table according to this algorithm to make the data source ready. Then we can just present the result set in Jasper. As it’s difficult to implement it in SQL, we could do it in esProc with simpler code:
A1=$select date,occupied from tb
A2=A1.group(date;t=(~.(occupied-occupied[-1]).to(2,)),t.select(~>0).sum():arrived, t.select(~<0).sum()*-1:left)
Like calling a database, Jasper can integrate the esProc code via JDBC.