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.
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.
1 of 1 people found this helpful
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
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.
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.
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.