6 Replies Latest reply on Dec 15, 2016 1:42 PM by philmodjunk

    Report creation issue

    stephcourdin

      I was hoping to create a single report layout and display a chart for each individual activity type

      ( so the chart needs to have a filter)

       

      the resulting report would have a total of 6 charts

       

      New to report generation in FileMaker (i'm very familiar with SQL and BI tools which allow you to generate reports and modify the sql expression to get the desired result-set to display)

       

      I have an Activity Table (GUID,Type,Date,Measurement) storing many activities measurements occurring at different dates by type

      screenshot(1)Screen Shot 01.png

       

      In a regular table layout i can apply a find filter and get the expected results

      screenshot(2)Screen Shot 02.png

       

      when i try and do the same in a report layout with a chart i do not get the expected results

      -- something with the filter seems to cause an issue

      -- does not seem to apply the filter and end up with gapped rows

      screenshot(3)Screen Shot 03.png

        • 1. Re: Report creation issue
          jbrown

          Hi.

          Do you have the chart source set to "Current Found Set"?

          It seems to me it is, for some reason, pulling the value of the first record for a specific date. 8/7/2015 doesn't have an Activity measurement, so the gap is there.

           

          You can go to a layout with only the chart on it, do your filter-find, and, after sorting it by date, the found results ( screenshot 2) should show up on your chart.

          • 2. Re: Report creation issue
            philmodjunk

            Do both layouts specify the same table occurrence when you open Layout Setup and check the "show records from" drop down?

             

            If the names aren't exactly the same, that could explain the different results from your find. A find only finds records from the context of your layout's table occurrence and only finds records in that table occurrence's data source table. Data from related records is not "found", but instead become accessible based on what records were found in the layout's found set of records as controlled by the relationships that link that data to that table occurrence.

             

            (A "table occurrence" is what we call a "box" in the relationships graph. You can have multiple such boxes that all refer to the same table--the same data source table as it's called.)

            • 3. Re: Report creation issue
              stephcourdin

              phil are you implying that you must have a separate layout for every chart instance ?

               

              this does not seem very practical / scalable

               

              we have 1 table with possibly 12 separate activity type measurements ... do you mean we need to generate 12 separate layouts one for each report ?

               

              on a separate table "surveys" which contains 20 questions .. we were able to create a single layout with a seperate chart for each question

              • 4. Re: Report creation issue
                stephcourdin

                jeremy the main layout is set to "profile table"

                 

                the charts were being generated using the "related records" setting in chat data

                 

                this worked perfectly for the "Survey Report" but does not seem to work well for the activities report

                 

                it seems the reporting is limited in flexibility .. that 1 single layout is limited to a single resultset

                • 5. Re: Report creation issue
                  jbrown

                  If the chart is on a layout with the table occurence (TO) that you want (see the response below), and you have a found set of records, the chart will respect the found set.

                  If you have the chart on a different TO, and you are using the 'related records', then you have to make sure that your relationship produces only the results you want. It makes me think that your relationship is allowing those non-data-filled points to show through. Check out your relationship and make sure it only shows you what should appear on the chart.

                  If you've FileMaker Pro Advanced, use the list function to list all the related data from this TO to the related table and see what fields are coming through

                   

                  To sort of answer your limited flexibility question and the scalability question, you can, in fact, have one chart show different sets of data. It all depends on your found set or the related records. But most people, I think, use ExecuteSQL to generate the x- and y-axis data. So much easier because it can be dynamic. I did a blog post on it awhile back: Dynamic Charting in FileMaker - Soliant Consulting

                  I'm not sure that I've ever built a chart to show 'found set' or current record' or related data. Instead, I use ExecuteSQL to generate the list of data points I need.

                  Explore ExecuteSQL if you've not had a chance to do that; it is a function that can pull data from anywhere in your table and plop it on a chart. Here's a great blog post on it and a paper written by Beverly here in the community.

                  • 6. Re: Report creation issue
                    philmodjunk
                    phil are you implying that you must have a separate layout for every chart instance ?

                    No, I am saying the found set of records that you get on one layout may not be the found set you find on another. It depends on whether the layouts are based on the same or different table occurrences. As others have pointed out, a found set produced by performing a find won't affect what records you are accessing in a related table except by controlling what parent record or records are present to link to those related records.