×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Matrix Report

thread243-1419732

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

Thanks,

Leo winky smile  

RE: Matrix Report

Try

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

RE: Matrix Report

(OP)
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 winky smile  

RE: Matrix Report

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

CODE

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

and got:

CODE

                         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.

RE: Matrix Report

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

RE: Matrix Report

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

CODE

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.      

RE: Matrix Report

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.

RE: Matrix Report

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!

RE: Matrix Report

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:

CODE

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close