1 2 Previous Next 15 Replies Latest reply on Feb 17, 2011 12:44 PM by Sorbsbuster

    Simple report showing summaries does not work

    FionaGent

      Title

      Simple report showing summaries does not work

      Post

      I am evaulating Filemaker pro and cannot get the following simpe report to work:

      I have 2 tables: Table 1 Assets - fields asset code and market

                              Table2 Valuations - fields asset code, date and value (ie multiple valuations for same asset code for different dates)

                              Table 1 and 2 are related by asset code

      I would like to produce a report showing the asset code, latest value for the asset and market. To be sorted by market showing subtotal by market.

      If I do a layout based on Table 2 - i correctly get subtotals but all values are shown (ie for previous dates)

      If I do a layout based on Table 1 - i get only the latest value of the asset but the subtotal (in the subsummary part) shows only the value of the last record reported ie not all total of the values of the all the assets in that market.

      I would also like to produce this report as at a date in the past, showing the most recent values related to the report date bt cannot even get the basic report right after many hours of struggling.

      Pease help - this would be a basic SQL join between the two tables in any relational database reporting tool.

      Fiona

        • 1. Re: Simple report showing summaries does not work
          Sorbsbuster

          "If I do a layout based on Table 2 - I correctly get subtotals but all  values are shown (ie for previous dates)" - if you simply didn't see the detailed listings of the dates and their individual values, would that be what you want?

          If so, all you have to do is keep the data you have in the sub-summary section, that you say displays correctly, and remove the fields from the body section, and remove the body section.

          ---

          "I would also like to produce this report as at a date in the past,  showing the most recent values related to the report date"

          If you search for all Table 2 records up to the date you are interested in and sort and display the report as above, will that not give you what you want?

          • 2. Re: Simple report showing summaries does not work
            FionaGent

            Thanks but these wold not work.

            I require only a single line per asset code in table 1, showing only a single value for the most recent date (from table 2), and all these should be subtotalled per the market field in table 1.

            • 3. Re: Simple report showing summaries does not work
              Sorbsbuster

              "If I do a layout based on Table 1 - I get only the latest value of the  asset but the subtotal (in the subsummary part) shows only the value of  the last record reported ie not all total of the values of the all the  assets in that market."

              If it is only this bit that is wrong: "[it] shows only the value of  the last record reported" then you seem to be placing a regular field in the sub-summary section.  That is what will display if you do that.  If you create a Summary Field in Table 1 that is the total of the field you want to summarise, and put that in the sub-summary section, it will show the total.

              • 4. Re: Simple report showing summaries does not work
                philmodjunk

                Here is a tutorial on summary reports with sub summary parts that you may find useful to look at: 

                Creating Filemaker Pro summary reports--Tutorial

                • 5. Re: Simple report showing summaries does not work
                  FionaGent
                  The field in the sub-summary section is a summary field. It only produces the correct total when running the report off table 2, but I then include all the records from table 2 in the report which is incorrect. The tutorial assumes that the report should include all the records in table 2, not as in my requirements, only the records showing the latest value of each asset. If I could find an algorithm to pick the records from table 2 which were the latest valuations (as per a input date ie the max date le an input variable). Perhaps I could produce the report but I have been unable to get the algorithm correct and also need to understand how to include a variable (a date) in the report selection process
                  • 6. Re: Simple report showing summaries does not work
                    Sorbsbuster

                    Sorry - I get confused about which part works correctly for you in each of the scenarios. ("It only produces the correct total when running the report off table 2,  but I then include all the records from table 2 in the report which is  incorrect")

                    You have a Header Table with Asset Code and Market fields in it, related to a Detail table with Asset Code and Value in it, with a date of the valuation of that asset code?

                    If you sort the relationship connected via Asset code by Date Descending, then you can create two calculation fields in the Header Table:

                    DateLatest = Table2::Date

                    ValueLatest = Table2::Value

                    and a 3rd field, a summary field:

                    SumValueLatest = Summary of ValueLatest

                    Find in Table 1 those Assets whose DateLatest is in the date range you want, put their details in the body part, sort by market, etc, and put the summary fields in the sub-summary section.  You will only get the sum of the latest values.

                    • 7. Re: Simple report showing summaries does not work
                      FionaGent

                      Thanks - tis may solve the problem of producing a report showing the most recent value for each aseet. If i would like to run a report showing the values as at a date in the past - for example last month or last year how would I achieve this?

                      • 8. Re: Simple report showing summaries does not work
                        philmodjunk

                        Actually, the summary fields compute values based on all records in the current found set or a sub group as controlled by sorting and the use of a Sub Summary Part. That "found set" could be all the records in the table or just those you've pulled up by performing a find or other manipulation of the found set of records.

                        That's the key for this last question. If you can perform a find that specifies a date range, your report will then be based on those records instead of on all the records.

                        • 9. Re: Simple report showing summaries does not work
                          FionaGent

                          I have looked at the find in the layout - can i exclude records based on the comparison of 2 fields in the layout - eg 2 dates?

                          • 10. Re: Simple report showing summaries does not work
                            philmodjunk

                            It's possible. Give an example of exactly what you want and I can show you how to do it manually and/or in a script.

                            • 11. Re: Simple report showing summaries does not work
                              Sorbsbuster

                              Try this:

                              I would modify the relationship between the tables.  Create in the Parent Table1 a global field gDateInHistory.  Set the relationship that was used above to include the requirement AND Table1::gDateinHistory >= Table2::Date.  Leave it sorted the same way.

                              Set the gDateInHistory on the Table1 layout, pick a date and I think it's worth a try.

                              • 12. Re: Simple report showing summaries does not work
                                FionaGent

                                I have created the gDateinHistory field and modified relationship. Looks promising. How do I assign the gDateinHstory field the same date value without updating the field manually for every record in Table1. I have not used global fields - do these have a different definition?

                                Thanks

                                • 13. Re: Simple report showing summaries does not work
                                  Sorbsbuster

                                  Yes - when you create the field go to options -> storage tab -> select use global value.

                                  • 14. Re: Simple report showing summaries does not work
                                    FionaGent

                                    Thanks then I assume I create a layout containing only the global field?

                                    1 2 Previous Next