动态变量值jasper报表

Question

我不知道这个问题怎么命名，我知道这只是一个简单而愚蠢的逻辑需要整理，但我可以解释我需要什么。我有一个jasper报告脚本和查询，在其中我需要根据从下面的查询中收集的值进行一个简单的计算

SELECT TOTAL, PARTIAL FROM PRICE WHERE TOTAL > 0

现在我需要在jasper报表脚本中计算这个值作为下面的表达式

VAR CALC += TOTAL + (PARTIAL) //as partial can be a -ve or +ve value

目前的情况是，我没有找到一种方法来做到这一点，每当我给一个变量赋值total并尝试使用它时，它总是从查询中获取值并进行计算，而我只需要它在那里出现一次，然后再对它进行计算。为此，我尝试使用calculation=“first”，但这也始终给出第一个值并继续。我希望我能很好地解决我的问题，请帮助

编辑

查询

SELECT RECEIPTS.DATENEW AS DATE,
       TICKETS.TICKETID AS TICKETID,
       PAYMENTS.PAYMENT AS PAYMENT,
       PAYMENTS.METHOD AS METHOD,
       PAYMENTS.TOTAL AS TOTAL,
       CUSTOMERS.NAME AS NAME,
(SELECT SUM(P.TOTAL) FROM PAYMENTS AS P
    INNER JOIN RECEIPTS AS R ON P.RECEIPT = R.ID 
    INNER JOIN TICKETS AS T ON R.ID = T.ID 
    INNER JOIN CUSTOMERS AS C ON T.CUSTOMER = C.ID 
    WHERE C.ID = CUSTOMERS.ID and P.PAYMENT IN ('debt','debtpaid', 'advance', 'cashrefund')) AS CTOTAL
    FROM RECEIPTS
          INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
          INNER JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
          INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
    WHERE   
          PAYMENTS.PAYMENT IN ('debt', 'debtpaid', 'advance', 'cashrefund')
....
....    
WHERE -TOTAL > 0

变量

<variable name="DUES" class="java.lang.Double" resetGroup="Customer" resetType="Group" calculation="Nothing">
        <variableExpression><![CDATA[$F{CTOTAL} + $F{TOTAL}]]></variableExpression>
        <initialValueExpression><![CDATA[new Double(0.0)]]></initialValueExpression>
</variable>

输出

Answer 1

您可以在SQL语句中定义变量本身，如下所示：-

   SELECT SUM(@csum := @csum + TOTAL+PARTIAL)
   FROM (SELECT TOTAL, PARTIAL,@csum := 0
         FROM PRICE WHERE TOTAL > 0 
         ) a;

查看此问答