• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

adding numeric decimals

New Here ,
Apr 21, 2006 Apr 21, 2006

Copy link to clipboard

Copied

update: ok this is working I do see .003 when i query the field but i do not see it when using "cfchart" and "cfchartseries" .

any ideas?

===============================
This is a two point question.

1) How do I add adding numeric decimals into my database.

Right now I am doing this

<cfquery name="UpdateData" datasource="MySource">
UPDATE Metrics
SET
Gross_Square = #NumberFormat(Gross_Square ,"__.___")#
</cfquery>

if I place an abort on this code it looks as if cold fusion is sending a .003 into the database.

When i query the database after words its a 0.00 its not grabing the .003

I am using sql field "decimal" and tried setting the scale to 2 and also tested 3

any ideas?


TOPICS
Advanced techniques

Views

1.4K

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
community guidelines
Community Expert ,
Apr 23, 2006 Apr 23, 2006

Copy link to clipboard

Copied

Might be an idea to change the column's datatype to "float".

Votes

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
community guidelines
New Here ,
Apr 24, 2006 Apr 24, 2006

Copy link to clipboard

Copied

This did not help. I can see the data on a query coming out as .003 but when i run it in "cfchart" it still only shows .00 does cfchart not allow a .003 value?

Votes

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
community guidelines
Community Expert ,
Apr 24, 2006 Apr 24, 2006

Copy link to clipboard

Copied

> This did not help.
I don't understand. You observed before, "When i query the database after words its a 0.00 its not grabing the .003", and after, "This did not help. I can see the data on a query coming out as .003 ". Are you now getting 0.003 from the database?

cfchart would automatically sort the values on an axis. If, for example, 3 and .003 occur on the same axis, cfchart might interprete .003 as 0 when compared to 3.

Votes

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
community guidelines
New Here ,
Apr 24, 2006 Apr 24, 2006

Copy link to clipboard

Copied

Thanks for looking at this. Ok here is an example I set up to show what my database is pulling from its query


http://64.243.182.238/test.cfm

the top example shows how the cfchart displays the results the bottom part shows how a cfoutput shows the results. As you can see for year 2005 I can not get the .003 to show up in the graphics. For some reason the chat cuts off the .003 and makes it just .00

any idea why it would do this?

Votes

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
community guidelines
Community Expert ,
Apr 25, 2006 Apr 25, 2006

Copy link to clipboard

Copied

any idea why it would do this?
The difference in scale. The y-axis of the chart in test.cfm goes up to $1.50. You divide the y-axis into 3 units of 0.5. Therefore 0.003 is about half a percent of one unit of the y-axis. I think that is why Coldfusion just rounds it off to zero.

The following code shows that, with proper scaling, cfchart can handle 0.003.
<cfchart format="flash"
xaxistitle="Year"
yaxistitle="Total Cost of Air Pollution Compliance Program Per Gross Square Feet of All Buildings, in $">
<cfchartseries type="bar">
<cfchartdata item="2004" value="0.032">
<cfchartdata item="2005" value="0.003">
</cfchartseries>
</cfchart>

Votes

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
community guidelines
New Here ,
Apr 26, 2006 Apr 26, 2006

Copy link to clipboard

Copied

sorry to be so confused.

Right now my cfchartseries looks like this


<cfchartseries
type="bar"
query="GetData"
itemColumn="MetricsYearText"
valueColumn="#varValues#"
seriescolor="##33CC99"
seriesLabel="test"
paintstyle="shade"/>

are you saying to add this

<cfchartseries
type="bar"
query="GetData"
itemColumn="MetricsYearText"
valueColumn="#varValues#"
seriescolor="##33CC99"
seriesLabel="test"
<cfchartdata item="2004" value="0.032">
<cfchartdata item="2005" value="0.003">
paintstyle="shade"/>


I seem to get an error when ido this. Also how will this be then dynamic?


Here is my whole chat code


<cfchart
rotated="yes"
chartHeight = "150"
xAxisTitle = "#GraphName#"
labelFormat = "#labelFormat#"
scaleFrom=#scaleFrom#
scaleTo=#scaleTo#
font="arial"
fontSize=12
fontBold = "no"
gridLines=4
show3D="yes"
foregroundcolor="##000066"
databackgroundcolor="##FFFFCC"
chartwidth="600">

<cfchartseries
type="bar"
query="GetData"
itemColumn="MetricsYearText"
valueColumn="#varValues#"
seriescolor="##33CC99"
seriesLabel="test"
paintstyle="shade"/>
</cfchart>

Votes

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
community guidelines
Community Expert ,
Apr 26, 2006 Apr 26, 2006

Copy link to clipboard

Copied

Start with the following, which is just a slight modification of your code, and develop it from there.

