12 Replies Latest reply on Dec 4, 2009 2:50 PM by philmodjunk

    Need help with a Report creation.

    DJD

      Title

      Need help with a Report creation.

      Post

      To simplify my situation, I have two tables: Projects and Savings.  There are (or can be) multiple Savings per Project.  Relationship Key is Project_ID.  I'm trying to create a simple report that lists each Project and then that Project's Savings detail as follows:

       

      Project_ID, Project_City, Project_State, Savings::Savings_Type, Savings::Savings_Amount, Savings::Savings_Description

                                                            Savings::Savings_Type, Savings::Savings_Amount, Savings::Savings_Description


                                                                                            Savings::Savings_Amount_Summary

       

      As an example, if a Project has multiple Savings Types then I want the first Savings Type to list on the same line as the Project Information, then any and all subsequent Savings Types I want to list under the first line of Savings Information and then all Savings Amounts for that Project are totaled.

       

      I have tried Sub-Summaries and Portals but I can't seem to get the information to list out properly.  I'm not sure if the Layout should use the Projects Table or the Savings Table (I've tried both) and I have tried different Sorting options.

       

      FMPro V. 10.03

      WinXP

      FMPro Novice

       

      Thank you for any and all help.

        • 1. Re: Need help with a Report creation.
          comment_1
            

          I'd say the report should be based on the Savings table - but be aware that this will exclude any projects that do not have at least one related record in Savings.

           

          If you want the project details to appear on the same line as as the first saving of the group, you cannot use a leading sub-summary part. You must use a calculation field (defined in the Savings table) that gets the parent project's details only when ProjectID is different from the previous record's ProjectID (use the GetNthRecord() function for this).

          • 2. Re: Need help with a Report creation.
            philmodjunk
              

            There's a trick for aligning a sub summary heading with the first row of the grouped body records. It's been around awhile but it still works.

             

            Start with your basic sub-summary report set up:

            SubSummary sorted by Savings:: ProjectID---------

            Put fields from Project Here

            Body---------------

            Put Savings fields here

             

            Now, for any project fields and/or layout text that you want to appear once each sub-summary group, aligned with the first savings row....

             

            Select Format | align text | bottom

            Move them into the body part with the bottoms of each such object aligned with the bottoms of your Savings fields.

            Resize the SubSummary part if you now need to reduce its size since some of the fields are now in the body

            Click on each such object's selection handle and resize it by dragging up until at least one pixel of the object crosses the sub-summary part boundary.

            Save, and sort your records by ProjectID to see the result in FMP 10. In earlier versions, you'll have to preview or print the report to see subsummary parts.

             

            You should see this type of format:

            Project Fields ..... First savings record for this group

                                     Second Savings Record for this group

                                     Third savings record for this group

            Project fields .......First savings record for 2nd group

                                     Second Savings record for 2nd group

            and so forth.

            • 3. Re: Need help with a Report creation.
              DJD
                

              PhilModJunk,

               

              Not sure I totally understand.  Are you saying to allow the Project Fields to be half in the SubSummary and half in the Body?  Also, should this Layout be based on the Projects or the Savings Table.

               

              Thanks,

               

               

              • 4. Re: Need help with a Report creation.
                philmodjunk
                  

                Base it on Savings.

                As long as one pixel of a layout object extends into the sub-summary part, it will be treated as though the entire object is within the sub-summary part. By extending the selection handles into the sub-summary from the body, you're placing an invisible part of that object into the SubSummary part. (You can make the sub-summary part only a few pixels tall, if you want.)

                • 5. Re: Need help with a Report creation.
                  DJD
                    

                  I have Active Projects and Completed Projects in this database.  This report is based on the Savings for the Completed Projects.  When I base the Layout on the Savings Table it pulls the Active Projects with Savings also.  I still haven't figured out the "first Savings detail on the Project info line" yet but this other problem has popped up now.

                   

                  Thanks,

                   

                   

                  • 6. Re: Need help with a Report creation.
                    DJD
                       Also, I'd like the Completed Projects without any Savings to show up in the report. 
                    • 7. Re: Need help with a Report creation.
                      DJD
                        

                      Phil,

                       

                      Ok, I understand now how to make the first Savings appear on the Project Info line, Thanks.  But I'm still confused on the Active vs. Completed Projects and just showing the Completed Projects Savings and those Completed Projects without any Savings.

                       

                      Thanks,

                       

                      • 8. Re: Need help with a Report creation.
                        comment_1
                          

                        DJD wrote:
                        Also, I'd like the Completed Projects without any Savings to show up in the report. 

                        Then you're back to square one. Do the report from Projects, with a portal to Savings. Give the portal enough rows to accommodate the largest expected number of savings, and set it to slide up.


                        • 9. Re: Need help with a Report creation.
                          DJD
                            

                          Okay, thank you comment.  I will try that approach.

                           

                           

                          • 10. Re: Need help with a Report creation.
                            DJD
                              

                            comment,

                             

                            How do you set the Body of a Layout to slide also?  I set my Savings Portal to slide but it is staying the same size as the Body still.  Any suggestions?

                             

                            Thanks,

                            • 11. Re: Need help with a Report creation.
                              comment_1
                                 Check the 'Also reduce the size of the enclosing part' box.
                              • 12. Re: Need help with a Report creation.
                                philmodjunk
                                  

                                Make sure you select "Also reduce the size of the enclosing part".

                                 

                                Make sure any layout objects in the same layout part that are along side or below the portal are also selected  to slide up/Reduce size of enclosing part.

                                 

                                Such sliding is only visible in Preview mode and when report is printed/PDF'd.