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.
I'd like to have two columns in my report: one that sums sales for ENGLAND and one that sums sales for JAPAN. How would I do that?This message has been edited. Last edited by: Kerry,
Read up on the ACROSS statement. Easiest way to turn rows into columns
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, 2007
That works great ( I just adjusted the column numbers for my report).
The output I get is:
COUNTRY ENGLAND ITALY DIFF SEATS ------------------------------------- 2 13,978 45,140 31,162 4 17,850 5,925 -11,925 5 13,491 . -13,491
I will be shutting off COUNTRY by doing an AS '' for the ACROSS. Is there any way to do the following: 1) Rename the ENGLAND and ITALY columns? 2) Get the ENGLAND and ITALY titles to be on the same line as SEATS?
After the dif column for the sum of RCOST by SEATS Across countries, I'd like SUM RCOST for the same countries across BY CAR within each BY SEAT.
For example, for the 2 SEATS, I'd like to then see which cars have 2 SEATS in England and Italy and the SUM of their RCOST - is there any way to do that in the same report?
DEFINE FILE CAR
ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
JAPAN /D5=RCOST * (COUNTRY IS 'JAPAN ');
DIFF /D5=ENGLAND-JAPAN;
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN DIFF
BY SEATS
ON TABLE SUMMARIZE
END
For the second, I only got so far:
Using multiple verbs you can get a correct total RCOST (over all countries), while limiting the scope of detail rows produced by the second verb to selected countries by means of IF/WHERE TOTAL:
DEFINE FILE CAR
ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
JAPAN /D5=RCOST * (COUNTRY IS 'JAPAN ');
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN COMPUTE DIFF/D5=ENGLAND-JAPAN;
BY SEATS
SUM RCOST
BY SEATS
BY COUNTRY
BY CAR
IF TOTAL COUNTRY EQ ENGLAND OR JAPAN
-*ON COUNTRY SUMMARIZE
END
But if you add subtotals, you get phantom subtotal lines for the other countries.
And if you change BY CAR to ACROSS CAR, even w/o totaling other things break -- IF TOTAL and ACROSS do not seem to mix well.This message has been edited. Last edited by: j.gross,
- Jack Gross WF through 8.1.05
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005
Perhaps I wasn't clear with what I was trying to do but with your first suggestion I was easily able to do it as follows:
DEFINE FILE CAR ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND'); JAPAN /D5=RCOST * (COUNTRY IS 'JAPAN '); DIFF/D5=ENGLAND-JAPAN; END TABLE FILE CAR SUM ENGLAND JAPAN DIFF BY SEATS SUM ENGLAND JAPAN DIFF BY SEATS BY CAR END