1 2 Previous Next 16 Replies Latest reply on Dec 20, 2011 12:09 AM by NaturSalus

    Cross Tab Report

    NaturSalus

      Title

      Cross Tab Report

      Post

      Hello,

      I am trying to setup a cross tab report with the following structure

      Year: 2009

      Deviation Origin----Q1----Q2---- Q3----Q4----------Total

      Audit Deviation-------5------4-------3------2------------14

      Customer-------------8------7------10-----28-----------53

      OOS------------------14-----23-----16-----36-----------89

      ...

      -----------------------27-----34-----29-----66----------156

      Year: 2010

      Deviation Origin----Q1----Q2---- Q3----Q4----------Total

      ....

       

      All the data of my current project come from the DEVIATION table


      Relevant field of the DEVIATION Table:

      __kp_Deviation

      DeviationCount. Summary. Count of __kp_Deviation

      Total. Calculation. Calculation result is: Number. = DeviationCount

      TotalSummary. Summary. = Count of Total

      z_Quarter. Calculation.  Calculation result is: Number.   = Ceiling ( Month ( DeviationDate ) / 3 )

      z_Q1_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 1 ; DeviationCount)

      z_Q1_Total. Summary. = Count of z_Q1_Number

      z_Q2_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 2 ; DeviationCount)

      z_Q2_Total. Summary. = Count of z_Q2_Number

      z_Q3_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 3 ; DeviationCount)

      z_Q3_Total. Summary. = Count of z_Q3_Number

      z_Q4_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 4 ; DeviationCount)

      z_Q4_Total. Summary. = Count of z_Q4_Number

      ...

      The Cross Tab Report is on the Report Deviation by Origin & Quarter Layout based on the DEVIATION table and the Default View = List View.

       The Structure of the  Report Deviation by Origin & Quarter Layout is the following:

      HEADER

      Sub-summary by z_DateDeviationYear (Leading)

      Sub-summary by DeviationOrigin (Leading)

      Trailing Grand Summary

       

      I am attaching a picture with view of the Report Deviation by Origin & Quarter Layout 

       

      In order to see if it is working first I switch to Browse Mode,

      then Sort Records by the foowing Sort Order:

      • z_DateDeviationYear
      • z_Quarter
      • DeviationOrigin

       

      Ascending Order

       

      finally I witch to Preview Mode.

       

      Unfortunatelly I am not getting what I was expecting.

       

      The real data as entered in the database and summarized by hand are (BTW this is the way I would like the Report to look like):

       

      Year: 2009

      Deviation Origin----Q1----Q2---- Q3----Q4----------Total

      Audit Deviation-------1------2------0-----0-------------3

      Customer Dev--------2------0------0-----0-------------2

      EHS Deviation--------0------1------1-----0-------------2

      Inspection------------0------0------2-----1-------------3

      TOTAL----------------3------3------3-----1------------10

       

       

      Year: 2010

      Deviation Origin----Q1----Q2---- Q3----Q4----------Total

      Inspection------------0------1------1-----0-------------2

      OOS-------------------2------1------0-----0-------------3

      TOTA------------------2------2------1-----0-------------5

       

      However I am not getting this. Since it seems that I cannot upload 2 images in the same post I will try to upload  a second image in the next post.

       

      So my question is, what should I modify to get the report desired?

       

      Thanks,

       

      natursalus

       

       

       

       

       

       



       

      report_layout_1.jpg

        • 1. Re: Cross Tab Report
          NaturSalus

          This is how it looks like my report:

           

          As you can see the marginal totals are wrong and the repeated occurrences for a particular Deviation Origin are shown individually

           

          natursalus

          • 2. Re: Cross Tab Report
            philmodjunk

            First off, I'd use this layout structure:

            Leading Sub Summary when sorted by Year
                Put year and column header labels here

            Leading Sub Summary when sorted by DeviationOrigin
                 Put DeviationOrigin and 5 filtered portals here to get 6 coluns in your cross tab

            Trailing Sub Summary When Sorted by Year
                  Put 5 Filtered portals here to get yearly totals.

            Note that there is no body part. I'm suggesting this structure on the assumption that you want to see data from more than one year at a time, grouped by year.

            Define this relationship:

            Deviation::anyfield X AllDeviations::anyField

            AllDeviations is a new table occurrence of Deviation. All portals on this layout will use this relationship, but with different filters.

            You'll need just one "count of" summary field, sTotalCount, that counts a never empty field, such as DeviationOrigin for all the total counts in your report.

            Each portal will refer to Alldeviations, and be set to display one portal row. Each will contain the same field, sTotalCount. Given how similar they are, you can set up one portal and then duplicate it 9 times and just change the portal filter expression for each one.

            The Upper Q1 portal would use a portal filter such as:

            Deviation::DeviationOrigin = AllDeviations::DeviationOrigin And Deviation::Year = AllDeviationYear and AllDeviation::z_Quarter = 1

            The lower Q1 portal would have:

            Deviation::Year = AllDeviationYear and AllDeviation::z_Quarter = 1

            The upper "Total" portal would use:

            Deviation::DeviationOrigin = AllDeviations::DeviationOrigin And Deviation::Year = AllDeviationYear

            The lower "total" portal would use:

            Deviation::Year = AllDeviationYear

            • 3. Re: Cross Tab Report
              NaturSalus

              Hello Phil,

               

              Thank you for your guidance.

              I will implement your suggestions.

              Thanks,

               

              natursalus

              • 4. Re: Cross Tab Report
                philmodjunk

                Spotted a typo. AllDeviationYear Should read as AllDeviation::Year

                • 5. Re: Cross Tab Report
                  NaturSalus

                  Hello Phil,

                  Excellent support.

                   

                  Thanks,

                   

                  natursalus

                  • 6. Re: Cross Tab Report
                    NaturSalus

                    Hello Phil,

                    I have implemented all your suggestions, but I am not getting the desired results.

                    First of all, the desired report, based on the the actual data in the database.

                    Year: 2009

                    Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                    Audit Deviation-------1------2------0-----0-------------3

                    Customer Dev--------2------0------0-----0-------------2

                    EHS Deviation--------0------1------1-----0-------------2

                    Inspection------------0------0------2-----1-------------3

                    TOTAL----------------3------3------3-----1------------10

                     

                     

                    Year: 2010

                    Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                    Inspection------------0------1------1-----0------------2

                    OOS-------------------2------1------0-----0-------------3

                    TOTAL-----------------2------2------1-----0-------------5

                     

                    Please have a  look at the result that I get after Sorting the records in Ascending order first by Year and then by DeviationOrigin.

                    • 7. Re: Cross Tab Report
                      NaturSalus

                      In the next posts I am goint to document what I did.

                      First of all the relationship between DEVIATION and AllDEVIATION both TOs of the DEVIATION table.

                      • 8. Re: Cross Tab Report
                        NaturSalus

                        Second, the relevant fields of the DEVIATION table

                        In red the changes suggested by you.

                         

                        Relevant field of the DEVIATION Table:

                        __kp_Deviation

                        DeviationCount. Summary. Count of __kp_Deviation

                        Total. Calculation. Calculation result is: Number. = DeviationCount

                        TotalSummary. Summary. = Count of Total

                        TotalCount. Summary. = Count of DeviationOrigin

                        z_Quarter. Calculation.  Calculation result is: Number.   = Ceiling ( Month ( DeviationDate ) / 3 )

                        z_Q1_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 1 ; DeviationCount)

                        z_Q1_Total. Summary. = Count of z_Q1_Number

                        z_Q2_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 2 ; DeviationCount)

                        z_Q2_Total. Summary. = Count of z_Q2_Number

                        z_Q3_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 3 ; DeviationCount)

                        z_Q3_Total. Summary. = Count of z_Q3_Number

                        z_Q4_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 4 ; DeviationCount)

                        z_Q4_Total. Summary. = Count of z_Q4_Number

                         

                        • 9. Re: Cross Tab Report
                          NaturSalus

                          Third, the layout setup

                          The Structure of the Report Deviation by Origin & Quarter Layout is the following:

                          • Sub-summary by z_DateDeviationYear (Leading). Place the DEVIATION: z_DateDeviationYear field as well as the Quarter (Q1, Q2, Q3, Q4) and Total labels.

                          • Sub-summary by DeviationOrigin (Leading). Place the DEVIATION::DeviationOrigin field and 5 filtered portals here to get the 6 columns of your cross tab report (Deviation Origin, Q1, Q2, Q3, Q4, Total).

                          • Sub-summary by z_DateDeviationYear (Trailing). Place 5 filtered portals to get the yearly totals.

                          Note that there is no Body part.  

                           

                          PORTALS

                          Every Portal will referer to the AllDEVIATION TO and will be set to display one portal row. Each Portal will contain the same field: DEVIATION::TotalCount

                          The Upper Q1 Portal would use a portal filter by Calculation such as:

                          • DEVIATION::DeviationOrigin = AIIDEVIATION::DeviationOrigin

                            And

                          • DEVIATION:: z_DateDeviationYear = AllDEVIATION::z_DateDeviationYear

                            And

                          • AllDEVIATION:: z_Quarter = 1

                          The Upper "Total" Portal would use a portal filter by Calculation such as:

                          • DEVIATION::DeviationOrigin = AIIDEVIATION::DeviationOrigin

                            And

                          • DEVIATION::z_DateDeviationYear = AIIDEVIATION::z_DateDeviationYear

                          The Lower Q1 Portal would use a portal filter by Calculation such as:

                          • DEVIATION::z_DateDeviationYear = AllDEVIATION::z_DateDeviationYear

                            And

                          • AllDEVIATION:: z_Quarter = 1

                          The Lower "Total" Portal would use a portal filter by Calculation such as:

                          • DEVIATION::z_DateDeviationYear = AIIDEVIATION::z_DateDeviationYear


                          The Portal filters for the rest of the quarters (Q2, Q3 & Q4) are similar to the one for Q1

                          the only changes are:

                          for Q2: AllDEVIATION:: z_Quarter = 2

                          for Q3: AllDEVIATION:: z_Quarter = 3

                          for Q4: AllDEVIATION:: z_Quarter = 4


                          • 10. Re: Cross Tab Report
                            philmodjunk

                            All that you have set up looks to be correct. In the screen shot, the in correct totals would be those found in the "total" column and the Trailing Sub Summary  "when sorted by year" row. Are the totals in the Q1 through Q4 columns correct?

                            Does your found set include all Deviation records for the two years shown in the screen shot?

                            Have you checked a table view of the raw data to confirm that the z_Quarter and z_Year fields are returning the expected values for every Deviation record?

                            • 11. Re: Cross Tab Report
                              NaturSalus

                              Hello Phil,

                               

                              THINGS THAT LOOK WRONG

                              If you compare the desired report, based on the the actual data in the database, and the data in the Report Deviation by Origin & Quarter you see incorrect data in:

                              • The Number of Deviations for each Type of Deviation Origin.

                              So,

                              Instead of: 

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------1------2------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------1------1-----0-------------2

                              Inspection------------0------0------2-----1-------------3

                              TOTAL----------------3------3------3-----1------------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------1------1-----0------------2

                              OOS-------------------2------1------0-----0-------------3

                              TOTAL-----------------2------2------1-----0-------------5

                               

                              I am getting:

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------3------3------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------2------2-----0-------------2

                              Inspection------------0------0------3-----3-------------3

                              TOTAL---------------10-----10----10-----10-----------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------2------2-----0------------2

                              OOS-------------------3------3------0-----0-------------3

                              TOTAL-----------------2------2------1-----0-------------5

                               

                              • The Yearly Totals for each Quarter

                               

                              So,

                              Instead of: 

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------1------2------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------1------1-----0-------------2

                              Inspection------------0------0------2-----1-------------3

                              TOTAL----------------3------3------3-----1------------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------1------1-----0------------2

                              OOS-------------------2------1------0-----0-------------3

                              TOTAL-----------------2------2------1-----0-------------5

                               

                              I am getting:

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------3------3------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------2------2-----0-------------2

                              Inspection------------0------0------3-----3-------------3

                              TOTAL---------------10-----10----10-----10-----------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------2------2-----0------------2

                              OOS-------------------3------3------0-----0-------------3

                              TOTAL-----------------2------2------1-----0-------------5

                               

                              THINGS THAT LOOK RIGHT

                              The Lateral Totals for each DeviationOrigin type seem to be okay as well as the Grand Total

                               

                              So,

                              It was expected: 

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------1------2------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------1------1-----0-------------2

                              Inspection------------0------0------2-----1-------------3

                              TOTAL----------------3------3------3-----1------------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------1------1-----0------------2

                              OOS-------------------2------1------0-----0-------------3

                              TOTAL-----------------2------2------1-----0-------------5

                               

                              I am getting:

                              Year: 2009

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Audit Deviation-------3------3------0-----0-------------3

                              Customer Dev--------2------0------0-----0-------------2

                              EHS Deviation--------0------2------2-----0-------------2

                              Inspection------------0------0------3-----3-------------3

                              TOTAL---------------10-----10----10-----10-----------10

                               

                               

                              Year: 2010

                              Deviation Origin----Q1----Q2---- Q3----Q4----------Total

                              Inspection------------0------2------2-----0------------2

                              OOS-------------------3------3------0-----0------------3

                              TOTAL-----------------2------2------1-----0------------5

                               

                              In a word what is not working is the counting of deviations for each type of DeviationOrigin.

                               

                               

                              In the screen shot, the incorrect totals would be those found in the "total" column and the Trailing Sub Summary  "when sorted by year" row.

                              In the Trailing Sub Summary part, there are two types of totals:

                              • Totals per Quarter
                              • Grand Total

                               

                              Totals per Quarter are all wrong.

                              Grand Total is correct

                               

                              Are the totals in the Q1 through Q4 columns correct?

                              No.

                              The totals per Quarter are all incorrect.

                               

                              Does your found set include all Deviation records for the two years shown in the screen shot?

                              Yes. I am attaching picture.


                              Have you checked a table view of the raw data to confirm that the z_Quarter and z_Year fields are returning the expected values for every Deviation record?

                              Since the number of fields is big I have to check the data individually.

                              For example:

                              Deviation 1

                              Deviation Number: 1

                              Deviation Date: 06/01/2009

                              DeviationOrigin: Customer Service Deviation

                              z_Quarter = 1

                              z_Q1_Number = 15

                              z_Q2_Number =

                              z_Q3_Number =

                              z_Q4_Number =

                              z_Q1_Total = 5

                              z_Q2_Total = 5

                              z_Q3_Total = 4

                              z_Q4_Total = 1

                              TotalSummary = 15

                              TotalCount = 15

                               

                              Deviation 2

                              Deviation Number: 2

                              Deviation Date: 12/02/2009

                              DeviationOrigin: Customer Service Deviation

                              z_Quarter = 1

                              z_Q1_Number = 15

                              z_Q2_Number =

                              z_Q3_Number =

                              z_Q4_Number =

                              z_Q1_Total = 5

                              z_Q2_Total = 5

                              z_Q3_Total = 4

                              z_Q4_Total = 1

                              TotalSummary = 15

                              TotalCount = 15

                               

                               

                              Seems like the problem could be with the:

                               

                              z_Q1_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 1 ; DeviationCount)

                              z_Q1_Total. Summary. = Count of z_Q1_Number

                              z_Q2_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 2 ; DeviationCount)

                              z_Q2_Total. Summary. = Count of z_Q2_Number

                              z_Q3_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 3 ; DeviationCount)

                              z_Q3_Total. Summary. = Count of z_Q3_Number

                              z_Q4_Number. Calculation. Calculation result is: Number. = Case ( z_Quarter = 4 ; DeviationCount)

                              z_Q4_Total. Summary. = Count of z_Q4_Number

                               

                              But so far I cannot spot the mistake.

                               

                               


                              • 12. Re: Cross Tab Report
                                philmodjunk

                                Sorry for missing the part of your post where you posted the expected totals.

                                Since the number of fields is big I have to check the data individually.

                                Such a table veiw need only have columns for Year, Deviation origin, z_Quarter and Z_Year.

                                I'd just like confirmation that the fields used in the filters have the correct values for every quarter.

                                Now that I've finally figured out which section is wrong, we need to look at the DeviationOrigin field. Can you confirm that it is a field of type Text when you examine its definition in Manage | Database | Fields?

                                I'll gin up a small demo file and see if anything comes to mind when I do so. I can then upload it here for you to download and compare to yours.

                                • 13. Re: Cross Tab Report
                                  NaturSalus

                                  Hello Phil,

                                  Now that I've finally figured out which section is wrong, we need to look at the DeviationOrigin field. Can you confirm that it is a field of type Text when you examine its definition in Manage | Database | Fields?

                                  Yes it is Text tpe


                                  Such a table veiw need only have columns for Year, Deviation origin, z_Quarter and Z_Year.

                                  I am attaching a picture.


                                  I really appreciate your help.

                                  Crosstab reports is one of my "pending" learning subjects in FM and I want to learn it right.

                                  Thanks,

                                  natursalus

                                  • 14. Re: Cross Tab Report
                                    philmodjunk

                                    Here's a working demo file: http://www.4shared.com/file/ZUAR95fq/DeviationOrigin_CrossTab.html

                                    Can't spot any differences between it and what you have posted. Maybe you will when you take a look at it.

                                    1 2 Previous Next