6 Replies Latest reply on Dec 9, 2016 9:33 AM by philmodjunk

    Chart Question - No chart data displaying

    fmpdude

      I created a chart that has percent values from 0 to 100. The ExecuteSQL for the y-axis produces values like this (all between zero and 100):

       

      22

      27.92

      36.62

      30.5

      .

      .

      .

       

      ----------

       

      The SQL for the y-axis looks like this: ExecuteSQL("select ROUND(pct,2) from checks where pct is not null order by chk_date asc" ; "";  "")

       

      ---------

       

      My chart setup looks like this:

      And, in the Data Source Section, I have:

       

      I didn't fill in anything for the x-axis since I just want the y-axis data, which works fine if I just use the field and not an SQL for the y-axis data.

       

      Now, although the above SQL works fine in DV, the chart is always empty. I also tried using a paragraph mark for the second parameter. No difference.

       

      I fiddled around with all the Data Source options, but that couldn't find one where the data displayed.

       

      Surprisingly, if I switch axes, I do see the data values crammed on to the x-axis.

       

      ----

      If I uncheck the x-axis and y-axis constraints, then I get a graph like this below. The y-axis is way off given the decimal data between 0 and 100.

       

      Would appreciate any suggestions.

       

      Thanks

        • 1. Re: Chart Question - No chart data displaying
          fmpdude

          The issue seems to be that, for some reason, I must have the x-axis data defined as well.

           

          Once I define the x-axis values with another SQL, then the chart does populate.

           

          The problem now is that there are so many dates (the chart is just supposed to show a long trend) the x-axis labels are just a nasty looking line across the bottom of the chart.

           

          Don't see how to fix that.

           

          I just want the y-axis data displayed, again, which works fine if I create a separate layout and just use the field name (no x-axis data required).

           

          Not sure what the solution here might be.

           

          Thanks

          • 2. Re: Chart Question - No chart data displaying
            jbrown

            Hi.

            I'm a bit surprised about not using the x-axis as labels. I didn't know one could not fill it out and have it work. Interesting.

            But with SQL, it seems you have to give the chart something to put the data from the SQL statement into. Otherwise it won't know which number goes where.

            I put together a small file that shows percents and gathers those in the ExecuteSQL statement.  Without the x-axis, my chart is blank. With the x-axis filled in with something (I'm using a small list :  List (1;2;3;4;5;6; . . . )) the data can't show up.

             

            Charts, it seems, needs the intersection of the x and y axis to know where to place data. I suppose FileMaker chart auto fills in field names or something if you use a field. Here you aren't.

             

            That, I think, makes sense; what do the percents mean across time? Are they spread out by day, month, year?

             

            Try to add something in the x axis and see if that shows your data. You can always, I suppose, hide the labels, but I don't see why x-axis labels should be hidden.

            • 3. Re: Chart Question - No chart data displaying
              jbrown

              Hey. you figured it out. the x-axis part.


              For the labels, you can change the angle at which they display. It is in the chart tab / x-axis options.

              You might want to play with the color of the labels; if you don't want them to display, maybe make them the same color as your chart background.

              You can also change the way a date shows up on the x-axis. Instead of 2016-12-09, you can change the formatting to be 12/9 or something smaller. IN the same place as the x axis options, you can change the format to "Date" and then mess with the options there.

              1 of 1 people found this helpful
              • 4. Re: Chart Question - No chart data displaying
                philmodjunk

                Another trick, easier if not having to use delimited data, is to replace all but every nth date with a space or empty string. There are custom functions that can take your SQL result and do that.

                1 of 1 people found this helpful
                • 5. Re: Chart Question - No chart data displaying
                  fmpdude

                  Thanks for your reply.

                   

                  Well, the angle idea doesn't work since there are so many dates.

                   

                  But yes, to get just the y-axis to display all the same data -- without SQL -- do your finds and sorts ahead of time, and don't specify any x-axis data, then the graph displays as expected.


                  But, with the SQL, I ended up using a silly little hack. I dragged down a horizontal shape and covered up the part of the x-axis with all the overlapping dates, and adjusted the color to a light blue. Then I just did created a stroke around the whole graph with the same color. Then, I just GROUPED all the graphical items. Looks nice, actually.

                   

                  Why the SQL doesn't work the same way, I'm not sure. Some implementation inconsistency, I'm guessing. Maybe I should report it?

                   

                  Anyway, thanks for posting and I wanted to also share my hack solution.


                  Thanks,

                  • 6. Re: Chart Question - No chart data displaying
                    philmodjunk

                    If you don't want to show any dates see if this string works. :

                     

                    Substitute ( 10^Number of data points here - 1 ; 9 ; " ¶" )

                     

                    That gives you the right number of values to match your Y series, but with nothing that's visible.