I believe the following link will help you: http://helpx.adobe.com/coldfusion/kb/coldfusion-report-builder-charts-dynamic.html

   <cfstoredproc procedure= "MyStoredProcedure" datasource="MySource"

You are missing a closing angle-bracket on this line, however it may be a typographical error in your post.

   <cfprocresult name="mydata" resultset="1">

Change name="mydata" to name="qq" so that your function does not return an empty variable.

Is it possible to pass 2 data sources to the report?

Yes, that is why the chart's query is assigned to a request variable. The query that you pass to the report is the query parameter of the cfreport tag.

I just spotted another error in your CFML.

Change the following line

<cfset ReportQuery.Q = getQ()>

to

<cfset request.Q = getQ()>

The request.Q variable is the variable that you should have assigned to the chart as described in the link I posted earlier.

For one thing, remove the number signs around mydata in the cfreport tag.

Let's make sure your chart's stored procedure is returning data. Try the following and see if you get the expected result set. My changes are in bold:

<cfstoredproc procedure = "MySP" datasource = "MySource">

  <cfprocparam value = "#form.MyValue#" CFSQLTYPE = "cf_sql_ineteger">

  <cfprocresult name="mydata" resultset="1">

</cfstoredproc>

<cffunction name="getQ" returntype="query">

   <cfset var qq = ''>

   <cfstoredproc procedure= "MyChartSP" datasource="MySource">

     <cfprocparam value = "#form.MyValue#" CFSQLTYPE = "cf_sql_integer">

     <cfprocresult name="qq" resultset="1">

   </cfstoredproc>

  <cfreturn qq>

</cffunction>

 

<cfset Request.Q = getQ()><!--- I moved this after the function definition just to be sure. --->

 

<cfdump var="#Request.Q#">

<cfabort>

 

<cfreport format = "PDF" template="MyReport.cfr"  overwrite="Yes" query = "mydata">

</cfreport>

Please forgive me, I never personally used the method documented in the link I provided. I have reviewed how I achieved the same thing and I did it differently.

Click on your chart in the Report Builder and in the Properties window give it a Name such as MyChart.

Then change your CFML to the following:

<cfstoredproc procedure = "MySP" datasource = "MySource">

  <cfprocparam value = "#form.MyValue#" CFSQLTYPE = "cf_sql_ineteger">

  <cfprocresult name="mydata" resultset="1">

</cfstoredproc>

 

<cfstoredproc procedure= "MyChartSP" datasource="MySource">

  <cfprocparam value = "#form.MyValue#" CFSQLTYPE = "cf_sql_integer">

    <cfprocresult name="MyChartData" resultset="1">

</cfstoredproc>

<cfreport format = "PDF" template="MyReport.cfr"  overwrite="Yes" query = "mydata">

  <cfreportParam chart="MyChart" series="1" query="MyChartData">

</cfreport>

See the details in the ColdFusion documentation here:

Adobe ColdFusion 9 * cfreportparam

It works now,

That's good to hear.

Does ColdFusion Report Chart support sub report?

I have not done it myself, but the documentation says the name of the chart refers to a chart in a report or sub-report, so it seems that ColdFusion will find the chart as long as it is named correctly.