jtheres3 Posted December 16, 2020 Share Posted December 16, 2020 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 Link to comment Share on other sites More sharing options...
gustavofarias Posted December 16, 2020 Share Posted December 16, 2020 What do you mean by incorrect? Different ordering? Do you have any grouping in your report? Link to comment Share on other sites More sharing options...
jtheres3 Posted December 16, 2020 Author Share Posted December 16, 2020 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. Link to comment Share on other sites More sharing options...
gustavofarias Posted December 16, 2020 Share Posted December 16, 2020 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? Link to comment Share on other sites More sharing options...
luked Posted December 17, 2020 Share Posted December 17, 2020 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. Link to comment Share on other sites More sharing options...
jtheres3 Posted December 17, 2020 Author Share Posted December 17, 2020 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' thento_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_hoursfrom Table_Namegroup by object_id,type_of_benefit,soft_benefit_classorder 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. Link to comment Share on other sites More sharing options...
gustavofarias Posted December 17, 2020 Share Posted December 17, 2020 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 Link to comment Share on other sites More sharing options...
jtheres3 Posted December 28, 2020 Author Share Posted December 28, 2020 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. Link to comment Share on other sites More sharing options...
jtheres3 Posted January 4, 2021 Author Share Posted January 4, 2021 In Jasper Studio, can we restrict a Row in the Detail band to be printed only once? Please advise. Thank You Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now