Highlighted

create Report charting data series

Engaged ,
Sep 12, 2014

Copy link to clipboard

Copied

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

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

TOPICS
Reporting

Views

888

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

create Report charting data series

Engaged ,
Sep 12, 2014

Copy link to clipboard

Copied

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

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

TOPICS
Reporting

Views

889

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 12, 2014 0
Advocate ,
Sep 16, 2014

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2014 0
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
Advocate ,
Sep 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0
iccsi LATEST
Engaged ,
Sep 17, 2014

Copy link to clipboard

Copied

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2014 0