5 Replies Latest reply on Dec 7, 2011 11:09 AM by NaturSalus

    Report with Several Subsummaries

    NaturSalus

      Title

      Report with Several Subsummaries

      Post

      Hello,

      I have created the Deviation Report with five subsummaries that is not showing the data correctly.

      I am asking help to troubleshooting it.

      I am developping a project that manages Deviations reports. So the basic structure of the DEVIATION table is the following:

      __kp_Deviation

      DeviationTitle

      DeviationDescription

      ...

      DeviationOrigin

      DeviationSubject

      ...

      DeviationDate (Date)

      DeviationCount (Summary = Count of __kp_Deviation)

      Total (Calculation = Get(TotalRecordCount))

      z_DateDeviationMonth (Calculation = Month(DeviationDate))

      z_DateDeviationMonthName (Calculation = MonthName(DeviationDate))

      z_DateDeviationYear (Calculation = Year(DeviationDate))

      z_Quarter

       

      The Deviation Report is created by the Report Script attached to the Report Button that is on the the Deviation_Detail Layout based on the DEVIATION Table.

       

      The Report Script contains, apart from other script steps, the following Sort Records script step:

      • z_DateDeviationYear
      • z_Quarter
      • z_DateDeviationMonth
      • DeviationOrigin
      • DeviationSubject

      and sorts in Ascending Order

       

      Deviation Report Layout Structure

      The Deviation Report is created from the Deviation Report layout based on the DEVIATION Table

      The Deviation Report layout has the following structure:

      HEADER that cointains the "Deviation Report" text and "Total" text

      Sub_summary by z_DateDeviationYear (Leading) that contains the folllowing fields:

      DEVIATION::z_DateDeviationYear    and   DEVIATION::DeviationCount

      Sub_summary by z_Quarter (Leading) that contains the folllowing fields:

      DEVIATION::z_Quarter    and   DEVIATION::DeviationCount

      Sub_summary by z_DateDeviationMonthDate (Leading) that contains the folllowing fields:

      DEVIATION::z_DateDeviationMonthDate    and   DEVIATION::DeviationCount

      Sub_summary by DeviationOrigin (Leading) that contains the folllowing fields:

      DEVIATION::DeviationOrigin     and   DEVIATION::DeviationCount

      Sub_summary by DeviationSubject (Leading) that contains the folllowing fields:

      DEVIATION::DeviationSubject     and   DEVIATION::DeviationCount


      Deviation Report Structure Purpose

      The purpose of the Deviation Report is to come out with the following basic report:

      Deviation Report                 Total 

      Year                                  Number of Deviations per Year

      Quarter                              Number of Deviations per Quarter   

      Month                                Number of Deviations per Month  

      Deviation Origin                  Number of Deviations per type of Deviation Origin

      Deviation Subject                Number of Deviations per type of Deviation Subject

       

       

      In order to troubleshoot it, my first question is related to the suitability of the calculation on which it is based the DEVIATION::DeviationCount field. Should I use the DEVIATION::__kp_Deviation field or should I use another field?

      I wonder what happens to DEVIATION::DeviationCount field when the user erases a record. In this case the generated DEVIATION::__kp_Deviation value stays in the database although it no longer has any significance for the updated database. Am I right?

       

      As you can see from the attached picture, there is something wrong because for the OOS Deviation in the Equipment that was reported on February 2009 is placed under the Second Quarter instead of under the first Quarter as it should.

      deviation report wrong

      Help on how to sort this out is much needed and appreciated.

       

      Thanks,

       

      natursalus

       

       

        • 1. Re: Report with Several Subsummaries
          philmodjunk

          In order to troubleshoot it, my first question is related to the suitability of the calculation on which it is based the DEVIATION::DeviationCount field. Should I use the DEVIATION::__kp_Deviation field or should I use another field?

          Any never empty field will produce the same results. The primary key field would by my preferred choice for this as it should never be an empty field.

          I wonder what happens to DEVIATION::DeviationCount field when the user erases a record. In this case the generated DEVIATION::__kp_Deviation value stays in the database although it no longer has any significance for the updated database. Am I right?

          Removing a record (either by deleting it or  omitting it from the report's found set) will reduce the count returned from this field by one. The specific values in the field do not matter. This summary field only counts the number of records with a non empty primary key field.

          The structure of your layout and the sort order you use looks to be correct. Except for one discrepancy that may just be a typo on your part when you posted your question: z_DateDeviationMonth is listed as the sort field, but the description of the associated sub summary part lists: z_DateDeviationMonthDate.

          Assuming that's a typo, if you are not getting the expected grouping, then you'll need to check two things first: The data types of the fields used in the sort. The values in those fields in the records where they group incorrectly.

          In Manage | Database | fields, you should confirm that these fields are of type number:

          z_DateDeviationYear
          z_Quarter
          z_DateDeviationMonth

          If they are, then find the record for the OOS Deviation and check the values in these same fields.

          • 2. Re: Report with Several Subsummaries
            NaturSalus

            Hello Phil,

            Thanks for looking into my question

             

             

            Except for one discrepancy that may just be a typo on your part when you posted your question: z_DateDeviationMonth is listed as the sort field, but the description of the associated sub summary part lists: z_DateDeviationMonthDate.

            Yes, it is a typo on my part.

            My final objective is to have the Month Names on the Deviation Report sorted as per the position in the calendar year and not as per the alphabetical order.

            I want:

            • January
            • February
            • March

             

            I don't want:

            • February
            • January
            • March

             

            This is the reason why

            I use the z_DateDeviationMonth in the Sort Records script step of the Report Script. 

            and

            the Sub_summary by z_DateDeviationMonth (Leading) that contains the folllowing fields:

            DEVIATION::z_DateDeviationMonthDate    and   DEVIATION::DeviationCount

             

            In Manage | Database | fields, you should confirm that these fields are of type number:

            z_DateDeviationYear
            z_Quarter
            z_DateDeviationMonth

             

            All of them are of type number.

             

            If they are, then find the record for the OOS Deviation and check the values in these same fields.

             

            __kp_Deviation = 12

             z_DateDeviationYear = 2009

            z_Quarter = 2

            z_DateDeviationMonth = 2

            z_DateDeviationMonthName = February

            DeviationDate = 19/02/2009

            DeviationOrigin = OOS

            DeviationSubject = Equipment

            DeviationTitle = Deviation 12

             

            So, what's wrong is the value for z_Quarter

            The calculation for Deviation::Z_Quarter is the following:

            Ceiling ( Month ( DeviationDate ) / 3 )

            Calculation result must be a number

            Do not evaluate if all refrence fields are empty is Checked

            Do not replace existing value of field (if any) is Checked

             

            Can you see where is my mistake?

             

            Thanks

             

            natursalus

            • 3. Re: Report with Several Subsummaries
              NaturSalus

              Hello Phil,

               

              I found the mistake.

               

              The field Deviation::Z_Quarter was Number Type and it should be Calculation Type.

              After the change everything is working as it should.

               

              Many thanks,

               

              natursalus

              • 4. Re: Report with Several Subsummaries
                philmodjunk

                My final objective is to have the Month Names on the Deviation Report sorted as per the position in the calendar year and not as per the alphabetical order.

                Here's another way to sort by month in chronological, rather than alphabetical order:

                Define a calculation field specified to return "date": Datefield - day ( datefield ) + 1

                This returns the date of the first day of the same month for all dates that might be entered in DateField.

                You can put such a field on your layout and use data formatting to display just the month, but if you sort on this field, it will sort records in chronological order by month.

                Thus, is does the job of two of your current calculation fields. Wink

                • 5. Re: Report with Several Subsummaries
                  NaturSalus

                  Hello Phil,

                  Thank you for your valuable tip.

                  Can`t think of anything better than FM to keep the mind busy.