create Report charting data series

[iccsi]

I want to add chart to my report and add a chart control on my report.

I double click on the chart and edit chart type as line chart and edit chart series.

I use Data from a fix of values and choose the field name from the drop down from my data source field name, but I got following error message.

An error occurred while trying to create a chart on this report.

Ensure that the column(s) selected for the chart series are numerical values. Error: The chart could not be generated due to an error in the graphing engine.

I tried to select from data from query. I have to build my query from report.

I use a stored procedure for this report, so I need advance to build my query, once I use the stored procedure then Report Builder warn me that it will erase all fields to recreate fileds.

I would like to know what is the right procedure to create chart for the report.

Your help and information is great appreciated,

Regards,

Iccsi

[EddieLotter]

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

[iccsi]

Thanks for your information and help,

It works to use <cfquery>, but it gets error message when I change it to cfstoreproc.

<cfset ReportQuery.Q = getQ()>

  <cfreport format = "PDF" template="MyTest.cfr"  overwrite="Yes" />

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

  <cfset var qq = ''>

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

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

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

    </cfstoredproc>

  <cfreturn qq>

</cffunction>

Thanks again for helping,

Regards,

Iccsi

Invalid token c found on line 76 at column 5.

The CFML compiler was processing: A cfstoredproc tag beginning on line 75, column 5. A cfstoredproc tag beginning on line 75, column 5.

[EddieLotter]

   <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.

[iccsi]

Thanks a million for the information and help,

The report runs, but it does not show the data from stored procedure which is from table from the query.

It looks like stored procedure did not execute.

I need pass 2 data sources to the report, one is for the tables on the report and one is for the chart.

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

Thanks again for helping,

Regards,

Iccsi,

[EddieLotter]

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.

[iccsi]

Thanks a million for the information and help,

It seems that the chart query does not pass to the report for some reason.

The chart always show the data from the data from the query, not from stored procedure.

I save query as a variable, but it does not show the variable when I re open the report from report builder.

Are there any particular way to save the variable on the report for the chart?

Thanks again for the information and help,

Regards,

Iccsi,

[EddieLotter]

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.

[iccsi]

I have following code to generate the report.

mydata is source or report and getQ is the source of chart.

The report running and mydata correct on the report, but the chart does not get result from MyChartSP.

Do I have any thing wrong from the code following?

Thanks a million again for helping and information,

Regards,

Iccsi,

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

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

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

  </cfstoredproc>

  <cfset Request.Q = getQ()>

   <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>

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

  </cfreport>

[EddieLotter]

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>

[EddieLotter · Accepted solution]

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

[iccsi]

Thanks a million for the information and help,

It works now,

Does ColdFusion Report Chart support sub report?

I mean that I want to place the chart on the Group Footer and only shows all details record of this report.

Thanks a million again for help and information,

Regards,

Iccsi,

[EddieLotter]

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.

[iccsi]

Thanks a million again for helping and information,

Yes, according to ColdFusion Report document, it seems that it supports Sub Report and Sub Chart.

I will give it a try,

Thanks again for helping and information,

Regards,

Iccsi,