# Cross Tab Report

**NaturSalus**Dec 16, 2011 8:21 AM

### 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