jtheres3

We have a requirement where we need to retrive the Saved input parameters of a Report from the Jaspersoft Database schema. I see JIReportOptionsinput table holds Saved Report values. But it is in BLOB format. Can you please guide how to retrieve this BLOB data from JIReportOptionsinput Table

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

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.

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.

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.

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