Jasper Reports Groups and Calculating Percentages of each group of the total

I have a data series like the following

Year	Month	Item	SaleCount
2020	Jan	Apple	2
2020	Jan	Banana	3
2020	Feb	Apple	1
2020	Feb	Banana	3
2020	March	Apple	5
2020	March	Banana	0
2021	Jan	Apple	5
2021	Jan	Banana	3
2021	Feb	Apple	2
2021	Feb	Banana	7
2021	March	Apple	5
2021	March	Banana	2

I am grouping data By Year then By Month then By Item Type

I would like to to show the percentage sales of each item as a function of the monthly total. However this is problematic given I use a variable reset on the Month Grouping to count total sales for that month. Then I tried to use F{SalesCount}/v{Total_Monthly_Sales}/100

as the v{Percent_of_Total} But as that variable is still being calculated dynamicaly during the internal Item lines basically for the first item its 100 and then each subsequent item is a percentage of the incrementing count..and not valid. The Total Sales are really on valid when the monthly group has finished being generated. The only solution I can see is use a sql calculation to produce a monthly total field and use this in my percentage variable.

Solution

What you need to do is to use evaluationTime="Auto" for a text field that displays the percentage formula based on the current field value and the monthly group total.

Something like this:

<variable name="Total_Monthly_Sales" class="java.lang.Integer" resetType="Group" resetGroup="Month" calculation="Sum">
    <variableExpression><![CDATA[$F{SaleCount}]]></variableExpression>
</variable>
...
        <textField evaluationTime="Auto" pattern="#,##0.##">
            <reportElement x="400" y="0" width="100" height="30" uuid="42be2bf2-495e-4ec9-afbe-6ea99bb6ec34"/>
            <textFieldExpression><![CDATA[$F{SaleCount} * 100f / $V{Total_Monthly_Sales}]]></textFieldExpression>
        </textField>

Note this will only work if you directly use the field and variable in the text field expression, creating a separate variable for the percentage value will not allow you to use evaluationTime="Auto".