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] Across Sort Field Row Totals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Across Sort Field Row Totals
 Login/Join
 
Gold member
posted
Hi,

I'm facing a problem in brining the across sort field value row totals at the end of the table as a separate columns for each higher order across sort field values.

As per the below code I need a separate column for each REGION(midwest,northeast,southeast) at the end of the table. Here separate column represens (SUM of UNITS across
ACROSS REGION,ACROSS STATE) ROW TOTAL basically with in the REGION,STATE and should display at the end of the table as a separate column each REGION(midwest,northeast,southeast)

Once I get the total/sum for each (midwest,northeast,southeast) I need one more column that gives me region wise average column all these should display only at the end of the table. I used FML but somewhere while using ACROSS AND COMPUTE to call the row totals getting error.

The mockup data may help for your reference. I know this will not come in a tabular format but somehow I want to project it

midwest norteash
IL NJ CT MA midwest northeast
coffee 5 6 7 8 11 15
food 1 2 3 4 3 7


I have given the code for reference below
  
  
SET PAGE-NUM=OFF
TABLE FILE GGSALES
 SUM UNITS
   BY CATEGORY AS  ''
     ACROSS REGION AS ''
      ACROSS STATE AS ''
END



Thanks in advance

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
 
Posts: 78 | Registered: January 07, 2008Report This Post
Expert
posted Hide Post
As someone once said: "If you can say it in English, I can write it in WebFOCUS". Consider the following code (Column Total thrown in as an extra freebie):
SET PAGE = OFF
TABLE FILE GGSALES
HEADING
"SUM of UNITS by CATEGORY ACROSS REGION and STATE"
"With ROW-TOTALs and COLUMN-TOTALs</1>"
 SUM UNITS/D12C AS ''
   BY CATEGORY AS  ''
   ACROSS REGION AS  ''
   ACROSS ST AS  ''
ON TABLE ROW-TOTAL AS 'ROW TOTAL'
ON TABLE COLUMN-TOTAL AS 'COLUMN TOTAL'
END


SUM of UNITS by CATEGORY ACROSS REGION and STATE 
With ROW-TOTALs and COLUMN-TOTALs 
  
RESULTS:
             Midwest                 Northeast               Southeast               West 
             IL      MO      TX      CT      MA      NY      FL      GA      TN      CA      WA      ROW TOTAL 
Coffee       109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176 108,776 235,583 121,180 1,376,266 
Food         118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284 114,221 222,711 117,523 1,384,845 
Gifts         79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823  71,650 152,276  82,766   927,880 
COLUMN TOTAL 307,581 297,727 299,737 302,440 301,909 312,326 310,302 330,283 294,647 610,570 321,469 3,688,991 




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
Hi Doug,

Thanks! for your quick response. But, I'm not looking for a normal row total which you have produced for coffee category(1,376,266) and so on and so forth.

as per the data I need to show three columns(Midwest,Northeast,Southeast) for Coffee row which should come at the end of the table. For instance in the output the "ROW TOTAL" column is shown at the end similarly three columns should show at the end of the table.

For COFFEE ROW :

The column1 "TotMidwest" at the end of the table nothing but ---> SUM(IL,MO,TX unit values) i.e.. 332,777
The column2 "TotNortheast" at the end of the table nothing but---> SUM(CT,MA,NY unit values) i.e.. 335,778
The column3 "TotSoutheast" at the end of the table nothing but ---> SUM(FL,GA,TN unit values) i.e.. 350,948

I'm looking the same for "Food" and "Gifts" rows.

I put the data in CODE delimiters. Not sure whether it will show in tabular format or not

  
             Midwest                 Northeast               Southeast          
             IL      MO      TX      CT      MA      NY      FL      GA       TotMidWest TotNortEast TotSeast
Coffee       109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176  332,777     335778      350,948 
Food         118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284  9999999     9999999     9999999
Gifts        79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823   9999999     9999999     9999999 



Just to add more thing I want to show the output in EXL2k format. I know it is possible holding in a HTMTABLE format and using HTMLFORMAT=XLS we can show the row totals side by side. I want to show using EXL2K format strictly not through HTMLFORMAT=XLS as you all aware of HTMLFORMAT=XLS will not allow to create multiple tab data excel out put and tab title issues.



Thanks!

This message has been edited. Last edited by: subbu,


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
 
Posts: 78 | Registered: January 07, 2008Report This Post
Gold member
posted Hide Post
Hi all,

Finally I got what I want from my friend. Please see the below code.

I still have one more question To bring the row totals at the end I have concatenated with 'Z1XXX'.

if you see the output the row total will show 'Z1TotalMid West' ......

I want to hide the first two letters and I dont want to remove the prefixed letter. Any ideas

