sub report data source at run time

[iccsi]

I have a sub report and on the main report and I want to set up sub report and main report data soruce using stored procedures.

I have following code, it only runs sub report, but not main report has sub report on it.

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

Your help and information is great appreciated,

Regards,

Iccsi, 

<cfreport format = "PDF" template= "subReport.cfr"

      query = "#mysubStored#">

  </cfreport>

   <cfreport format = "PDF" template= "MainReport.cfr"

      query = "#mydata#">

  </cfreport>

  </CFIF>

[EddieLotter · Accepted solution]

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]

Thanks for the information and help,

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

I want it sort by code, but it comes different order which does not make sense for the report.

Are there any particular way to make sure subreport query follow my order clause in the stored procedure?

Thanks again for helping,

Regards,

Iccsi,

[EddieLotter]

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>

[iccsi]

Thanks for the information and help,

This is a footer report which is simple one table and my code is sort by ID, but the result set sort by code.

Thanks again for helping,

Regards,

Iccsi,

[EddieLotter]

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

[iccsi]

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

It seems that cfdump run the stored procedure, but sub report get from table, not from stored procedure,

Regards,

Iccsi,

[EddieLotter]

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]

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

Should I include crf extention for subreport on cfm file?

Regards,

Iccsi,

[EddieLotter]

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]

I use following code to call main report and subreport using cfreportparam.

the subreport in cfreportparam is SubReportName and in the Report Builder

is SubReportName.cfr.

Thanks again for helping,

Regards,

Iccsi,

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

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

</cfreport>

[EddieLotter]

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]

Thanks a million for the information and help,

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,

Regards,

Iccsi,

[EddieLotter]

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.