Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] Matrix Report - How to sum data in rows?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] Matrix Report - How to sum data in rows?
 Login/Join
 
Expert
posted
My brain isn't working today. I'd like to create a matrix report that summarizes the data rows across columns.

The fex shown below produces this:

        GREEN  ORANGE  PURPLE 
LOW     .      STEVE   . 
LOW     .      .       SALLY 
LOW     .      .       HARRIET 
LOW     COSMO  .       . 
MEDIUM .       REG     . 
MEDIUM FRANZ   .       . 
HIGH   .       JACK    . 
HIGH   .       .       JILL 
HIGH   SUZIE   .       . 
HIGH   MARY    .       . 

I'd like this:

       GREEN   ORANGE  PURPLE 
LOW    COSMO   STEVE   SALLY 
LOW    .       .       HARRIET 
MEDIUM FRANZ   REG     . 
HIGH   SUZIE   JACK    JILL 
HIGH   MARY    .       . 


I can't make this fex do it:

SET ASNAMES     = ON
SET HOLDLIST    = PRINTONLY
SET HOLDFORMAT  = ALPHA
SET PAGE        = NOLEAD
-RUN

-*-- Create the master for PEOPLE ------------------------------------
APP FI DATAMAST DISK PEOPLE.MAS
-RUN

-WRITE DATAMAST FILE=PEOPLE, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=RISK  , ALIAS=E01, USAGE=A8, ACTUAL=A08, $
-WRITE DATAMAST   FIELD=COLOUR, ALIAS=E02, USAGE=A8, ACTUAL=A08, $
-WRITE DATAMAST   FIELD=NAME  , ALIAS=E03, USAGE=A8, ACTUAL=A08, $

-*-- Create the data file for PEOPLE ---------------------------------
FILEDEF PEOPLE DISK PEOPLE.FTM
-RUN
-*            123456781234567812345678
-WRITE PEOPLE HIGH    ORANGE  JACK
-WRITE PEOPLE HIGH    PURPLE  JILL
-WRITE PEOPLE HIGH    GREEN   SUZIE
-WRITE PEOPLE HIGH    GREEN   MARY
-WRITE PEOPLE MEDIUM  ORANGE  REG
-WRITE PEOPLE MEDIUM  GREEN   FRANZ
-WRITE PEOPLE LOW     ORANGE  STEVE
-WRITE PEOPLE LOW     PURPLE  SALLY
-WRITE PEOPLE LOW     PURPLE  HARRIET
-WRITE PEOPLE LOW     GREEN   COSMO

-*-- Create the Matrix Report for PEOPLE -----------------------------
TABLE FILE PEOPLE
PRINT
NAME
ACROSS COLOUR AS ''
BY RISK AS ''
ROWS
'LOW' OVER
'MEDIUM' OVER
'HIGH'
END
-RUN

This code can be copied and run.

Thank you,

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Francis

I was thinking of SUM in stead of PRINT, but that would give you one row per RISK.

So maybe put a counter in the selection (I hit the post before I finish my mail) and give that an extra byfield with the risk. So if you have 2 names in one risk-colour combination the second name has 2.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
  
DEFINE FILE PEOPLE
 KEY/A16=RISK | COLOUR ;
END
-* generate LIST=relative line number of name, within Colour (column) and Risk value (block of rows).
TABLE FILE PEOPLE
  LIST NAME RISK COLOUR
    BY KEY NOPRINT
    BY NAME NOPRINT
  ON TABLE HOLD
END

TABLE FILE HOLD
  WRITE NAME
  ACROSS COLOUR AS ''
  BY RISK
    ROWS 'LOW' OVER 'MEDIUM' OVER 'HIGH'
  BY LIST NOPRINT
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Yes Jack, this is exactly what I said.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
just saw Frank's suggestion as I started to post so i haven't looked at that. The problem is that you want to PRINT to get all names, but you want to SUM so that you combine rows as much as possible. But it obviously can't be both. Here's something that's close:
TABLE FILE PEOPLE
SUM NAME
ACROSS COLOUR AS ''
ACROSS NAME NOPRINT
BY RISK AS ''
ROWS
'LOW' OVER
'MEDIUM' OVER
'HIGH'
END

Which gets you
        GREEN      ORANGE     PURPLE 
LOW     COSMO           STEVE HARRIET   SALLY 
MEDIUM  FRANZ       REG         
HIGH    MARY SUZIE JACK       JILL   


