Working with Subreports

Posted on November 13, 2013 at 12:32pm

Hi  - fairly new to Ireports.  Am writing a report and need subreports.   My subreport works when i run test it, when I do the linking to the main report, the data does not return with the same values that it does when i run it by its self.    The main report only returns the provider name , grouped by provider and Measure.  the data is on the Provider Group ( only want 1 line returned per provider).

The subreport has the same groupings,  and is returning summary data for the provider.  I have tried placing the data on the subreport, in the detail, measures and provider but it returns the same data each time..

Currently , i'm linking the provider and the begin and end dates.   iF I add one for  MEASURE-ID the data still is incorrect.  

Both supreports are attached

I'm not sure what i'm doing wrong with the linking to make this happen.   

SQL from main report: 

SELECT
     Measure.`MeasureID` AS Measure_MeasureID,
     Measure.`MeasureName` AS Measure_MeasureName,
     Measure.`Target_P` AS Measure_Target_P,
     Report.`ReportID` AS Report_ReportID,
     Report.`ProName` AS Report_ProName,
     Report.`MeasureID` AS Report_MeasureID,
      SUM(Numerator),
     SUM(Denominator),
     Report.`Percent` AS Report_Percent,
     Report.`Exclude` AS Report_Exclude,
     Report.`Repdatetime` AS Report_Repdatetime,
     Report.`PassFail` AS Report_PassFail,
     Report.`ASDate` AS Report_ASDate
FROM
     `Measure` Measure INNER JOIN `Report` Report ON Measure.`MeasureID` = Report.`MeasureID`
WHERE
     Report.`ASDate` >= $P{BeginDate}
     and Report.`ASDate` <= $P{EndDate}
     and Denominator > 0
Group by
Report.`ProName` ,
    Measure.`MeasureName`
    ORDER BY
     Report.`ProName` ASC,
    Measure.`MeasureName`,
     Report.`ASDate` ASC
 
 
SQL from sub report :  data on report is the avg-percent, measure-target-p .  the AVG=Percent does not return to the main report correctly
SELECT
     SUM(Numerator),
     SUM(Denominator),
     Round(AVG(Percent),2) AS AVG_Percent,
     Measure.`MeasureID` AS Measure_MeasureID,
     Measure.`MeasureName` AS Measure_MeasureName,
     Measure.`Target_P` AS Measure_Target_P,
     Report.`ProName` AS Report_ProName,
     Report.`MeasureID` AS Report_MeasureID,
     Report.`Percent` AS Report_Percent,
     Report.`ASDate` AS Report_ASDate,
     Report.`Denominator` AS Report_Denominator,
     Report.`Numerator` AS Report_Numerator
FROM
     `Measure` Measure INNER JOIN `Report` Report ON Measure.`MeasureID` = Report.`MeasureID`
WHERE
    Denominator > 0 
     and Report.`ASDate` >= $P{BeginDate}
     and Report.`ASDate` <= $P{EndDate}
     and Report.`ProName` LIKE $P{Provider}
     and  Report.`MeasureID` = 1

Please help me with any suggestions or if you need more information  

Attachments: 
Binary Data ochc_measures_01_subrpt.jrxml
Binary Data new_ochc_measures_with_subreports.jrxml
iReport Designer
bernie.obrigewitsch's picture
bernie.obrigewitsch
44
Joined: Nov 13 2013 - 12:19pm
Last seen: 9 years 1 day ago

3 Answers:

Posted on November 13, 2013 at 8:19pm
1.When ever you want to display data after completing the main report data ,then you should keep the sub report in Summary Section. 2.If you want to display data along with Parent report then place sub report in detail section. 3.sub report contains any parameters ,then you should define those parameters in parent report. 4.finally go to the main report --->Right click on the sub report -->>go to properites window --> select connection paramters -->>defined in parenet report (only which are using in sub report.) 5.Then Execute,it perfectly works.
netti royala's picture
netti royala
723
Joined: Oct 28 2013 - 11:43pm
Last seen: 8 years 5 months ago
Posted on November 14, 2013 at 6:05am

OK thank you for you answer.. I will relook at how I did this, but thought I had completed all of these steps.  The issue I think I'm having is, in the subreport I only want to pull Measure 1, while the main report is pulling all measures.  I dont now how to only pass the 1 to the subreport to only get that data.   I will be adding more subreports pulling 10 more measures, they will be indentical to the first, just pulling a different measure.. So once i get the one done, the rest will fall into place. 

I will post what i find out once i relook at what i did in the 2.

 

bernie.obrigewitsch's picture
bernie.obrigewitsch
44
Joined: Nov 13 2013 - 12:19pm
Last seen: 9 years 1 day ago
Posted on November 14, 2013 at 7:25am

I did get it working.  by defining the Measureid in the main report, and assigning a default value of 1 to it..    I have my subreport data in the detail on the main report my subreport is placed in the Provider Group footer.   Thank you for the help.  

bernie.obrigewitsch's picture
bernie.obrigewitsch
44
Joined: Nov 13 2013 - 12:19pm
Last seen: 9 years 1 day ago
Feedback
randomness