Matrix Report

[LeonelSanchezJr]

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


Thanks,

Leo ;-)

 

[focusdev]

Try

TABLE FILE MYFILE
SUM FIELD_VALUE
BY PRODUCT
BY STATUS
ACROSS MONTH
END

 

[LeonelSanchezJr]

Thank you, however, I had tried that previously and it didn't work.

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 ;-)

 

[focwizard]

Without seeing your actual code, it's difficult to see why you're getting the output you do. However, I tried this:

TABLE FILE CAR
SUM      RCOST 
    OVER DCOST
    OVER COMPUTE PERCENT/F6.2% = RCOST/DCOST;
BY COUNTRY
ACROSS SEATS
END

and got:

                         SEATS 
                             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.

 

[ka2c]

Sorry to ride along this question. I'm doing something similar and would like to get an average of the column total at the end of the report. I'm also using the 'BY... ACROSS' instruction.

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

 

[focwizard]

I'm assuiming you're using ACROSS, so here's a way to do it:

DEFINE FILE CAR
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.

 

[ka2c]

Thanks for your helpful idea. My situation is actually a bit more complicated than I stated above, but I probably can expand on your idea & make things work. Thanks again.

 

[ka2c]

Hi Focwizard, I managed to work around your idea and created my desired report which includes a row total (ACROSS... ROW-TOTAL). Unfortunately, on the summary line (ON TABLE SUMMARIZE), this 'row total' column contains the column total instead of the column average. Is there a simple way to fix that? Thanks!

 

[focwizard]

The issue is, ON TABLE SUMMARIZE will do a SUBTOTAL for everything except COMPUTES, which are redone using the subtotal value. Also, HOW do you calculate the column average?

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:

DEFINE FILE CAR
ONE/I4=1;
END
TABLE FILE CAR
SUM RETAIL NOPRINT
[red]FST.[/red]ONE NOPRINT
COMPUTE XRETAIL/D7 = RETAIL/[red]FST.[/red]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:

DEFINE FILE CAR
ONE/I2 = 1;
END
TABLE FILE CAR
SUM RETAIL NOPRINT
FST.ONE NOPRINT
COMPUTE XRETAIL/D7 = RETAIL/FST.ONE;
BY COUNTRY 
ACROSS BODYTYPE
[RED]AND COMPUTE ROWTOTAL/D7 = (C3+C6+C9+C12+C15+C18+C21);[/RED]
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.