How can I sum distinct values?

[juan.ferreyra]

Hello everyone, first time asking here but I don't see this question already answered.

 

I have a tax report and I need a summary of the tax bases and the situation goes like this.

$F{taxBase} is the tax base.

$V{taxBaseAmt} is the sum of all $F{taxBase}, grouped by tax range. This is calculated as a separate group section and reset for each group. The group is called taxRate.

With this in mind, I have as many groups printed as tax ranges the DataSource has, and I need to sum al DISTINCT $V{taxBaseAmt} as a summary line.
I need to sum all DISTINCT values because the way the report is built, some compound tax ranges (with two different taxes) are broken down into to separated groups, but the tax bases are the same for both of them, and it should be summed up only once in the totals.

I've tried creating a new variable, with a scriptlet as expression but the evaluation of the expression is done for each record, so it doesn't seem usefull)
Also tried implementing a custom incrementer, but the same result, it seems that the calculation is done for every record (tried setting Increment Type for the group of taxRate but nothing changed).
My last attempt was to set the Initial Value Expression to a Scriptlet that adds the las value to a HashSet and then sum that values, but it thros the error I append at the end of this post.

The general idea is to mantain a HashSet with the values of $V{taxBaseAmt} and only sum to the variable when the value doesn't exists in the set, any new aproach is welcome.

Just let me know for any other information I could provide!

Thanks in advance.

 

ERROR:
 

 1. Type mismatch: cannot convert from void to Object value = ((com.smf.frilac.customizations.scriptlet.SumDistinctScriptlet)parameter_SumDistinct_SCRIPTLET.getValue()).addVal(); //$JR_EXPR_ID=24$ <----------------------------------------------------------------------------------------------------------------->

2. Type mismatch: cannot convert from void to Object value = ((com.smf.frilac.customizations.scriptlet.SumDistinctScriptlet)parameter_SumDistinct_SCRIPTLET.getValue()).addVal(); //$JR_EXPR_ID=24$ <----------------------------------------------------------------------------------------------------------------->

3. Type mismatch: cannot convert from void to Object value = ((com.smf.frilac.customizations.scriptlet.SumDistinctScriptlet)parameter_SumDistinct_SCRIPTLET.getValue()).addVal(); //$JR_EXPR_ID=24$ <-----------------------------------------------------------------------------------------------------------------> 3 errors

 

[anish.rai]

 

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[mbamania]

Hi,

It is hard to understand the requirement like this, can you please share a jrxml file as well as the requirement you are looking for. 

[juan.ferreyra]

Hi, the I added the jrxml.

You can abstract the problem to just doing something like the following SQL query:

SELECT sum(distinct(amount)) FROM ammounts

My dataset is sent from a Java process, and I can't modify the query that builds the dataset.

I've solved the problem by using a scriptlet. The reset expression of variable $V{taxBaseAmt} calls the script and adds its current amount if this amount is not yet added.

Then the value of variable $V{sumTaxBaseAmt} updates its value to that sum and that is the result I needed.

<?xml version="1.0" encoding="UTF-8"?> "dd/mm/yyyy" $F{taxBaseAmt} new BigDecimal("0") $F{taxAmt} new BigDecimal("0") $F{totalAmt} new BigDecimal("0") $F{taxBaseAmt} $P{SumDistinct_SCRIPTLET}.addVal() $F{taxAmt} $F{totalAmt} $P{SumDistinct_SCRIPTLET}.getDistinctSum() $F{taxAmt} 0 $F{totalAmt} 0 $F{taxId} $F{taxName} $V{taxBaseAmt} $V{taxAmt} $V{totalAmt} Totals by Tax Rate / Withholding $V{taxAmt} $V{totalAmt} $V{taxBaseAmt} $P{groupBy}.equals("Y")? $F{businessPartnerId}: "" new Boolean($P{groupBy}.equals("Y")) ($P{businessPartnersName}.equals("name2") && $F{businessPartner2} != null) ?$F{businessPartner2}:$F{businessPartner} Tax ID: $F{businessPartnerTaxId} new Boolean($P{groupBy}.equals("Y")) Totals by Business Partner $V{taxBaseAmtBP} $V{taxAmtBP} $V{totalAmtBP} $F{taxId} new Boolean($P{DETAIL}.equals("Y")) Name Rate Doc. No. new Boolean($P{groupBy}.equals("Y")) Doc. Date business Partner Acct. Date bP. Country bP. Region Tax Base Amount Tax Amount Total Document Amt. new Boolean($P{groupBy}.equals("N")) Tax Id: Doc. Type $F{taxId} new Boolean($P{DETAIL}.equals("N")) ]]></text> </staticText> <staticText> <reportElement key="staticText-10" mode="Opaque" x="573" y="0" width="64" height="12" forecolor="#FFFFFF" backcolor="#727070" uuid="6b846ce5-8008-405a-9456-fa1c100be829"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Right"> <font size="8"/> </textElement> <text><![CDATA[Tax Base Amount Tax Amount Total Document Amt. $P{TITLE} new Boolean($P{DETAIL}.equals("Y")) $F{taxName} $F{rate} $F{documentNo} $F{docType} new Boolean($P{groupBy}.equals("Y")) $F{docDate} $F{acctDate} ($P{businessPartnersName}.equals("name2") && $F{businessPartner2} != null) ?$F{businessPartner2}:$F{businessPartner} $F{bpCountry} $F{bpRegion} $F{taxBaseAmt} $F{taxAmt} $F{totalAmt} new Boolean($P{groupBy}.equals("N")) $F{businessPartnerTaxId} $V{sumTaxBaseAmt} TOTALES: $V{sumTaxAmt} $V{sumTotalAmt}