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     Coding Report Headings Question...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Coding Report Headings Question...
 Login/Join
 
Master
posted
I’ve created a focexec to display a report of fees earned in a calendar year. In this case,
I want to query the database for two years at once.

In the first table request so I coded:
WHERE FACYY EQ '2004' OR '2005'
ON TABLE HOLD AS HOLDMGA FORMAT FOCUS
to load all the data to a hold file.

In the report request I coded

TABLE FILE FEES
SUM XX/C YTD/C
ACROSS BLAH_SORT NOPRINT
ACROSS BLAHBLAH/C AS ''
BY YEAR NOPRINT
BY BLAH_SORT NOPRINT
BY HELLODOLLY AS FEE
ON YEAR PAGE-BREAK SUMMARIZE

FOOTING BOTTOM
. . . .
END
-RUN

I am getting the expected output like:

Little Bopper Corporation
Calendar Year Fee Report For 2004

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total Year 2004 1,234

Little Bopper Corporation
Calendar Year Fee Report For 2005

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total Year 2005 4,321


Little Bopper Corporation
Calendar Year Fee Report For ????

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total 5,555

I am trying to get a date range (2004-2005)
to show on the Final Page of the Report (to replace
the ???).

Maybe I could do this with dialogue manager,
But I wondered if anyone has played with
Mainframe FOCUS to get the date(s) to show when
reporting on multiple sort values?

Suggestions anyone?

Thanks!
Idea


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
Expert
posted Hide Post
Tom,

I can't think of a method using just a single TABLE request, but you could perform the first part breaking on the year with the heading of each page to show the year value, but do not perform the summary. Then have a second request to provide the summary report giving the totals with a fixed heading showing for all years.

T

For example -

DEFINE FILE GGSALES
  YEAR/YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR NOPRINT PAGE-BREAK
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"</1 Dollar detail by Region for <YEAR </1"
ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END
-RUN
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"</1 Dollar detail by Region for <MIN.YEAR to <MAX.YEAR </1"
ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END
-RUN



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
Guru
posted Hide Post
Tom,
If I understand correctly what you're looking for, this should work:

ON TABLE SUMMARIZE AS 'TOTAL 2004-2005'

or

ON TABLE SUMMARIZE AS 'TOTAL &YEAR1 - &YEAR2'

Greg



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
 
Posts: 274 | Location: Boston/New England | Registered: February 12, 2006Report This Post
Master
posted Hide Post
quote:
BY YEAR NOPRINT PAGE-BREAK



Gregv,

I tried your method and I get an error for using the AS statement.

Tomsweb


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
Master
posted Hide Post
quote:
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR NOPRINT PAGE-BREAK
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END



Tony,

In you upper code snippet I have added
...
BY YEAR NOPRINT
BY YEAR NOPRINT PAGE-BREAK
This give me
* TOTAL YEAR 2004 999,999
and
* TOTAL YEAR 2005 999,999
on the totals page for the individual year.
and the end I get a Grand Total ....

In the lower code snippet I get the 2 year (2004-_) totals for each record, but no Grand Total.

Thanks,
Tomsweb


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
Expert
posted Hide Post
om,

Apologies, firstly you do not need the ON YEAR PAGE-BREAK in the second report as there is no supporting BY YEAR.

In your statement above you do not require both statements -
quote:
BY YEAR NOPRINT
BY YEAR NOPRINT PAGE-BREAK
Just the second one will do.

Lastly, there is no summarize on the example I gave you so you would have to add one or do you have an ON TABLE NOTOTAL ?

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
Guru
posted Hide Post
Tom,
Here's a better example of what I was thinking.

-SET &YEAR1='1996';
-SET &YEAR2='1997';
DEFINE FILE GGSALES
YEAR/YY=DATE;
END
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR PAGE-BREAK SUMMARIZE
BY REGION
BY ST
WHERE YEAR FROM '&YEAR1.EVAL' TO '&YEAR2.EVAL'
HEADING
"YEAR ON TABLE SUMMARIZE AS 'TOTAL &YEAR1.EVAL - &YEAR2.EVAL'
END



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
 
Posts: 274 | Location: Boston/New England | Registered: February 12, 2006Report 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     Coding Report Headings Question...

Copyright © 1996-2020 Information Builders