<cfchart
rotated="yes"
chartHeight = "150"
xAxisTitle = "year"
yAxisTitle = "Total Cost of Air Pollution Compliance Program Per Gross Square Feet of All Buildings, $"
labelFormat = "number"
font="arial"
fontSize=12
fontBold = "no"
gridLines=4
show3D="yes"
foregroundcolor="##000066"
databackgroundcolor="##FFFFCC"
chartwidth="600">

<cfchartseries
type="bar"
query="GetData"
itemColumn="MetricsYearText"
valueColumn="varvalues"
seriescolor="##33CC99"
seriesLabel="test"
paintstyle="shade"/>
</cfchart>

Votes

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
community guidelines
New Here ,
Apr 26, 2006 Apr 26, 2006

Copy link to clipboard

Copied

ok i changed it out now its now showing any values at all?

http://64.243.182.238/test.cfm

Votes

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
community guidelines
Community Expert ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

My suggestion assumes that the names of the database columns returned by the query are MetricsYearText and varvalues , respectively, for year and total cost. Is it so?


Votes

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
community guidelines
Community Expert ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

... something like this

Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

it works now on the test but if i take away where you set the data to certain values it will not pull the data from the database. There has to be a way to do this where the data can come from a database no?

Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

I mean this way you set up works really well but only as long as the data is pre-set up.

Example you gave

<!--- Create two-column query, datatypes varchar for year, decimal for varValues--->
<cfset getData = QueryNew("MetricsYearText, varValues", "varchar, decimal")>
<!--- Make 2 rows in the query --->
<cfset newRow = QueryAddRow(getData, 2)>
<!--- Set cell values --->
<cfset dummy = QuerySetCell(getData, "MetricsYearText", "2004", 1)>
<cfset dummy = QuerySetCell(getData, "varValues", 0.032, 1)>
<cfset dummy = QuerySetCell(getData, "MetricsYearText", "2005", 2)>
<cfset dummy = QuerySetCell(getData, "varValues", 0.003, 2)>

do i have to some how use this code to set values on the out put of the database?

sorry i am so confused on this one.

also if you notice i changed the scale to .2 and you can see in my test example if now shows it sees values there but still shows only 0.00 on the graph. Not sure why.

Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

looking at this over and over again the only differece between what you have and I have is you are setting the values and on my query the values are being cut on by cfm.

I tried this

<cfchartseries
type="bar"
query="GetData"
itemColumn="MetricsYearText"
valueColumn="#NumberFormat(varValues ,"__.___")#"
seriescolor="##33CC99"
seriesLabel="test"
paintstyle="shade"/>

to see if Icould force the formatting but this did not work and errors out. There has to be some way to force the formatting to ensure it keeps all of the numbers in the output. I am almost there if anyone has any ideas please let me know.

Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

BKBK,

I think the cfchart really does have a bug. If you use your example and add extra 00 in the data for example


<cfset dummy = QuerySetCell(getData, "varValues", 0.00003, 2)>

then the graph once again can not handle it. It changes this to just a 0 and it no longer works. Some of my querys have values like .000005 so I think the chart has a bug where it just can not handle this. Let me know what you think.

Votes

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
community guidelines
Community Expert ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

I mean this way you set up works really well but only as long as the data is pre-set up.
No, the QueryNew() and QuerySetCell() bits simply simulate a database. When Coldfusion reaches the cfchart tag, it interpretes getData precisely as it would a query of the database. In fact, this suggests that your original query may not return the resultset that you expect.

Test it. What do you get when you do a dump right after the original query code like this?

<cfquery name="getData" datasource=... etc>
...
</cfquery>
<cfdump var="#getData#">






Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

also look here http://64.243.182.238/test.cfm I added the extra 00 on your code and you can see how it just goes to 0 as defauly and no longer works at all.

Votes

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
community guidelines
Community Expert ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

also look here...
I did. I see that the chart in the middle completely answers your original question. It neatly plots and displays the graph you wanted.

I added the extra 00 on your code and you can see how it just goes to 0 as defauly and no longer works at all.
I have answered the question before. If you yourself take out some millimetre graph paper and set out to plot the data, you will be confronted with the same problem. In fact, chances are, your solution will be less accurate that Coldfusion's.

The ratio of 0.032, the highest cost, to 0.000005, the lowest so far, is 6400 to 1. Suppose that you let one millimetre on your graph paper stand for 0.000005, which is just about what the eye can take. Then, for the same graph to contain the data point 0.032, your graph paper must be at least 6.4 metres long. In other words, 7 yards. Not a realistic solution, you'll agree.

There is also the point of practicality. Why split hairs and calculate costs down to $0.000005? Shouldn't you just treat any amount less than $0.005 as $0? I think that's what Coldfusion is hinting at. And rightly so.





Votes

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
community guidelines
New Here ,
Apr 27, 2006 Apr 27, 2006

Copy link to clipboard

Copied

LATEST
Thanks so much for your help. I think I get this now. Sorry for all of the confusion.

Votes

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
community guidelines
Resources
Documentation