1 Reply Latest reply on Oct 21, 2014 7:25 AM by philmodjunk

    Problems with calc field in Sub Summary report

    sccardais

      Title

      Problems with calc field in Sub Summary report

      Post

      I have a sub summary report that includes the a calculation field in a sub summary part. The Layout doesn't have a body.

      The calculation is an unstored number result based on Summary fields using the Get Summary function.

      The calculation uses Summary fields from an external data source - also FMP 13 file on the same local drive.

      (GetSummary(SURVEYS_Same Org::s_zPromoter; SURVEYS_Same Org::Org_ID) / GetSummary(SURVEYS_Same Org::s_CountResponseID;SURVEYS_Same Org::Org_ID)
      - GetSummary(SURVEYS_Same Org::s_zDetractor;SURVEYS_Same Org::Org_ID)/GetSummary(SURVEYS_Same Org::s_CountResponseID;SURVEYS_Same Org::Org_ID))

      The report looks something like the attached screenshot. It summarizes Orgs (customers) based on the number of customer satisfaction surveys they've submitted. The green shaded column is the calculation causing problems.

      I'm having two problems.

      1. Sorting on the calculation field doesn't change the sort order. I want to sort this report by the calculation with the lowest numbers (negative) first. Data viewer shows the correct values but for some reason, sorting on this field has no affect on the report.

      2. Sorting on the calculation field takes forever. I suspect this is because it's an unstored calculation. What can be done to speed this up?

      Sub_Summary_Calc.png

        • 1. Re: Problems with calc field in Sub Summary report
          philmodjunk

          I suspect that you can't sort on the calculation field for the same reasons that you can't sort on a summary field using the option to list the field in a sort order (only the re-order method can be used). And since this is a calculation field, I don't think that you can use the re-order option either.

          Even if that were possible, it would be very slow.

          You may need to use a table where you have one record for each unique combination of Company and product, then use a relationship to match to and compute the values that you want here.

          The fact that the data comes from an external source probably does not have much, if any effect on what you are observing.