In this blog you will see how to define a variable in SQL SELECT statement and use that variable to do the calculations for other computed values.
Defining variables in MySQL or in other database is easy but defining variable in iReport is totally different.
I show you both way how to define variable in MySQL SELECT statement and in iReport.
Defining variable in MySQL: -
SET @tsum := 0;
SELECT SUM(@tsum := @tsum + ProfitTotal) as Cumulative,
SUM(profit) as ProfitTotal
Defining a variable in iReport : -
SELECT SUM(@tsum := @tsum + ProfitTotal) as Cumulative
(SELECT profitTotal, @tsum :=0
FROM table_name ) a
As you can see first I have defined the variable in inner select statement then you can sum up in the outer select statement.