AnsweredAssumed Answered

Cross Tab Report

Question asked by NaturSalus on Dec 16, 2011
Latest reply on Dec 20, 2011 by 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

Outcomes