Defining a variable in SQL SELECT statement in iReport

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

FROM table_name

Defining a variable in iReport : -

SELECT SUM(@tsum := @tsum + ProfitTotal) as Cumulative

FROM

    (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.

http://stackoverflow.com/questions/23731737/dynamic-variable-value-jasper-report/23742241#23742241