I should show me

' TotalMid West' ...... Instead of 'Z1TotalMid West' ...... Smiler



  
SET PAGE-NUM=OFF

DEFINE FILE  GGSALES
REGION1/A100 = IF REGION EQ 'Midwest' THEN 'Z1Total MidWest' ELSE IF REGION EQ 'Northeast' THEN 'Z2Total NorthEast' ELSE IF REGION EQ 'Southeast' THEN 'Z3Total SouthEast' ELSE 'Z4Total West';
HLDRGN/A100  = REGION;
END

TABLE FILE GGSALES
SUM UNITS 
-*COMPUTE ST1/A02 = IF REGION EQ 'Midwest' THEN 'Z2' ELSE IF REGION EQ 'Northeast' THEN 'Z3' ELSE IF REGION EQ 'Southeast' THEN 'Z4' ELSE 'Z5';
COMPUTE ST1/A02 = '';
BY CATEGORY
BY REGION1
ON TABLE HOLD AS HLD2
END

-RUN
TABLE FILE GGSALES
 SUM UNITS
 BY CATEGORY 
 BY HLDRGN 
 BY ST 
ON TABLE HOLD AS HLD1
END
?FF HLD1
?FF HLD2

TABLE FILE HLD1
PRINT *
ON TABLE HOLD AS MAIN
MORE 
FILE HLD2
END

TABLE FILE MAIN
SUM UNITS
BY CATEGORY AS ''
ACROSS HLDRGN AS ''
ACROSS ST AS ''

END




Thanks for all your help


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
 
Posts: 78 | Registered: January 07, 2008Report This Post
Master
posted Hide Post
REGION1/A100 = IF REGION EQ 'Midwest' THEN 'Total MidWest' ELSE IF REGION EQ 'Northeast' THEN 'Total NorthEast' ELSE IF REGION EQ 'Southeast' THEN 'Total SouthEast' ELSE 'Total West';


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
The easiest and simplest solution to this problem is to use ACROSS-TOTAL. Unfortunately, the total columns are placed at the end of each ACROSS group (REGION in this case). And there doesn't appear to be an easy way to move the total columns to the end of the report.

TABLE FILE GGSALES
 SUM UNITS AS ''
 BY CATEGORY AS ''
 ACROSS REGION AS ''
 ACROSS ST AS ''
 ACROSS-TOTAL AS 'Total'
 ON TABLE SET PAGE OFF
-* ON TABLE PCHOLD FORMAT EXL2K
END


The following code will place the total columns at the end of the report. The first TABLE FILE counts the number of sort fields (regions and states). These counts are used later to determine which NOPRINT columns contain the required totals. This approach allows the report to be more dynamic so the number of columns can vary without causing an error. However, the code is far more complicated than the ACROSS-TOTAL code above. Perhaps someone else can come up with a better solution.

SET HOLDLIST = PRINTONLY
SET ASNAMES = ON
-*
TABLE FILE GGSALES
 COUNT DST.ST/I5
 BY REGION
 ON TABLE SAVE AS REGIONS
END
-RUN
-SET &XREGCOUNT = &LINES ;
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO &XREGCOUNT
-READ REGIONS &XREGION.&I.11 &STCOUNT.&I.5
-ENDREPEAT1
-*
TABLE FILE GGSALES
 SUM UNITS/D10C AS 'TOTUNITS'
 BY REGION
 BY CATEGORY
 SUM UNITS/D10C
 BY REGION
 BY CATEGORY
 BY ST
 ON TABLE HOLD AS SUMDATA
END
-*
TABLE FILE SUMDATA
 SUM TOTUNITS NOPRINT
     UNITS AS ''
 BY CATEGORY AS ''
 ACROSS REGION AS ''
 ACROSS ST AS ''
-SET &K = 1 ;
-REPEAT ENDREPEAT2 FOR &I FROM 1 TO &XREGCOUNT
 COMPUTE TOTREGION&I/D10C = C&K ; AS 'Total &XREGION.&I'
-SET &K = &K + ( &STCOUNT.&I * 2 ) ;
-ENDREPEAT2
 ON TABLE SET PAGE OFF
-* ON TABLE PCHOLD FORMAT EXL2K
END


EDIT: Changed &REGION to &XREGION to prevent interpretation as the registered trademark symbol ®.
Also added these two lines
SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
To make Dan's sample work, you might want to add the following two lines to the top of the fex -

SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

and also change all ® to &REG

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
-SET &ECHO=ALL;

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

DEFINE FILE GGSALES
STATE/A05 = ' ' | ST;
END
-RUN