One line per risk, but with all names associated with each color (sorry - colour) Smiler


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
quote:
Yes Jack, this is exactly what I said.


Now that I re-read your response, true.

What I added was the intermediate HOLD (which is essential to get those line offsets, before you can proceed with the cross-tabs) -- and letting LIST BY do the calculation.


I tried combining into a single request...
TABLE FILE PEOPLE
WRITE NAME
OVER      RISK
OVER      COLOUR
OVER      COMPUTE LIST/I5= IF RISK EQ LAST RISK AND COLOUR EQ LAST COLOUR THEN LAST LIST + 1 ELSE 1;
  BY RISK
    ROWS 'LOW' OVER 'MEDIUM' OVER 'HIGH'
  BY TOTAL LIST NOPRINT
  ACROSS COLOUR AS ''
END

...figuring the ACROSS is treated as BY through the point where the Compute happens, before it is pivoted from vertical to horizontal ordering.
But no dice:

(FOC32415) BY TOTAL and ACROSS: BY TOTAL key cannot be COMPUTEd


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
Jack - your first solution works for me. I was hoping to do it without an intermediary hold file, but this is good.

Thanks very much.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
And thank you Frank.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
For what it's worth, here is a way to do it in one pass. It is ugly but it works. However, Jacks suggestion is the same concept and is a lot less code.

-*-- Create the Matrix Report for PEOPLE -----------------------------
DEFINE FILE PEOPLE
RC/A16=RISK || COLOUR;
GL/I1=IF (RISK EQ 'LOW') AND (COLOUR EQ 'GREEN') THEN LAST GL+1 ELSE GL;
GM/I1=IF (RISK EQ 'MEDIUM') AND (COLOUR EQ 'GREEN') THEN LAST GM+1 ELSE GM;
GH/I1=IF (RISK EQ 'HIGH') AND (COLOUR EQ 'GREEN') THEN LAST GH+1 ELSE GH;
OL/I1=IF (RISK EQ 'LOW') AND (COLOUR EQ 'ORANGE') THEN LAST OL+1 ELSE OL;
OM/I1=IF (RISK EQ 'MEDIUM') AND (COLOUR EQ 'ORANGE') THEN LAST OM+1 ELSE OM;
OH/I1=IF (RISK EQ 'HIGH') AND (COLOUR EQ 'ORANGE') THEN LAST OH+1 ELSE OH;
PL/I1=IF (RISK EQ 'LOW') AND (COLOUR EQ 'PURPLE') THEN LAST PL+1 ELSE PL;
PM/I1=IF (RISK EQ 'MEDIUM') AND (COLOUR EQ 'PURPLE') THEN LAST PM+1 ELSE PM;
PH/I1=IF (RISK EQ 'HIGH') AND (COLOUR EQ 'PURPLE') THEN LAST PH+1 ELSE PH;
LINENUMB/I1=
IF (RC EQ 'LOWGREEN') THEN GL ELSE
IF (RC EQ 'LOWORANGE') THEN OL ELSE
IF (RC EQ 'LOWPURPLE') THEN PL ELSE
IF (RC EQ 'MEDIUMGREEN') THEN GM ELSE
IF (RC EQ 'MEDIUMORANGE') THEN OM ELSE
IF (RC EQ 'MEDIUMPURPLE') THEN PM ELSE
IF (RC EQ 'HIGHGREEN') THEN GH ELSE
IF (RC EQ 'HIGHORANGE') THEN OH ELSE
IF (RC EQ 'HIGHPURPLE') THEN PH ELSE 0;
END
TABLE FILE PEOPLE
SUM NAME
ACROSS COLOUR AS ''
BY RISK AS ''
ROWS
'LOW' OVER
'MEDIUM' OVER
'HIGH'
BY LINENUMB NOPRINT
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
There's a subtle diff, which can matter in real life:

Mickey's one-step code assigns line numbers by means of "Define", which ties the values to the input order [which, admitedly, satisfies F.M. original quest].

Assigning the line numbers in a separate step allows "compute"-type processing to take hold, giving control over the output order (of names within a risk+colour grouping), regardless of the input order.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
The main difference between Jack's and Mickey's solutions is that in Mickey's, the dimension values have to be known, for the DEFINE to work.

Thanks and drinks all around.

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] Matrix Report - How to sum data in rows?

Copyright © 1996-2020 Information Builders