Skip to main content
G
gyannuzzi
Participating Frequently
July 30, 2009
Question

how to use external query in subreport?

  • July 30, 2009
  • 1 reply
  • 2527 views

I would like to use report builder to display a simple report containing a one to many relationship.

For example, a list of customer orders where each customer order can consist of many ordered items.

The queries for this report must be external.  Is this possible, and if so, how do I do it?

I was able to do it with embedded queries no problem.

Passing in a query for outer report works no problem.

I was able to pass in a query for the subreport, however, I cannot filter that subreport by the parent id.

Any ideas? Let me know if this is not clear enough, it seems like it should be a fairly common task/occurrence.

(I just started looking at report builder yesterday)

This topic has been closed for replies.

1 reply

G
gyannuzziAuthor
Participating Frequently
July 30, 2009

Straight from the cf8 livedocs, I see this ...

---------------------------

Note: You cannot specify a subreport query that depends on arguments from the master report. Instead, you can define a CFML function or CFC method that returns the subreport query given the arguments from the master report. ColdFusion calls this code when it executes the subreport.

---------------------------

so it appears the short answer is no, but there appears to be a workaround. Has anyone tried this workaround/can you tell me what the above text means precisely / how would I implement it?

H
hchung2010
Participant
December 22, 2009

Here is how I create the external query in the subreport:

0. Create your subreport. For the SQL query, you can use the built-in query (just to test the structure of your design) then later cut and paste your query to the external file. To filter out the criteria for your subreport, you need to add the "Parameters" for as many criteria as you want.

1. Design the report. For the SQL query, you can use the built-in query (just to test the structure of your design) then laater cut and paste your query to the external file. To filter out the criteria for the report, you need to add the "Parameters" for as many criteria as you want. (You also might have to add the criteria from the subreport so that you can later specify in the calling file).

Add your subreport, make sure to rename the subreport to something that you can reference later in the external code.

2. Then here is an example of how you will put everything together:

Example: Let's say you want to create a report named "CustomerReport.cfr" having a subreport "CustomerOrderReport.cfr". If you create a query file "qry_getCustomerInfo.cfm" which having the 2 SQL queries named "getCustomerInfo" and "getCustomerOrderInfo".

Here is how you will call the report:

<cfinclude template="qry_getCustomerInfo.cfm">

<cfreport template="CustomerReport.cfr" format="pdf" query="getCustomerInfo">

<cfreportparam name="whatevername that you created in parameter" value="#your passing value#">

<cfreportparam name="whatevername that you created in parameter" value="#your passing value#">

<cfreportparam name="whatevername that you created in parameter" value="#your passing value#">

<cfreportparam SUBREPORT="getCustomerOrder" query="getCustomerOrderInfo">

</cfreport>

Try to see whether this helps. Good luck.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices