18 Replies Latest reply on Apr 27, 2006 1:21 PM by mikey999

    adding numeric decimals

    mikey999 Level 1
      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?


        • 1. Re: adding numeric decimals
          BKBK Adobe Community Professional & MVP
          Might be an idea to change the column's datatype to "float".

          • 2. Re: adding numeric decimals
            mikey999 Level 1
            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?
            • 3. Re: adding numeric decimals
              BKBK Adobe Community Professional & MVP
              > 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.

              • 4. Re: adding numeric decimals
                mikey999 Level 1
                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?
                • 5. Re: adding numeric decimals
                  BKBK Adobe Community Professional & MVP
                  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>

                  • 6. Re: adding numeric decimals
                    mikey999 Level 1
                    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>
                    • 7. Re: adding numeric decimals
                      BKBK Adobe Community Professional & MVP
                      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>
                      • 8. Re: adding numeric decimals
                        mikey999 Level 1
                        ok i changed it out now its now showing any values at all?

                        http://64.243.182.238/test.cfm
                        • 9. Re: adding numeric decimals
                          BKBK Adobe Community Professional & MVP
                          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?


                          • 10. Re: adding numeric decimals
                            BKBK Adobe Community Professional & MVP
                            ... something like this

                            • 11. Re: adding numeric decimals
                              mikey999 Level 1
                              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?
                              • 12. adding numeric decimals
                                mikey999 Level 1
                                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.
                                • 13. Re: adding numeric decimals
                                  mikey999 Level 1
                                  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.

                                  • 14. adding numeric decimals
                                    BKBK Adobe Community Professional & MVP
                                    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#">






                                    • 15. Re: adding numeric decimals
                                      mikey999 Level 1
                                      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.
                                      • 16. Re: adding numeric decimals
                                        mikey999 Level 1
                                        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.
                                        • 17. adding numeric decimals
                                          BKBK Adobe Community Professional & MVP
                                          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.





                                          • 18. Re: adding numeric decimals
                                            mikey999 Level 1
                                            Thanks so much for your help. I think I get this now. Sorry for all of the confusion.