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     NOPRINT a field in an across but PRINT the field in a column-total or summarize

Read-Only Read-Only Topic
Go
Search
Notify
Tools
NOPRINT a field in an across but PRINT the field in a column-total or summarize
 Login/Join
 
Member
posted
This is an example of something that I have now :

JOIN CLEAR *

TABLE FILE CAR
SUM
COMPUTE TOT_DEALER_COST/D15.2MC = DEALER_COST; NOPRINT
BY COUNTRY
BY CAR
SUM DEALER_COST
OVER COMPUTE DEAL_PCT/D7.2% = (DEALER_COST / TOT_DEALER_COST ) * 100;
ACROSS MODEL
ACROSS-TOTAL
BY COUNTRY
BY CAR
ON COUNTRY RECOMPUTE AND COLUMN-TOTAL
WHERE MODEL CONTAINS 'AUTO';
WHERE COUNTRY CONTAINS 'GERMANY' OR 'JAPAN';
END

What I want to end up with is the DEAL_PCT field NOT included in the detail, but DEAL_PCT IS included in the COLUMN-TOTALs for COUNTRY and the report total. And I want the ACROSS-TOTAL to include the DEALER_COST but NOT the DEAL_PCT.

What I want should end up looking something like this :
Model
Country Car 2door 4 door Total
Japan DATSUN $100 $200 $300
TOYOTA $500 $300 $800
Total Japan $600 $500 $1100
54.55% 45.45%
Germay AUDI $200 $400 $600
BMS $1000 $4000 $5000
Total Germancy $1200 $4400 $5600
21.43% $78.57

Total $1800 $4900 $6700
26.87% 73.13%

Any suggestions ?

I am using webfocus 7.1 for windows/oracle.


WF 8.2.4 Windows
Mainframe
Oracle/DB2/SQL Server
App Studio
Tomcat/Servlet
 
Posts: 11 | Registered: May 23, 2004Report This Post
Expert
posted Hide Post
karen,

The toolbar has a search function:

Click it, type in percent total, click GO

2 pages of examples

As Prairie has reminded all newcomers to the Forum(this was her designated week), please do the following:

Karen...welcome to the Forum. Please go in and update your signature with which version you are running. Thanks

** Update signature
1) Logon to Focal Point and go into your complete profile.
2) Scroll down the page until you see the “Signature” field.
3) Fill in the signature you want to use. Here is a sample to model:

Prod: WebFOCUS 5.2.3 on Win 2K/IIS 6/ISAPI Mode
Test: WebFOCUS 7.1 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode

4) Once all changes are made/added, scroll down to the bottom of the page and click on the Submit button.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
Well Thanks for taking over Tom...you can have next week. Wink


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
Follow you anytime!!!! Sweating


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
Do a search on RECAP


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
As indicated RECAP and placing information in a subfoot.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
I've looked at all of the places that each of you have suggested and tried all of the examples and I still don't see a good solution. If I missed the example would someone please point me to it ?

I rewrote the original program to try to make it more maintainable. Before it had defines for each across value hard coded creating each column. It did not do an across. Therefore whenever a new code was added the program had to be modified. I wanted to have the program not have to be touched whenever a new code was added.
The original sample code that I posted gives me all of the correct data I just don't want to show the PCT in the body of the report, I only want it to show in the summary lines. I have that working from the stand point that I made the PCT field color white ( on white background) in the body of the report, but that is really hokey and ultimately shows a blank line. Plus if the user selects to output this report to EXCEL then the data is really still there. What I really want is to NOPRINT a field in the detail and ON sort RECOMPUTE and SUMMARIZE field1 and field2 (field2 being the NOPRINTed field in the detail). It seems that other people need this functionality too. How about a new feature ?


WF 8.2.4 Windows
Mainframe
Oracle/DB2/SQL Server
App Studio
Tomcat/Servlet
 
Posts: 11 | Registered: May 23, 2004Report This Post
Virtuoso
posted Hide Post
Okay. It took some time for me to figure this out but I think I have a viable solution for you. It is not pretty but it works. There should be an easier way to do this but for now this is what I came up with.

In the following code I basically created the TOTAL and PERCENT rows of data in HOLD files and then added them back into the raw data as rows of data. This makes it possible to treat them as normal rows of data instead of summary lines which seems to be the problem.

Run the code below and see how it works.

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

DEFINE FILE CAR
NEWSALES/D12=SALES;
END

-* Extract the raw data

TABLE FILE CAR
SUM NEWSALES
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE BODYTYPE NE 'CONVERTIBLE' OR 'HARDTOP'
ON TABLE HOLD AS 'RAWDATA'
END

-* Create summary records for the total sales

TABLE FILE RAWDATA
SUM NEWSALES
COMPUTE CAR/A16='Total';
COMPUTE SORTCNTL/I1=2;
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS 'COUNTRYTOT'
END

-* Create summary records for the percent sales

TABLE FILE RAWDATA
SUM PCT.NEWSALES AS 'NEWSALES' WITHIN COUNTRY
COMPUTE CAR/A16='Percent';
COMPUTE SORTCNTL/I1=3;
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS 'COUNTRYPCT'
END

DEFINE FILE RAWDATA
SORTCNTL/I1=1;
END

-* Combine all the data into one file

TABLE FILE RAWDATA
SUM NEWSALES
BY COUNTRY
BY SORTCNTL
BY CAR
BY BODYTYPE
ON TABLE HOLD AS 'FINALDATA'
MORE
FILE COUNTRYTOT
MORE
FILE COUNTRYPCT
END

-* Reformat the NEWSALES field dynamicly

DEFINE FILE FINALDATA
THEFRMT/A8=DECODE SORTCNTL(1 'D12' 2 'D12' 3 'F6.2%');
END

-* Generate the final output

TABLE FILE FINALDATA
SUM NEWSALES/THEFRMT
BY COUNTRY
BY SORTCNTL NOPRINT
BY CAR
ACROSS BODYTYPE ACROSS-TOTAL
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report 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     NOPRINT a field in an across but PRINT the field in a column-total or summarize

Copyright © 1996-2020 Information Builders