13 Replies Latest reply on Apr 21, 2017 8:39 AM by philmodjunk

    Getting Rid of Blank Records in Report

    fmpuser0

      I have a database with the following structure:

       

       

      1. Expense Type -> This account has only two values: fixed expenses and variable expenses
      2. Account
      3. Sub-Account
      4. Date
      5. Concept
      6. Amount

       

      I need to create a report that shows sub-totals by Expense Type, then Account and then Sub-Account but I need Concept to display only when the type of expense is variable.

       

      I have created the following two additional fields:

       

      • c_Concept: a calculated field per formula If ( PatternCount ( Expense Type ; "Trip Expenses" ) ; Concept ; "")
      • c-AmountReports: a calculated field per formula If ( PatternCount ( c_Concept ; " " ) ;  Amount ;"" )

       

      I have succeeded in creating the report I want based on these two new fields but I can´t get rid of the blank spaces occupied by them when they contain no text, i.e. when they are a fixed expense despite, having having played with all options available for Sliding.

       

      I am using FMP 11.

       

      Any help in solving this issue will be greatly appreciated.

        • 1. Re: Getting Rid of Blank Records in Report
          philmodjunk

          Your calculations aren't consistent with what you post for 1. Expense Type would never have the value "Trip Expenses" and patterncount makes no sense let alone using patterncount to check for a space character.

           

          But you can exclude records from a report by performing a Find that excludes them from the found set. Putting an * In a field, for example, will limit the found set to records where that field is not empty.

          • 2. Re: Getting Rid of Blank Records in Report
            fmpuser0

            I copied the formula for c_Concept from an earlier version. The correct version is

            • c_Concept: a calculated field per formula If ( PatternCount ( Expense Type ; "Fixed Expenses" ) ; Concept ; "")

            My apologies.

             

            When I exclude all records that are associated with fixed expenses the subtotals disappear. Attached is a screenshot of the layout.

            fmplayout.jpg

            • 3. Re: Getting Rid of Blank Records in Report
              philmodjunk

              I can't tell anything from that screen shot. I don't see anything on the layout that looks like a summary field that might be used to report a sub total.

               

              Why use pattern count at all in these calculations?

               

              and the second calculation field:

               

              c-AmountReports: a calculated field per formula If ( PatternCount ( c_Concept ; " " ) ;  Amount ;"" )

               

              Still does not make any sense.

              • 4. Re: Getting Rid of Blank Records in Report
                fmpuser0

                philmodjunk,

                Have you scrolled the screenshot all the way to the right?

                It is showing subtotals on my report the way I need them.

                 

                The c-AmountReports field is also performing as I intended it to with the use of PatternCount for no other reason that it it is working and did not give it another thought. Any better solution will be appreciated.

                • 5. Re: Getting Rid of Blank Records in Report
                  Malcolm

                  Subsummary sections appear when your sort criteria include the break field. [ it is a pity that ] Subsummary sections [ do not ] respect the sliding rules so [ if they did ] you may be able to minimise the section when the fields are empty.

                   

                  Malcolm

                  • 6. Re: Getting Rid of Blank Records in Report
                    philmodjunk

                    But what is the purpose of that second calculation field? How do you use it?

                     

                    C_AmountReports returns the value of amount if there is at least one space somewhere in c_Concept. That makes no sense at all as I see no purpose for that field.

                     

                    The details I need aren't in your screen shot, can't see how your sub summaries are defined, can't see how your summary fields are defined.

                    • 7. Re: Getting Rid of Blank Records in Report
                      fmpuser0

                      philmodjunk,

                       

                      The purpose o the second calculation field is the same as the first one: show it on the reports if it is associated with a fixed expense (it does not show if it is with a variable expense because then it is blank).

                       

                      But I am not intent on using the solution I have devised. I just did not know any other to accomplish my goal of "I need to create a report that shows sub-totals by Expense Type, then Account and then Sub-Account but I need Concept to display only when the type of expense is variable.".

                       

                      Is the attached what you need vis-à-vis the definition of the summary fields?fmplayoutsubtotaldef.jpg

                      • 8. Re: Getting Rid of Blank Records in Report
                        fmpuser0

                        Malcolm,

                         

                        Given that "Subsummary sections respect the sliding rules so you may be able to minimise the section when the fields are empty." and since I have the sliding set as follows

                         

                        what is the reason that I am seeing all the blank records like this

                         

                        • 9. Re: Getting Rid of Blank Records in Report
                          fmpuser0

                          philmodjunk,

                           

                          Correction on screenshot below:

                           

                          the Sub-summary account by TripExp should be by Variable Expense (The field name has been changed from Trip Expenses to Variable Expenses)

                           

                          • 10. Re: Getting Rid of Blank Records in Report
                            philmodjunk

                            My advice is not to use these calculation fields. If you use a summary field that computes the total of amount and put it on your layout in a sub summary part "when sorted by" Expense Type

                             

                            You can get one sub total for each Expense Type. If you perform a find for only one Expense Type, then you also can get a report with a total for just one Expense Type. that's two different ways to use the same layout depending on what you need.

                             

                            No need for calculation fields or anything set to slide up.

                            • 11. Re: Getting Rid of Blank Records in Report
                              Malcolm

                              fmpuser0 My apologies. Subsummary parts do not recover blank space.

                               

                              Malcolm

                              • 12. Re: Getting Rid of Blank Records in Report
                                fmpuser0

                                philmodjunk,

                                 

                                The problem is that when I do a find for Expense Type "variable" the subtotals for "fixed" are no longer visible and I need everything on the same report like in the sample you can see here

                                • 13. Re: Getting Rid of Blank Records in Report
                                  philmodjunk

                                  To repeat:

                                   

                                  Find for the records you want for your report. Sort to group them by type. Use Summary fields that simply Summarize Amount and put that field in a sub summary part (when sorted by type). You then get a sub total for each type included in your report and you do not have blank records.