Get a sum of amounts according to another field

[lahiru_1]

I have two fields in the report, which are Amount and Currency. And I have to calculate the total for each currency. This report is for a financil application, therefore the currency can be anythn. And my problem is to get the total for each currency. This total has to be for each page and the whole report as well. How can solve the above problem. Please Help me. If there is any clarification of the problem, please ask. Thank you.

[Tom C]

You need to keep track of individual currency totals at report level and page level. For example, US dollar for page sum:

    <variable name="zero" class="java.math.BigDecimal">        <variableExpression><![CDATA[new BigDecimal("0.00")]]></variableExpression>    </variable>    <variable name="Sum_US" class="java.math.BigDecimal" resetType="Page" calculation="Sum">        <variableExpression><![CDATA["USD".equals($F{currency})?$F{amount}:$V{zero}]]></variableExpression>    </variable>[/code]

US dollar for report sum:

    <variable name="Total_US" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA["USD".equals($F{currency})?$F{amount}:$V{zero}]]></variableExpression>    </variable>[/code]

Then use them in your report layout in the page footer and summary section, respectively.

[lahiru_1]

Hi, I already tried that method. In that i have to define the currency as USD. But in my case there can be 100+ currencies. I have no idea which currencies will come as i take a web service to get the data. Is there a way to solve without having to manually define the currencies

[abc.hegde]

Just a hint, not tested - Create a group with expression $F{currency}. Create a variable for sum and say reset type is group and select the created group. 

 

[Tom C]

JR engine can do tally on a single field (in your case $F(Amount}), but it cannot tally field value based on another value (in you case $F{Currency}). You will have to tell JR how to keep track of each $F(Amount} based on $F{Currency} value. If we can assure that each page of your report only has one currecncy type, then you can simply sum $F(Amount} in a variable with reset type to page. But this will not help to show all currency totals in the summary in one single tally variable. JR does not know the relationship between $F(Amount}) and $F{Currency} - you have to tell it to track all currency totals for the report, for example tracking USD currency total for the report in $F{Total_USA}:

    <variable name="Total_US" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA["USD".equals($F{currency})?$F{amount}:$V{zero}]]></variableExpression>    </variable>[/code]

.. so as to track other currencies in seperate sum variables.

To satisfy your report design requrement, you will need to find a way to keep track of all possible 100+ currencies and tally them individually. The suggested approaches can be:

1) Rewirite your SQL query to provide all individual currency amounts in the record as Amount_USD, Amount_CanadianDollar,  Amount_MexicanPeso,...etc, thus JR can simply tally each individual amount field to provide page and report level summary information;

2) Create individual summary variables in the report for all possible currencies for page and report tally (as demonstrated in my previous answer);

3) Create a variable collection or an array in the report to track currency amount individually as the records come in, and tally them manually for report and page summary. This can be acheived by a Java class method inheriting JR as a scriptlet and use it in the report template.

 

The first two approaches are relatively simple, but tedious. The last suggestion requires Java programming effort with advanced JR knowledge to make it work - I do not have a sample code to show the approach but it should work (please understand the posting provided here are volunteer based and we do not always have time to write demo code). Hope this helps to explain and clarify the situation.

[lahiru_1]

@tchen i already tried the first two methods. They are working as expected. but the clients want as i mentioned earlier. And the company dont want me to specify the currencies which are coming. I havent tried the last suggestion. I will look into it. Hopefully it will be the answer im looking for. Thanx for the answers. I really appreciate it.