2 Replies Latest reply on Feb 4, 2016 12:01 PM by jasheldo

    A line chart


      I'm trying to generate a graph in FMP 14 and am struggling with it.  Here's the set up:


      I have 9200 patient records.  Each record has lots of information but all we care about is the insurance product they have (HMO or PPO), the month and year of their service (this is a text field data element I created: Month ( Admission Date ) & "/" & Year ( Admission Date ) ) and the patients service category (there's a total of five options, each patient has been given one.)


      Below is an example of the graph I'm trying to reproduce in FMP.  It uses the exact same data (exported from FMP and was a 45 second pivot chart I whipped together).  What I'd like to have FMP produce is two graphs that look identical to to the one below.  One is for HMO product.  The other is for PPO product.  The x-axis is the month & year of service.  The y-axis is the count of patients by each of the categories.


      I'm finding this to be obtusely difficult in FMP.  I've created sub-summary reports with scripts that sort records by product, then category, then date, then service category.  There's a sub-summary section for each of these sort criteria.  When I put field elements into the report, each sub-summary totals like I would expect but the graphs are useless garbage.  I've looked as much as I can and am now resorting to asking for help because I just don't have time to mess around trying to figure this out any more.


      Lastly, I'm using FMP because even though this can be done very simply and quickly in Excel I


      (a) want to understand how to do it in FMP and

      (b) want to show others how awesome FMP is and they should be glad they got the licenses.


      Thank you in advance for your help.



        • 1. Re: A line chart


          Do you know ExecuteSQL at all? I'm finding the more and more I use charts, the faster it is setting up as ExecuteSQL. There are some gotcha's on this, however, as well.


          Each of your types in your graph is a different data series, so these need to be set up in the chart as a data series. You can point to a field or a calculation. I guess I'd point my series to TOs of the data, where each table occurrence only sees that type of data. So a relationship from your parent TO to the data TO. (Does that make sense?)

          Since you're getting the data from a found set, make sure the source is set to be "Found Set" and then make sure the sorting is done by year / month number (so you have the months in the correct order).


          That's a start. I'll see if I can be more clear with some digging into it.


          Again, though I use ExecuteSQL in all my charting needs.

          • 2. Re: A line chart

            Hi Jeremy.


            Thank you very much for the insight.  It didn't occur to me to create TO's but after reading your explanation that makes sense.  One way I tried to make this work was to create five y-axis series, each a GetSummary based on one of the five Service Categories.


            Unfortunately (for me) I don't know any SQL and that's been by design.  I just don't want to learn SQL right now.  Almost all my of reasoning to use FMP was because it's simple and is an all-in-one solution that makes working with and analyzing my data simple.  However, I've been finding the analysis part to be frustratingly lacking and complicated.  Maybe it's my expectations that are out of line but I want the analysis part to be as robust and as simple and seamless as what Excel provides. To the point where I have to do my data analytics in FMP and then export it to Excel for reporting, I feel I may have picked the wrong relational database platform for my needs.  If that doesn't exist I'll happily suggest this for a future product enhancement.