Announcement

Collapse
No announcement yet.

Love XLGraph!!

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

    #31
    Simon,
    You're right - it was the SQL statement. Good troubleshooting. I experimented and googled and it appears that access is biased towards US format when evaluating queries. If access can interpret the date as US then it will. In the process of hunting I found a code snippet that converts the date literal to 'international' format of YYYY-MM-DD. This is supposed to work without regard to local settings. It appears to, even with the short format set to dd-MMM-yy (12-Oct-02).

    The code line is:
    sdate = year(sdate) & "-" & left("00",2-len(month(sdate))) & month(sdate) & "-" & left("00",2-len(day(sdate))) & day(sdate)

    I used the above when I build the queries in Xlgraph and hopefully that takes care of the date issue. - note that I still don't modify strUserQuery (I think that's what Michael is using in the temperature script to interface to Xlgraph).

    I also needed to change the time format with local date set to D/M/YY to avoid problems here with the data being interpreted correctly when pasted into excel. I added a routine that will convert the AM/PM to 24-hour clock to work around that.

    I've put up a new version of Xlgraph here

    If this doesn't seem to have fixed the issue let me know.
    jim

    Comment


      #32
      Rupp,
      Here is one of my charts
      jim
      Attached Files

      Comment


        #33
        Thanks Jim

        -Rupp
        ...One Nation Under GOD, Indivisible, With Liberty And Justice For All.
        💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

        Comment


          #34
          Rupp,
          You asked for examples. Looks like it's going to be a cold night in UK.
          PeteG
          Attached Files

          Comment


            #35
            I found a small nit in v1.5 - one of the entries is misspelled in the INI file. To correct change "bVisibleExcel" to "bExcelVisible".
            jim

            Comment


              #36
              Jim,
              I saw you incorporated the Count function in 1.5, but unfortunately it applies to recordsets and not to SQL expressions. For my Temperature ASP I group records and count needs to be applied on the group result rather than on the count of the elements that are included in the group. I do not think there is anyway to do what you are trying for the general case, and especially if you are trying to stay compatible with Access97. I went back to the record counting you used on 1.4 to get the correct counts.

              Comment


                #37
                Michael,
                Live and learn - I knew there was a reason I only commented out the old code rather than deleting it.

                A corrected version has been uploaded to the library.
                jim

                Comment


                  #38
                  Jim,
                  On your to-do list you may want to protect yourself in the ArrayFill procedure for the case where the SQL expression returns no data. The moveFirst in this case will fail.

                  I've been playing with xlgraph with very sparse sample sets and find I do not understand how the x-axis scaling is working. As an example I use
                  intSpan=2, intDataInDay=24, intXaxisMajorUnit=0.25 to chart 3 days of data. When I have a relatively complete set of data spanning the dates of interest then things look nice. When I have only 3 or 4 data points somewhere in the middle of the period then I will typically get a chart with 3 x-axis labels spanning a single day. Is there a simple explanation for how this works?

                  Comment


                    #39
                    Michael,
                    Currently Xlgraph doesn't do anything with the x-axis scaling (other than a little tweaking on the mimimum to make it an even multiple of the major unit). Excel is auto-scaling the x-axis based on the data sent to it. I"ll add a switch to have xlgraph set the lower and upper limits on x-axis scaling so that the x-axis matches the time period being searched regardless of what data is returned.
                    jim

                    Comment


                      #40
                      Jim, another question about the chart. The legend contains a colored line segment followed by the label. Is there a way to change the line segment to a circle or box? The colors are very hard to distinguish with the fine line. I do not know if there is a parameter for setting the background of the legend, but the color resolution may be better if it is the same background as the chart background where the data lines are drawn. In my case the legend is white and the chart is light blue and it is easy to distinguish the lines on the chart while it is not on the legend.

                      Another note on potential failure modes. The new Convert to 24Hr format could have a non-date data item as input (empty data set) and it will fail. In my case I made a isDate check before doing the conversions.

                      Comment


                        #41
                        Michael,
                        I looked into it a bit and the legend symbols can be changed, unfortunately they're linked to the chart lines. Whatever you do to the legend line style also happens to the chart line style. You can change the line style to have markers but that tends to make the chart look bad when you have many data points. We could play with line weights but I think the chart lines get a little thick looking (the next weight appears to be about twice as thick (just eyeballing it).

                        The legend background color can be changed and I'll add that to the next version (I keep thinking of doing that one anyway - it's the about only default color item left)
                        jim

                        Comment


                          #42
                          I supplied a SQL string with data covering a 4 hour range, otherwise all ini parameters are the same as was used for 24 hours of data. The chart generated a graph for one day of data. I did not pay much attention to the correctness of the data since the x axis was not as desired. The WHERE portion of the SQL qualification has a value of the nature #2002-11-05 4:00# for a date value. Is there some restriction that prevents the display of data for periods of minutes or hours?

                          Comment


                            #43
                            There shouldn't be anything that restricts it. I did a few here and cut it back to 4 hours chunks of the last. They look bad (probably cause the x-axis unit is set relatively large) but they work. How are you asking for the data? with strUserQuery or strUserQueryFilter? There is a problem with the code around appending strUserQueryFilter- the parentheses are added in the wrong spots so the query doesn't quite end up as it should.
                            jim

                            Comment


                              #44
                              When I went back to look at the actual query string I realize that I rounded the date in the calculation so I actually had 28 hours of data. Good to know that is should work. I was using the full query and not the filter.

                              Comment


                                #45
                                I uploaded a new version to the library: Xlgraph v1.6

                                Changes include:
                                v1.6
                                --added strUserXaxisMin, strUserXaxisMax - user can set values for X-axis scale "2002-11-16 8:00 pm"
                                --added bSetXaxisMinMaxScale - Xlgraph will force x-axis min max values based on intSpan
                                --added intChartTitleBorderLineStyle, intChartTitleColor
                                --added intLegendFillColor, intLegendBorderLineStyle
                                --added lngYaxisTitleInteriorColor, intYaxisTitleBorderLinestyle
                                --added lngXaxisTitleInteriorColor, intXaxisTitleBorderLinestyle
                                --added bSetXaxisMinMaxScale - forces settings for limits on x-axis regardless of data returned
                                --added code that would actually read bDebugVerbose settings from ini
                                --corrected a problem with adding the strUserQueryFilter to the query
                                (parantheses in the wrong place)
                                --modified code to make independent of Excel worksheet name
                                --add IsDate check to functions ConvertAMPMto24 and InternationalDate in case the value isn't a date
                                --changed way that font for Chart Title will appear- due to the way the code was arranged the actual font size used for the Chart title was not the value in the ini file. In the code the chart title font size was being set, then the chart size scaled, at which point Excel would rescale font size ( 9->12,12->17.5). The chart title will now be the font size specified in the ini file.

                                jim

                                Comment

                                Working...
                                X