Working with Subreports

[bernie.obrigewitsch]

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  

[netti royala]

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.

[bernie.obrigewitsch]

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]

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.