10 Replies Latest reply on Jan 13, 2014 9:34 AM by philmodjunk

    Reporting troubles......probably easy.

    SteveMartino

      Title

      Reporting troubles......probably easy.

      Post

           Hello forum, Hi Phil.  I was hoping someone can help me with some trouble I am having with a report layout.  Basically the report gives me a list of nozzles, and a total of how many of each.  That works fine.  I can export this to an Excel, and create an order based on a reorder calculation, minus the stock on hand.  Also works fine in Excel.  Doesn't want to translate for me into FM (Pro 12 Adv.)

           Quick mock-up.  Screenshot one shows layout mode with the fields. On screenshot 2, what appears to be happening is instead of taking the number from 'Amount in Use' from the subsummary part for the calculations, it takes the number from Trailing Grand Summary Part.  Therefore all the calculations in the 'Re-Order Calc' use the same initial number.

           I've been toying around with many options, but cant get it to work.

           Any help would be greatly appreciated.

           Thanks

           Steve.

            

      report1.png

        • 1. Re: Reporting troubles......probably easy.
          SteveMartino

               screenshot 2

          • 2. Re: Reporting troubles......probably easy.
            philmodjunk

                 If a calculation field refers to the value of a summary field defined in the same table, you'll get a summary value based on all records in your found set. If you want to access a sub total as displayed in a sub summary layout part, use the get Summary function to access that sub total. The "break" field that you use as a parameter for this function should be the same field as the "when sorted by" field you select for the sub summary layout part that shows the same sub total.

            • 3. Re: Reporting troubles......probably easy.
              SteveMartino

                   Thanks again Phil (and again and again).  Never used that function, never could've figured it out on my own.

                   Steve

              • 4. Re: Reporting troubles......probably easy.
                SteveMartino

                     Hi again, quick follow up.  Can't seem to figure out how to do the following (see screenshot)  I am trying to total the numbers from the first column.  The field is based on an unstored calculation(c_fuel filter_order_amount), which is based on the unstored calculation(c_fuel filters_Reorder_calc).

                     I tried the Summary function, which I cannot use.  I tried merge field, no luck.  I tried to create another text field next to the field and then count, total, or summary function...all with no luck.

                     Is this possible to total this column based on the unstored calculations?

                     Any help or guidance would be greatly appreciated.

                     Thanks

                     Steve

                • 5. Re: Reporting troubles......probably easy.
                  philmodjunk

                       I see no reason why you can't use a summary field to compute this total. The summary field must be defined in the same table as the field being summarized and it must be a field with number specified as the result type.

                  • 6. Re: Reporting troubles......probably easy.
                    SteveMartino

                         Thanks Phil for your response.  I thought I could do the same but have no luck.  I end up with a total that's way out of whack.  I tried all variations.  I'll try some more later.

                         Thanks again

                         Steve

                    • 7. Re: Reporting troubles......probably easy.
                      philmodjunk

                           That "S" reveals the reason why. Each row of data in your report represents a group of records, not one record per row as I had assumed that you had here. Thus, the grand summary is computing a total where each of the values listed is repeated once for every record in your found set and you thus get a much larger value than you would expect just from looking at the layout in browse mode.

                           To get the correct total, you'd need to either:

                           1) total a different field

                           2) use "sum the reciprocal" to get the correct total: How to count the number of unique occurences in field.

                           3) Use execute SQL to compute the desired total.

                      • 8. Re: Reporting troubles......probably easy.
                        SteveMartino

                             Thanks Phil for your response.  At least I know I tried almost everything to figure out how to NOT do it.  I'll delve into these 3 ideas after work.

                             Very much appreciated

                             Steve

                        • 9. Re: Reporting troubles......probably easy.
                          SteveMartino

                               Hey Phil, thanks for the help.  It's just not going to work for me. 

                               1) total a different field---but I need to total the field I'm trying to total.

                               2) "sum of the reciprocal"--way out of my understanding.

                               3) Execute SQL--trying.  Read almost every link and many websites, can't get past the dreaded "?"

                               I'll give it another go when I have some time--maybe more research will prove helpful.

                               Thanks again

                               Steve.

                          • 10. Re: Reporting troubles......probably easy.
                            philmodjunk

                                 1) yes but the field that you are trying to total is calculated from other fields. There probably is a way to total values from those other fields in a way that gets the total that you need

                                 2) you have a calculated total for your first row of data stated as "9". Let's say the group of records represented by that row counts up as 5 records. Then defininging a calculation field as YourCalcField / GetSUmmary ( CountingSummaryField ; breakfield ) will compute a a value of 9/5 for this set of records and then a Summary field that totals this new calculation field will return the correct result

                                 The more I look at your layout and try to imagine the underlying data model, the more that I think you have issues with your data model that need to be corrected. Seems like you need a table where each row shown in your screen shot is represented by a single record that is linked via a relationship to the table on which you are basing the report you show in the screen shot. Calculating the need totals and sub totals would then be vastly simpler when computed from the context of such a table.