-* Detail data
TABLE FILE GGSALES
SUM UNITS/D10C
BY REGION
BY CATEGORY
BY STATE
ON TABLE HOLD AS DETDATA
END
-RUN

-* Total data
TABLE FILE GGSALES
SUM UNITS/D10C
COMPUTE STATE/A05 = 'TOTAL';
BY REGION
BY CATEGORY
ON TABLE HOLD AS SUMDATA
END
-RUN

TABLE FILE DETDATA
SUM
UNITS AS ''
BY CATEGORY AS ''
ACROSS REGION AS ''
ACROSS STATE AS ''

-* ON TABLE PCHOLD FORMAT EXL2K

MORE
FILE SUMDATA
END
-RUN


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
Guru
posted Hide Post
Given the solutions here, it is still worth pretty up the headings:

-SET &ECHO=ALL;

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


-* Detail data
TABLE FILE GGSALES
SUM UNIT 
COMPUTE REG/A30 = ' '|REGION;
COMPUTE STA/A30 = ST; 
BY REGION
BY CATEGORY
BY ST 
ON TABLE HOLD AS DETDATA
END
-RUN

-* Total data
TABLE FILE GGSALES
SUM UNIT
COMPUTE REG/A30='Total';
COMPUTE STA/A30 = REGION;
BY REGION
BY CATEGORY
ON TABLE HOLD AS SUMDATA 
END
-RUN

TABLE FILE DETDATA
SUM
UNIT AS ''
BY CATEGORY AS ''
ACROSS REG AS ' '
ACROSS STA AS ''

-*ON TABLE PCHOLD FORMAT EXL2K

MORE
FILE SUMDATA
END
-RUN 


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
Tony, thanks for spotting my errors. I set HOLDLIST=PRINTONLY and ASNAMES=ON by default and sometimes forget that fact. The only way I could prevent &REGION from displaying as ®ION was to replace the ampersand with the HTML code for ampersand (& + amp; ). So I decided to change &REGION to &XREGION to avoid the issue. I have now made these changes to my original post. It's all a mute point anyway, because Hua's post above is a much better solution.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Hi Dan,

Thanks for updating the solution. I have used ur solution, One question is can we create a dummy column "TOTAL" before the table like how you have created compute columns at the end.

I have given mockup data below

                   Midwest                 Northeast               Southeast          
 STORE             IL      MO      TX      CT      MA      NY      FL      GA       TotMidWest TotNortEast
 TOTAL   Coffee    109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176  332,777     335778   
 TOTAL   Food      118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284  9999999     9999999     
 TOTAL   Gifts     79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823   9999999     9999999     



Thanks! in advance


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
 
Posts: 78 | Registered: January 07, 2008Report This Post
Expert
posted Hide Post
Dan,

I too, have several customisations in my profile(s) but I also have a vanilla install that I try to test things in before I post - I don't always get the time now-a-days though!

As for the &REG, I often forget the old ampersand problem and then have to edit my post afterwards to put it right - I still forget though (no comments from the POW team on my age thank you!).

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
I will use Hua's code as an example because I believe it is a better technique than the one I posted. But the approach is the same regardless which piece of code you use: add a BY column to the output.

SET HOLDLIST  = PRINTONLY
SET ASNAMES   = ON
SET PAGE      = NOLEAD
SET BYDISPLAY = ON

-* Detail data
TABLE FILE GGSALES
 SUM UNITS
 COMPUTE REG/A15  = ' ' | REGION ;
 COMPUTE STA/A15  = ST ; 
 COMPUTE STORE/A5 = 'TOTAL';
 BY REGION
 BY CATEGORY
 BY ST 
 ON TABLE HOLD AS DETDATA
END

-* Total data
TABLE FILE GGSALES
 SUM UNITS
 COMPUTE REG/A15  = 'Total';
 COMPUTE STA/A15  = REGION ;
 COMPUTE STORE/A5 = 'TOTAL';
 BY REGION
 BY CATEGORY
 ON TABLE HOLD AS SUMDATA 
END

-* Report
TABLE FILE DETDATA
 SUM UNITS/D10C AS ''
 BY STORE       AS ''
 BY CATEGORY    AS ''
 ACROSS REG     AS ''
 ACROSS STA     AS ''
-*ON TABLE PCHOLD FORMAT EXL2K
MORE
 FILE SUMDATA
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
Thanks Dan.

Francis brought the chicken, I just added the sauce. Smiler

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
Tasty!


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
Gold member
posted Hide Post
Hi Dan,

Thank you so much. It helped me alot and once again I would like to say thanks to all who gave the opinions/suggestions for my issue.

Best Regards
 
Posts: 78 | Registered: January 07, 2008Report 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] Across Sort Field Row Totals

Copyright © 1996-2020 Information Builders