Announcement

Collapse
No announcement yet.

Question on XLGraph

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Question on XLGraph

    I have a database that contains 3 fields.
    1. Probe Location
    2. Date reading taken
    3. Temperature
    Probe Location can be either "outside" or "inside" (only have 2 probes right now).

    I would like to generate a graph that graphs inside and outside temps on the same graph (different series).

    Can XLGraph do this?

    Thanks...

    #2
    davistw,
    No, it wouldn't work with that database. You could rewrite the query statement and add a variable or two to make it work.

    If you wrote the database as
    1. Date/time
    2. Indoor Temp
    3. Outdoor Temp
    then it would work - as you add probes/sensors you just add another field to the database (use the field name to indicate location)
    jim

    Comment


      #3
      The problem is the database is generated in this format (1-Wire temp) and it alternate's taking readings from probes and even then it may or may not take log temperature readings from all probes depending on if there has been changes. The readings are fairly asyncronous.

      Comment


        #4
        davistw,
        I've been thinking about this and I was wrong (hate when I forget how things I wrote work). You could do it by writing a script to read the data from the database by each location in succession and place the data into an array. You could than pass this array into Xlgraph to be graphed by #including xlgraph and using the ExXlgraph function.

        At first I was thinking that the data would have to be sorted overall by date but played around with excel and as long as it's sorted by date realative to a given series it works. An array formatted like below will graph correctly:
        <pre class="ip-ubbcode-code-pre">
        time series1 series2
        1a 77
        2a 78
        3a 81
        1b 45
        2b 46
        3b 46
        </pre>
        It's late, I'm tired and I hope the above makes sense.
        jim

        Comment


          #5
          I think I can write a query to do this...
          Thanks...

          Comment


            #6
            The chart is choppy...(See attached). Is there a way to tell excel/chart to fill in the points missing? I tried smoothing but that just smoothed the chart points...
            Attached Files

            Comment


              #7
              I think you want to pull the data in multiple passes through the database - once to get all the points for Outside then once for the computer room points. The data points for each series need to be grouped together.

              From looking at the graph it looks like you made one pass through and put each point in its respective array column - so excel interrupts the graph because there are blank spots in the data.
              jim

              Comment


                #8
                That worked except now it chart's the temp for one then the time scale starts over and it charts the other one. The only way can think of to fix this is to add some logic that "fill's in" the missing datapoints. What do you think?
                Attached Files

                Comment


                  #9
                  I think the problem is that the date values are getting interpreted as text rather than as dates (especially since Dtime is listed as the starting value). For this to work excel needs to interpret the X axis as dates.

                  Are you putting in a header row? That might be confusing excel into thinking that first column is text. It might be the formatting of the dates. I had a problem before with Access importing a text file of dates- the dates had to be in 24 hour format rather than AM/PM to work correctly.

                  Something that might help you to see how excel is interpreting things is to look in xlgraph.vbs for the line that contains "appXl.quit" (#136 or so) and comment it out. That will leave excel running after the script is done so you can look at what it's doing.

                  It does work, at least with the manually manipulated data set I made to test.
                  jim

                  Comment


                    #10
                    Jim, I sent you email. That might be a better way to converse for this...

                    Comment


                      #11
                      Xlgraph does make it easy to produce graphs and the .ini interface is a smart way to communicate the large number of graph-specific parameters.

                      I've got a suggestion for additional capability, as well as a bunch of questions.

                      1) It is possible to pass SQL expressions for field names and to the best of my understanding the resultant query is formed correctly, but an Access error is still generated. For example a data field can be scaled in Access with an expression such as "[Data]/1000 AS ScaledData" When looking at the SQL view from Access this expression shows up and when running the query the scaled data appears. When the same SQL statement is executed in xlGraph the error message "no value give for one or more required parameters" is produced at line 176. Even the expression [Data} does not work, while just Data will work. Any ideas?

                      2) # 2 answered above already.

                      3)Sometimes database tables contain more than just data to be graphed. For example Jim Doolittle uses the homeseer log to collect all sorts of data and uses data mining filters to query only that which is of interest. Could an .ini parameter be added that will allow the addition of filters? From the SQL perspective this will just add additional AND conditions to the statement?

                      4) I toyed with trying to remove the small black line border around the created jpg file but did not have any success. Is there a way to accomplish this so the image can be blended with other elements of the web page without the border providing a hard demarcation?

                      5) FYI, there are graph formats, and in particular, the ones with two Y-axis scales, that can be produced interactively in Excel, but were not made visible for an automation interface. I think it added too many additional combinations and Microsoft elected to not take the risk for checkout and support.

                      [This message was edited by Michael McSharry on Friday, 26 July 2002 at 02:22 AM.]

                      Comment


                        #12
                        Jim,

                        Is it possible to have XLgraph do everything that it currently does, but stop before exiting at the end. I'd like the ability to have it do a complete graph, but then do some small tweaks and save an additional copy.

                        So, if I could have it just not exit when done, that would be great. Maybe a parm that is passed in?

                        Thanks
                        Mike

                        Comment


                          #13
                          Mike,
                          I have some control variables in the ini now for debugging purposes. One of these controls whether excel exits or not when it's done. So this should be addressed when I upload the next version. I'll stick the macro code to export charts as jpg's since I don't think you can do that from within excel (at leat in the version that I have).

                          Michael,
                          1) I think I found the problem, although I was getting a different error than you did. I use the field name entry from the ini for the SELECT statement and to pull the information out of the recordset. Using the example above the data in the recordset would be "scaledData" but xlgraph was looking for a field named"[Data]/1000 as ScaledData]. I've added a function to parse for the renamed field name and it seems to be working.

                          3)This is a good idea. I think I'll add three INI variables, one to add a filter to the WHERE clause, one to replace the ORDER clause, and one that would let the user replace the whole query.

                          4) I think I found the setting to remove the border but haven't had a chance to experiment yet.

                          5) There are even some supported that are generically supported in xlgraph. I tried to run through the types I thought likely to be used like XY, line, bar and check the settings work for them (at least w/o crashing) but didn't go through everything available. Some I just don't think are likely to be used.

                          I'll probably post an updated version over the weekend after I have a chance to do 3) and 4)

                          jim

                          Comment


                            #14
                            Thanks Jim. Its an excellent addition to the board and I'm very glad to see you actively support it.

                            Comment


                              #15
                              As long as the .ini file is growing would it be possible to allow the user specify the ending date for the data to be displayed. As I understand it now the most recent data is always displayed with the number of days specified by the span constant. It would be nice to select any given window within the database for display.

                              I imagine that the same could be done by using the full SQL query and that would be fine with me. That implies, then, that the span constant would be ignored when the full query string option is selected.

                              I also suspect that I do not fully undestand how to specify the date ranges in xlgraph.ini. I'm drawing a graph with the intspan=0 yet the graph is showing up with all the data contained in the database.

                              Comment

                              Working...
                              X