Matrix Report
Matrix Report
(OP)
I am using Developers Studio 714.
The report should look like this:
MONTH MONTH MONTH
PRODUCT STATUS 10/2007 9/2007 8/2007
ABC APPLICABLE 365 458 617
ABC SOLD 118 179 226
ABC PERCENT 32% 39% 17%
However, I am getting the STATUS values appearing before each MONTH.
MONTH MONTH
PRODUCT STATUS 10/2007 STATUS 9/2007 STATUS
ABC APPLICABLE 365 APPLICABLE 458 APPLICABLE
ABC SOLD 118 SOLD 179 SOLD
ABC PERCENT 32% PERCENT 39% PERCENT
I'm sure it's something simple, but I can't get it!
Please help....
The report should look like this:
MONTH MONTH MONTH
PRODUCT STATUS 10/2007 9/2007 8/2007
ABC APPLICABLE 365 458 617
ABC SOLD 118 179 226
ABC PERCENT 32% 39% 17%
However, I am getting the STATUS values appearing before each MONTH.
MONTH MONTH
PRODUCT STATUS 10/2007 STATUS 9/2007 STATUS
ABC APPLICABLE 365 APPLICABLE 458 APPLICABLE
ABC SOLD 118 SOLD 179 SOLD
ABC PERCENT 32% PERCENT 39% PERCENT
I'm sure it's something simple, but I can't get it!
Please help....
Thanks,
Leo 
Talk To Other Members
RE: Matrix Report
TABLE FILE MYFILE
SUM FIELD_VALUE
BY PRODUCT
BY STATUS
ACROSS MONTH
END
RE: Matrix Report
I had to create it as an FML report using the FOR command and placing all of the amount fields into one common column.
Thank you.
Thanks,
Leo
RE: Matrix Report
CODE
SUM RCOST
OVER DCOST
OVER COMPUTE PERCENT/F6.2% = RCOST/DCOST;
BY COUNTRY
ACROSS SEATS
END
and got:
CODE
2 4 5
COUNTRY
----------------------------------------------------------
ENGLAND RETAIL_COST 13,978 17,850 13,491
DEALER_COST 11,719 14,940 11,194
PERCENT 1.19% 1.19% 1.21%
FRANCE RETAIL_COST . . 5,610
DEALER_COST . . 4,631
PERCENT . . 1.21%
ITALY RETAIL_COST 45,140 5,925 .
DEALER_COST 36,320 4,915 .
PERCENT 1.24% 1.21% .
JAPAN RETAIL_COST . 6,478 .
DEALER_COST . 5,512 .
PERCENT . 1.18% .
W GERMANY RETAIL_COST . 6,355 58,377
DEALER_COST . 6,000 48,563
PERCENT . 1.06% 1.20%
By changing fields appropriately, you should get what you want.
RE: Matrix Report
MONTH MONTH MONTH
PRODUCT 10/2007 9/2007 8/2007
ABC 365 458 617
ABC 118 179 226
ABC 32 39 17
TOTAL 515 676 860
AVERAGE 171.7 225.3 286.7 <-- need!!
RE: Matrix Report
CODE
ONE/I4=1;
END
TABLE FILE CAR
SUM RETAIL NOPRINT
ONE NOPRINT
COMPUTE XRETAIL/D7 = RETAIL/ONE;
BY COUNTRY
ACROSS SEATS
ON TABLE SUMMARIZE
END
This takes advantage of the fact that, for the detail records, dividing the value by 1 give the value itself, while on the SUMMARIZE line, it uses the sub-totals. The sub-total of the 1's is the number of rows, so dividing by that gives the average.
If you leave off the NOPRINT, you can see what's happening.
RE: Matrix Report
RE: Matrix Report
RE: Matrix Report
What I gave before is a 'weighted' average. Because there are more values for 'W GERMANY', it gets more 'weight' for the average. Change it like this:
CODE
ONE/I4=1;
END
TABLE FILE CAR
SUM RETAIL NOPRINT
FST.ONE NOPRINT
COMPUTE XRETAIL/D7 = RETAIL/FST.ONE;
BY COUNTRY
ACROSS SEATS
ON TABLE SUMMARIZE
END
and it removes the row weighting. BUT, there's still the column weighting. Change it further, like this:
CODE
ONE/I2 = 1;
END
TABLE FILE CAR
SUM RETAIL NOPRINT
FST.ONE NOPRINT
COMPUTE XRETAIL/D7 = RETAIL/FST.ONE;
BY COUNTRY
ACROSS BODYTYPE
AND COMPUTE ROWTOTAL/D7 = (C3+C6+C9+C12+C15+C18+C21);
ON TABLE SUMMARIZE
END
Using a COMPUTE with column ('C') notation after the ACROSS, rather than a ROW-TOTAL, and you get a true average on the SUMMARIZE line. But, again, this is NOT the average of the row-totals, but the SUM of the column-averages.