1 2 3 Previous Next 40 Replies Latest reply on Jul 27, 2012 2:07 PM by philmodjunk

    Sub Summary reports and Instant Web Publishing

    swillette

      Title

      Sub Summary reports and Instant Web Publishing

      Post

      I have a copy table with a sub summary report that shows me my projects grouped by a field called ProjectID . I have my sub summary part on the bottom that shows me the numbers I need and a total summary that gives me the grand totals I need.

      All of the data is copied via a script from 4 other tables (some are SQL tables). So when my script executes, all the records in this copy table are deleted and new records are copied in.

      Not the most efficient way getting this report, but it is the only way I can get the numbers after trying multiple other ways. (Why can’t a portal show a found set of a layout!!!)

      I need to have my customer’s employees view this in a browser with Instant Web Publishing. And once again I’m stumped because IWP doesn’t support sub summary parts.

      I found this post but I’m not sure if it will work for me. Subsummary Reports in IWP

      My problem is I have multiple ProjectID (s) with different fields from the 4 copied tables. So after my script copies records from the first table, the script proceeds to copy data from the second table that has different fields than the first. When the script is all done, I then use summary fields to help group the ProjectID (s) together to get my sub summary report. (Example there can be 20 ProjectID (s) of 107-125:61 that are summed up as one)

      Is there any way I can show this data in IWP? 

        • 1. Re: Sub Summary reports and Instant Web Publishing
          philmodjunk

          It should be possible, but there are aspects of your post that are troubling:

          All of the data is copied via a script from 4 other tables (some are SQL tables). So when my script executes, all the records in this copy table are deleted and new records are copied in.

          This is not typically needed for a summary report with sub summary layout parts. You'll need to describe the structure of your data and why this "copying" is necessary.

          TSGal's suggestion in the link that you found is one possibility. A table where you have only one record for each Project and a relationship that links to the records that need to be totaled up for the sub total you currently compute in a sub summary layout part is another possibility. But again, I'd need to know the design of the tables and relationships from which you need to generate this report before I can describe it in more detail.

          Also, do you have FileMaker 12? There's a new function that may be especially helpful here in pulling together data from multiple tables and then computing the needed sub-totals.

          • 2. Re: Sub Summary reports and Instant Web Publishing
            swillette

            I am using FM 12 advanced. Here is my original post about my first attempt at getting the data I need displayed from this database.

            Showing the results of a found set from four related tables.

            I don't want to copy the records to another table, but this seems like the only way to do it. 

            The orginal database is in Access 2005. It has multiple queries that find certain data. Then the queries are gather together into other queries ending in a final query with the correct number.

            I have a script that has a found set for each of the tables. Then I copy the found set to one table were I am combining the ProjectID (s) together to show the total per ProjectID. I am using a sub summary layout object to do this. 

            I need the final numbers like the attached image. The Project Phase is the ProjectID.

            I tried using a portal at first, but the portal will not show the found records of the 4 tables.

            • 3. Re: Sub Summary reports and Instant Web Publishing
              philmodjunk

              I think that you should have continued your other thread...

              It's rather difficult to match up what is shown in the above screen shot to the table occurrences shown in the other thread. The single row of data represents what in relation to the tables and relationships shown in the other thread?

              Nor have you detailed how you have pulled all this data together into a report table.

              It would also be helpful to know what sub summary based sub totals you need to reproduce in IWP.

              • 4. Re: Sub Summary reports and Instant Web Publishing
                swillette

                So to go back to my first way of trying to get the data, I've attached the table diagram. The numbers you see in the previous attached Access DB image above are coming from the 4 related tables. Here is a list of the fields and where they are coming from:

                Project Code, Project Name, Project Manager – Project

                Go to Project and the date range fields are global fields in tblRevenue (this is an FM table, not SQL)

                Phase (is ProjectID) - Project 3 TO

                ProjectName - Project 3 TO

                Con Type - Project 3 TO

                Billable Hrs - TimeEntry

                Labor Cost - TimeEntry

                Expense Cost - ExpenseLog

                Hrs Billed - tblRevenueAdjustments 2 TO

                Labor Inv - TransactionTable 2 TO

                Expense Inv - TransactionTable 2 TO

                Inv Total - TransactionTable 2 TO

                Reserverd - tblRevenueAdjustments 2 TO

                Unbilled - tblRevenueAdjustments 2 TO

                Accrued Rev - calc field

                Multi - calc field

                The Project 3 TO is a found set of all the Active Projects. When I perform the find for the 4 other  tables, I am searching for the date range, and Active projects.

                The numbers I need to get are the summary of all “Active” records for the ProjectID in each of the tables based on ProjectCode.

                • 5. Re: Sub Summary reports and Instant Web Publishing
                  philmodjunk

                  That helps but I still have questions.

                  Billable Hrs - TimeEntry

                  Labor Cost - TimeEntry

                  Expense Cost - ExpenseLog

                  Hrs Billed - tblRevenueAdjustments 2 TO

                  Labor Inv - TransactionTable 2 TO

                  Expense Inv - TransactionTable 2 TO

                  Inv Total - TransactionTable 2 TO

                  Reserverd - tblRevenueAdjustments 2 TO

                  Unbilled - tblRevenueAdjustments 2 TO

                  You have a one to many relationship shown. So do each of these values represent a Total? (ie. Billable Hrs is the total of a field in TimeEntry?)

                  The numbers I need to get are the summary of all “Active” records for the ProjectID in each of the tables based on ProjectCode

                  ProjectCode? Your screen shot shows relationships that match on a field named ProjectID. Did you mean ProjectID?

                  By "Active" is this a status field in the Project table? Or is there such a status field in the child tables that must be used to filter out entries that would otherwise match to a record in the Project Table?

                  Should your report have the basic structure of one row for each Project with grand totals at the bottom?

                  Your answers may change this, but this looks like a report that can be set up on a layout based on Project 3 with calculation fields that pull totals from each of the related tables plus summary fields that then compute the grand totals.

                  • 6. Re: Sub Summary reports and Instant Web Publishing
                    swillette

                    >You have a one to many relationship shown. So do each of these values represent a Total? (ie. Billable Hrs is the total of a field in TimeEntry?)

                     

                    I'm sorry. That is a list of the fields and what tables they need to represent on the Financial Information Summary tab on the Access DB. 

                    The relationship is what is on the diagram. The 4 SQL tables are related by ProjectID to the Project 3 TO. Project 3 TO is related to Project by ProjectCode.

                     

                    >ProjectCode? Your screen shot shows relationships that match on a field named ProjectID. Did you mean ProjectID?

                     

                    The 4 SQL child tables are related by ProjectID to the Project 3 TO. Project 3 TO is related to Project by ProjectCode.

                     

                    >By "Active" is this a status field in the Project table? Or is there such a status field in the child tables that must be used to filter out entries that would otherwise match to a record in the Project Table?


                    There is status field in the Project table, but not in the 4 sql child tables.


                    >Should your report have the basic structure of one row for each Project with grand totals at the bottom?

                     

                    Yes, my report needs one row for each Project (all the same ProjectID (s) are totaled) with the grand total at the bottom.

                    >Your answers may change this, but this looks like a report that can be set up on a layout based on Project 3 with calculation fields that pull totals from each of the related tables plus summary fields that then compute the grand totals.

                     

                    I agree and I tried this first with portal, then just in a layout. I tried to put summary and calculation fields in the 4 child sql tables. 

                    I will attach a screen shot of my attempt with related fields from the child tables on  Project 3 TO layout. If there is only one ProjectID the numbers are correct. 

                    See the image I attached. On this layout, the Accrued Rev and Mult fields are not complete. (just ignore)

                    • 7. Re: Sub Summary reports and Instant Web Publishing
                      philmodjunk

                      I still don't have a clear answer to this question:

                      So do each of these values represent a Total? (ie. Billable Hrs is the total of a field in TimeEntry?)

                      What I have in mind does not use any portals, but does need a clear answer to the above question. Since you have many records in TimeEntry for one record in Projects, it would seem logical that the single row of data shows the fields I listed earlier as Totals computed from a set of related records in one of the other tables, such as Billable Hrs could be total computed from any number of TimeEntry records for the same project.

                      If this is the case, then let's use Billable Hrs as the example. I can see a field in TimeEntry named TEHours. This may be the wrong field, but I'll use it for this example and then you can just substitue the correct field from TimeEntry.

                      If you define a calculation field named BillableHrs in Projects as: Sum ( TimeEntry::TEHours ), and select Project 3 from the "context" menu at the top of the specify calculation dialog, then you can place BillableHrs on your Projects 3 based layout and you'll see a total from TimeEntry for each project record in your found set.

                      For a grand total of all BilllableHrs, you'd then define a summary field i Projects that computes the total fo BillableHrs.

                      • 8. Re: Sub Summary reports and Instant Web Publishing
                        swillette

                        Yes, B-Hrs is the total of TEHours in TimeEntry table. 

                        So I have tried what you are saying with the calculation in the Project table as B_Hrs = Sum(TimeEntry::TEHours) "Evaluate this calculation from the context of: Project 3.

                        I have tried this before without the Evalute this calculation...

                        I just tried it and no data is in the B_Hours field.

                        • 9. Re: Sub Summary reports and Instant Web Publishing
                          philmodjunk

                          This is a very commonly used method to extract aggregate data from a set of related records.

                          You need to check the details to see what is not correctly set up.

                          Does your layout specify Projects 3 in the "show records from" drop down found in layout setup? (must show Projects 3)

                          Is this a field of type calculation or a field of type number with an auto-entered calculataion specified? (cannot work with auto-entered calc.)

                          If, as a test, you drop a portal to TimeEntry on this layout, do you see the expected records for the given project record?

                          • 10. Re: Sub Summary reports and Instant Web Publishing
                            swillette

                            The layout is based on Project 3.

                            The field type is a calculation on an the SQL Table Project. You can only add calc and summary fields to SQL tables.

                            I tried your test, and I am getting 3 numbers in the portal. The portal is based on Project.

                             

                            Back on the layout based on Project 3 where I can't see the numbers, if I watch the field with the Data Viewer, I see the same 3 numbers in the portal test which are incorrect. 

                            Yet the find showing the correct numbers in the TimeEntry layout. See image.

                            • 11. Re: Sub Summary reports and Instant Web Publishing
                              philmodjunk

                              I'm confused by what I see.

                              On what table occurrence is the left hand layout based?

                              It looks like it is based on TimeEntry with a portal to an occurrence of Projects--not what I've been describing at all and it does not display the records I'd expect if it's a portal to Projects 3.

                              What I've described is puting a portal to TimeEntry on the Projects 3 Layout to confirm that your relationship is working correctly.

                              • 12. Re: Sub Summary reports and Instant Web Publishing
                                swillette

                                Sorry about that, I put the portal on the wrong table.

                                Ok, I put a portal to TimeEntry on the Projects 3 layout. I'm not sure what this going to do since a the portal shows all the records of the TimeEntry table, not the found set of the layout based on the table TimeEntry. 

                                 

                                 

                                • 13. Re: Sub Summary reports and Instant Web Publishing
                                  philmodjunk

                                  What it shows is that there are 17 records in TimeEntry that match to the current projects record (107-096:10). If the sum function is working correctly, it should show a total of 94 on this record on this layout.

                                  The main purpose of this test was to confirm that we didn't have something unusual messing up the relationship between projects 3 and TimeEntry.

                                  BTW, any idea why Project ID in the top left of this shot shows 107-96: instead of 107-96:10? When I saw that in early screen shots, I assumed that the field wasn't wide enough to show the full string of characters, but it looks like it should be wide enough to show the full string here on this layout.

                                  • 14. Re: Sub Summary reports and Instant Web Publishing
                                    swillette

                                    I got you on the test portal. 

                                    Every ProjectID starts with a six digit id with the two last numbers missing (107-096:). This is the ProjectCode. I forgot to change that field back to ProjectCode after one of my many attempts to get this to work.

                                     

                                    So here is where I'm at. I can get the right numbers into the Project 3 layout based on my copied records table CopyFromRecords, from the 4 child tables. If you go back in this post and look at the screenshot of my DB diagram, I am using the related table on the left of Project 3 called CopiedFromTables. 

                                    Let me get all the number in and working, and I will post all the calc fields from Project so you can see how it is working with the CopiedFromTables table. 

                                    This solve my sub summary view problem with IWP, but it's slow having to copy and paste the records from the 4 children into the CopiedFromTable.

                                    1 2 3 Previous Next