9 Replies Latest reply on Sep 5, 2012 8:30 PM by philmodjunk

    One report, two spreadsheet tables, 2 charts.



      One report, two spreadsheet tables, 2 charts.


      This seems like this would be simple. I have a Database: Product Name.    Qty.       Price.       Total I want to create a report that shows in table form all products, quanties, etc and summarizes their total" This is not hard and I've done this. Now I want a second spreadsheet/table that uses the total I gathered from the first spreadsheet. Minimum Budget = (Grand Total from 1st spreadsheet / 500) * Percentage {this is a number variable} Average Budget = (Grand Total from 1st spreadsheet / 1000) * Percentage {this is a number variable} Maximum Budget = Grand Total from 1st spreadsheet / 1500) * Percentage {this is a number variable} Then I want two charts on the report, one that use data from the first spreadsheet, another that uses data from the second spreadsheet.

        • 1. Re: One report, two spreadsheet tables, 2 charts.

          I don't see a "second spreadsheet" in your description only a list of records (spreadsheet number 1) and 3 calculated values that compute values from a total from the records at the beginning of the report. Did I miss something here?

          If I didn't miss something there, the basic report should be fairly easy to set up as you can use a list view layout with a grailing grand summary part and a summary field to show and compute the grand total.

          3 different calculation fields can then be defined and placed on the trailing grand summary to show the three computed values.

          As to the charts you want, you don't describe them in enough detail to suggest how to set them up, but I see no reason why they can't be setup as you've specified (But with a more detailed description of them, I might indicate otherwise.)

          • 2. Re: One report, two spreadsheet tables, 2 charts.

            Basically I want a report that looks like this:


            Product Name           Quantity             Price                  Total

            String                            3                 $1.00                  $3.00

            Kettle                            1                $10.00                $10.00

            Volleyball                       5                  $5.00                $25.00


                                                                      Total                $38.00


            Mininum Budget:

            Total           Mininum Budget Allowance       Percentage         Budget      

            $38.00                    $500                                 5%           Calculation

            $38.00                    $1000                                5%          Calculation

            $38.00                    $1500                                5%          Calculation


            Chart 1 = Product Name to Total                           Chart 2: Total to Budget

            • 3. Re: One report, two spreadsheet tables, 2 charts.

              I'm going to use the word "tables" in lieu of the word spreadsheet because they make more sense here.  Just know that I don't mean tables as in separate databases, i.e., filemaker terminology.


              It's crucial I show both "tables" as I've demonstrated above.

              It's equally important I get this information on one page.

              • 4. Re: One report, two spreadsheet tables, 2 charts.

                Are you sure that you have the headings in your example posted correctly?

                You show a column labeled "minimum budget allowance". Shouldn't that only refer to the first row of data in this part of your report? And then the next line is the "average" with the last line the "maximum" allowances?

                If so, that matches my understanding from your original post.

                Everything above the line can be from either a body or sub summary layout part. If each row represents one record use the Body. If it is a group of records where all the records have the same product name (or product ID), then use a sub summary layout part 'when sorted by Product Name' to group your data into these sub summary parts.

                The dotted line and all parts below would be placed on a trailing grand summary layout part and your calculations would refer to the summary field that computes the grand total in their calculation expressions in order to compute these amounts.

                Would your first chart be "Pie" charts that show each product and what percentage of the total they represent?

                And what would your second chart look like? 4 bars in a bar chart?--one for the total, and three for the three budget values?

                If my guesses are correct then this is all doable.

                • 5. Re: One report, two spreadsheet tables, 2 charts.

                  You're right; you're doing great at reading my mind.  :)

                  The second spreadsheet / table  should include 3 rows that reflect: Minimum Budget Allowance, Average Budget Allowance, and Maximum Budget Allowance.


                  Those amounts ($500, $1000, and $5000) will vary as will the percentage.


                  Since it's only 3 to 4 calculation rows, you're right I can control that in the summary field where the trailing grand summary lives.


                  They both are pie charts.

                  One is product to total the other is total to budget.

                  • 6. Re: One report, two spreadsheet tables, 2 charts.

                    Those amounts ($500, $1000, and $5000) will vary as will the percentage.

                    And where/how will you record these amounts and the percentages? Under what circumstances will they change?

                    This looks like data that can be recorded in a related table, but I lack the needed details to tell you exactly how they would be stored in a related table that links to this specific set of records. I'd guess, again, that you have a monthly, quarterly, or other time period based budget where the records at the top of the report all fall in this time period and then we can link one related record for the same time period where we store these 6 values for use in these calculations.

                    other is total to budget

                    The total for each product as fractions of a total budget? And to which budget amount? Minimum, average or maximum?

                    • 7. Re: One report, two spreadsheet tables, 2 charts.

                      It's hard to explain but this isn't something we would have on-going for monthly, quarterly, etc.

                      The report I have is extremely complicated and it would take a long time just to explain the purpose of the report; I tried to boil it down to its most basic of components.

                      Bottom line, the total from spreadsheet 1 is parsed to the related table where there are 3 calculations that use "total".

                      From spreadsheet 1, there is a chart products to total.

                      From spreadsheet 2, there is a chart total to budget.


                      So yes, there is a related table and I was able to parse the total from one filemaker table to another.  Further, I was able to create a report that showed the table I wanted ---- the trouble was, I couldn't get the two tables to appear "together" on the same page with the two charts.

                      This is what I'm driving at.

                      • 8. Re: One report, two spreadsheet tables, 2 charts.

                        The amounts $500, $1000, and $1500 could be hard-coded in or they could be variables that sit in layout mode hidden.  Same with the percentage. 

                        I just need the ability to change them when necessary.



                        Focus more on the total that is parsed from table 1, then calculated using the min / avg / max and this produces a result X.  To create the chart, I need  to compare total from spreadsheet 1, with X.

                        That's all.

                        • 9. Re: One report, two spreadsheet tables, 2 charts.

                          I don't see anything that you would use to "parse" totals from one table to another here. The upper part of the report is something that you should be able to generate directly from the table where this data is stored. Each row in that portion of the report can represent a single record or a group of records. You can control which records in this table are used for the report by performing a find to create a found set based just on the records you need for your report.

                          Here is one extremely simple way to manage the values used in the lower part of the report, but odds are very good that I would not use this method in an actual database solution, but without knowing more about your database, I can't make a different suggestion:

                          Define 6 global fields: gMinConst, gAveConst, gMaxConst, gMinPct, gAvePct and gMaxPct

                          Then, if you have not already, define sGrandTotal as a summary field computing the total of the data displayed in your Total Column in the upper portion of your report.

                          You can then define three calculation fields as:

                          Minimum Budget : sGrandTotal / gMinConst * gMinPct
                          Average Budget : sGrandTotal / gAveConst * gAvePct
                          Maximum Budget : sGrandTotal / gAveConst * gAvePct