sub report data source at run time

iccsi wrote:

 

I would like to know any way to set sub report and main report to run a report which has sub report on it,

If I understand you correctly, you want to pass a result-set to your subreport from the calling CFM script.

To do so, you need to give your subreport a unique name in the Report Builder and then you can do something like the following:

<cfreport format="PDF" template="MainReport.cfr" query="#mydata#">

     <cfreportparam subreport="SubReportName" query="#mysubStored#">

</cfreport>

iccsi wrote:

 

It seems that the subreport query does not follow the order by in the stored procedure.

This is a different problem. It is best to start new threads with new problems.

Are you sure the result-set is sorted the way you expect? The reason I ask is because I don't think the report changes the result-set.

An easy way to verify is to look at the result-set returned from your stored proc to be sure what it looks like, for example:

<cfdump var="#mysubStored#">

<cfabort>

What was the result when you did the test I recommended?

iccsi wrote:

 

cfdump get sort by id, but sub report still sort by code,

 

This suggests that you have a mismatch between what you named the subreport and what you are using in the cfreportparam tag.

Open your report and click on the subreport object to select it. In the "Properties" window, in the "Data" section, look at the "Name" property. This is the name you must use in the cfreportparam tag, not the file name of the subreport.

iccsi wrote:

 

The difference is mysubreport in the cfm file, and mysubreport.cfr in the report.

Should I include crf extention for subreport on cfm file?

No, you don't call the subreport directly from the cfm file, it is already embedded in your main report.

I don't think you understood my last post. Please read it again and check that what I say is true for your situation.

iccsi wrote:

 

the subreport in cfreportparam is SubReportName and in the Report Builder

is SubReportName.cfr.

This is where I think you are going wrong. You are using the file name of your subreport rather than the element name of your subreport in your main report.

Unless you explicitly changed the name of the subreport element in your main report, it will have an automatically generated name something like "TReportSubReport1".

Did you change the name of the subreport element in your main report?

iccsi wrote:

 

I got it, I need use TReportSubReport in the name field in the report builder to put it cfreporparam, thanks a million again for helping,

Correct. Remember, you can change the name to something more meaningful, especially if you have a complex report with multiple subreports. Just remember to change cfreportparam to match the change.