Jasper Report Results and Query Results are not matching

[jtheres3]

Hi Team, We have a scenario where in Query results from the SQL Developer app gives the corrrect data but when the same Query is used within the Jasper Report the results are incorrect. Have you ever faced this. Can you please guide in troubleshooting the issue.

Thanks

[gustavofarias]

What do you mean by incorrect? Different ordering? Do you have any grouping in your report?

[jtheres3]

Thank you for the response. There is no ordering issue. We have grouping in our Report.

By incorrect I refer the following --We have a Total row which should ideally display the sum of column values that satisfy a condition, but the total sum is not displayed in the row, instead it is blank. 

[gustavofarias]

 

Are you using a variable with calculation type SUM? can you show a print of your result? or part of your code so we can see the grouping expression and the variable expression?

[luked]

As gustavofarias stated above if you have a variable with a calculation you must ensure this is at the botom of your variable list as variables will be calulated in the order they are placed.

[jtheres3]

Currently we are not using Variable to calculate  SUM. The SQL Query is designed to pull all the column values and calculate the Sum of column values that satisfy a condition. We are just mapping the Fields to the Report Columns and Total Row. The issue occurs only when there report is generated for more than 1 id. 

 

Summation is handled in the Query below.

 

Select 

object_id,

case when type_of_benefit='Hard' then to_char(round(nvl(sum(q1_plan_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q1_plan_hours),0),3))
end q1_plan_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q2_plan_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q2_plan_hours),0),3))
end q2_plan_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q3_plan_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q3_plan_hours),0),3))
end q3_plan_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q4_plan_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q4_plan_hours),0),3))
end q4_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then
to_char(round(nvl(sum(q1_plan_hours),0),2)+round(nvl(sum(q2_plan_hours),0),2)+round(nvl(sum(q3_plan_hours),0),2)+round(nvl(sum(q4_plan_hours),0),2))
else null end )

else to_char(round(nvl(sum(q1_plan_hours),0)/1000000,3)+round(nvl(sum(q2_plan_hours),0)/1000000,3)+round(nvl(sum(q3_plan_hours),0)/1000000,3)+round(nvl(sum(q4_plan_hours),0)/1000000,3),'$9,999,999,990.999')||'M'

end annual_total_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q1_plan_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q1_plan_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q1_total_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q2_plan_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q2_plan_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q2_total_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q3_plan_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q3_plan_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q3_total_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q4_plan_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q4_plan_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q4_total_plan_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q1_plan_hours),0),2)+round(nvl(sum(q2_plan_hours),0),2)+round(nvl(sum(q3_plan_hours),0),2)+round(nvl(sum(q4_plan_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class)) 
else null end )

else to_char(sum(round(nvl(sum(q1_plan_hours),0)/1000000,3)+round(nvl(sum(q2_plan_hours),0)/1000000,3)+round(nvl(sum(q3_plan_hours),0)/1000000,3)+round(nvl(sum(q4_plan_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M'

end annual_t_plan_hours,

case when type_of_benefit='Hard' then to_char(round(nvl(sum(q1_actual_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q1_actual_hours),0),3))
end q1_actual_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q2_actual_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q2_actual_hours),0),2))
end q2_actual_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q3_actual_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q3_actual_hours),0),3))
end q3_actual_hours,
case when type_of_benefit='Hard' then to_char(round(nvl(sum(q4_actual_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 
else to_char(round(nvl(sum(q4_actual_hours),0),3))
end q4_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(round(nvl(sum(q1_actual_hours),0),2)+round(nvl(sum(q2_actual_hours),0),2)+round(nvl(sum(q3_actual_hours),0),2)+round(nvl(sum(q4_actual_hours),0),2))
else null end )

else to_char(round(nvl(sum(q1_actual_hours),0)/1000000,3)+round(nvl(sum(q2_actual_hours),0)/1000000,3)+round(nvl(sum(q3_actual_hours),0)/1000000,3)+round(nvl(sum(q4_actual_hours),0)/1000000,3),'$9,999,999,990.999')||'M' 

end annual_total_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q1_actual_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q1_actual_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q1_total_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q2_actual_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q2_actual_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q2_total_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q3_actual_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q3_actual_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q3_total_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' then 
to_char(sum(round(nvl(sum(q4_actual_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class))
else null end )

else to_char(sum(round(nvl(sum(q4_actual_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end q4_total_actual_hours,

case when type_of_benefit='Soft' then (case when lower(soft_benefit_class)='number' 
then to_char(sum(round(nvl(sum(q1_actual_hours),0),2)+round(nvl(sum(q2_actual_hours),0),2)+round(nvl(sum(q3_actual_hours),0),2)+round(nvl(sum(q4_actual_hours),0),2)) over (partition by object_id,type_of_benefit,soft_benefit_class)) 
else null end )

else to_char(sum(round(nvl(sum(q1_actual_hours),0)/1000000,3)+round(nvl(sum(q2_actual_hours),0)/1000000,3)+round(nvl(sum(q3_actual_hours),0)/1000000,3)+round(nvl(sum(q4_actual_hours),0)/1000000,3)) over (partition by object_id,type_of_benefit),'$9,999,999,990.999')||'M' 

end annual_t_act_hours

from 

Table_Name

group by 
object_id,
type_of_benefit,
soft_benefit_class

order by object_id,
type_of_benefit
 

 

Please see below the group expression from the jrxml.

 

<group name="Project" isStartNewPage="true">
        <groupExpression><![CDATA[$F{OBJECT_ID}]]></groupExpression>
        <groupHeader>
            <band height="77">
                <textField>
                <textFieldExpression><![CDATA["Type of Benefit"]]></textFieldExpression>
                
                
                </textField>
                
                </band>
        </groupHeader>
        <groupFooter>
            <band>
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
            </band>
        </groupFooter>
    </group>
                
            <group name="Benefit Type">
        <groupExpression><![CDATA[$F{TYPE_OF_BENEFIT}]]></groupExpression>
        <groupHeader>
            <band>
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
            </band>
        </groupHeader>    
        
        <groupFooter>
            <band height="40">
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
                <printWhenExpression><![CDATA[$F{TYPE_OF_BENEFIT}.equals("Hard")]]></printWhenExpression>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                    
                    <textFieldExpression><![CDATA["Total Hard Benefit"]]></textFieldExpression>
                </textField>
                
                </textField>
            </band>
            <band height="40">
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
                <printWhenExpression><![CDATA[$F{TYPE_OF_BENEFIT}.equals("Soft")]]></printWhenExpression>
                
                    <textFieldExpression><![CDATA["Total Soft Benefit Number"]]></textFieldExpression>
                </textField>
                
                
            </band>
        </groupFooter>
                
                <group name="Soft Benefit Class ">
        <groupExpression><![CDATA[$F{Soft_Benefit_Class}]]></groupExpression>
        <groupHeader>
            <band>
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
            </band>
        </groupHeader>
        <groupFooter>
            <band>
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
            </band>
        </groupFooter>
    </group>

 

Attaching the sample Report output.

[gustavofarias]

Why are you grouping in your SQL and then again in your report?

From what I can get, you are grouping in the exact same columns. If you have already gruped in the SQL, you just need to use the detail band

[jtheres3]

Thank you and that really worked. I just used the Detail band to show all the individual row values & the Total rows, the result values are correct. But the Total rows are printed multiple times with the invidual rows. 

How can we restrict the Total rows to be displayed only once? Please advise. Attached the screenshot for reference.

[jtheres3]

In Jasper Studio, can we restrict a Row in the Detail band to be printed only once? Please advise. 

Thank You