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     [CLOSED]ON TABLE RECOMPUTE AS 'TOTAL'

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]ON TABLE RECOMPUTE AS 'TOTAL'
 Login/Join
 
Platinum Member
posted
When using the
 ON TABLE RECOMPUTE AS 'TOTAL' 
is there a way to reference these totals in a report?? Or would I have to do a RECOMPUTE into a SUBFOOT to access these totals?? I am using the RECOMPUTE due to Percenatges within the output of my report.

ERINP

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


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Expert
posted Hide Post
TYPE=GRANDTOTAL, STYLE=BOLD, COLOR=BLUE,$


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Tom,
That gets me access to the Styling for the GRANDTOTALs I am after the values within the GRANDTOTALs.
I have a report that contains Percentages for 5 columns, The percentage value within each row is used to set the width for an image in my display, the RECOMPUTE totals these values as a GRANDTOTAL and what I want to do is use the recomputed totals to set these same image widths for the TOTAL at the bottom of my report but I am scratching my head as how to get the TOTAL values for this purpose.
 
COL1
12.50%
50.00%
50.00%
25.00%
66.67%
100.00%

45.24% AS TOTAL
 

How do I access/reference the value 45.24% in my code? I can't use TOT.COL1.Field1 because it is a RECOMPUTE on percentages....or can I?


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Expert
posted Hide Post
Use RECAP and SUBFOOT instead of ON TABLE RECOMPUTE.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Tom,
I will give the ol RECAP and SUBFOOT a shot...

Thanks for the input,

ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Platinum Member
posted Hide Post
Tom,
I got the RECAP and a SUBFOOT to work. When I:
 ON TABLE HOLD AS HOLD1 
How can I reference the RECAP field??
 ON TABLE HOLD1
PRINT
recap_Field from HOLD1 


Any ideas??

Thanks,
ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Virtuoso
posted Hide Post
ERINP, you use RECAP to calculate the totals and then SUBFOOT to display them.

TABLE FILE blah
PRINT ....
-* Calculate totals by COUNTRY 
ON TABLE RECAP
TOT01/D12.2 = something * 0.25 / 12;
TOT02/D12.2 = something_else - TOT01;
...
-* Display your "totals"
ON TABLE SUBFOOT
"Totals:<TOT01<TOT02"
END


However, based on your explanation it seems to me that what you need is to calculate those totals so you can use them later in a different request. If that's the case, you can produce them with regular COMPUTE fields, HOLD those results, -READ them in &variables and refer to them in your next request.

TABLE FILE blah
SUM
COMPUTE TOT01/A20 = FTOA(<your_expression_here>, '(D20.2)');
COMPUTE TOT02/A20 = FTOA(<next_expression_here>, '(D20.2)');
...
ON TABLE HOLD AS HPCTS FORMAT ALPHA
END
-RUN
-READ HPCTS &TOT01.A20. &TOT02.A20.

TABLE FILE blah
PRINT/SUM ...
ON TABLE SET STYLE *
TYPE=REPORT, ...., WIDTH=&TOT01, ..., $
TYPE=DATA, ...., WIDTH=&TOT02, ..., $
ENDSTYLE
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
njsden,
I do need them in a later request. I have to use the RECAP in the HOLD file because it is a weighted percentage and not necessarily a subtotal. Due to the location of the RECAP code I do not have the ability to create another variable that stores the data from the RECAP in the hold file because it is after the BY, WHERE, etc in the code. I was wondering if I could do -RUN -READ but the values are not available within the HOLD file itself.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Virtuoso
posted Hide Post
Could you make up some code using CAR or GGSALES with the exact calculations/RECAPs you're using along with the expected results?

If RECAP can do it, I'm *almost* sure we can make it happen with a combination of DEFINE/COMPUTE's although not necessarily in the same TABLE FILE request but hey! that's the wonder of HOLD files.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
If worse comes to worst, you can save the report in WP format (this will include your report data, including headings, subheadings, etc.) and then use Dialogue Manager to loop through it parsing each line to get the values you need in &variables; you can then use those in the next TABLE FILE request.

I'm still leaning towards a simple TABLE FILE/SUM COMPUTE/HOLD approach. Hopefully that'll be feasible.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Look into the PUTDDREC function: Using Functions > System Functions > PUTDDREC: Writing a Character String as a Record in a Sequential File

"The PUTDDREC function writes a character string as a record in a sequential file. The file must be identified with a FILEDEF command."

It might be possible for you to write the values into a file, then create a Master to read these values - just a thought based on this function I discovered, which might be v7.7 and newer...


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
Interesting ...

FILEDEF MYFILE DISK myfile.ftm
-RUN

TABLE FILE CAR
SUM RETAIL_COST AND DEALER_COST
BY COUNTRY
ON TABLE RECAP
MARGIN/A10 = FTOA((1 - (DEALER_COST / RETAIL_COST)) * 100, '(D8.2%)', MARGIN);
DUMMY/I2 = PUTDDREC('MYFILE',6,MARGIN,10,DUMMY);
ON TABLE SUBFOOT
"Margin: <MARGIN"
END
-RUN

-READ MYFILE &MARGIN.A10.
-TYPE Margin: &MARGIN

TABLE FILE CAR
SUM CAR NOPRINT
WHERE RECORDLIMIT EQ 1
HEADING 
"Margin from previous report: &MARGIN"
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
New Features 7.1.3 > Reporting Language Enhancements:

quote:
This feature is available in Version 7 Release 1.3 and higher.

The PUTDDREC function writes a character string as a record in a flat file. The file must be identified with a FILEDEF command ...


This function has been available for quite a while. Truly a hidden gem. Thanks for bringing it up Francis.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Thanks for providing an example - this may work for ERINP.


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
Platinum Member
posted Hide Post
quote:
Originally posted by njsden:
I'm still leaning towards a simple TABLE FILE/SUM COMPUTE/HOLD approach. Hopefully that'll be feasible.


njsden,
I am working thru this process currently to create COMPUTEs/DEFINEs to create the RECAP values in the first HOLD file so they are available to me in another request. It seems like the RECAP could have that functionality added to it to allow for those values to be carried thru to the next report instead of doing it through a seperate process of the data.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
Look into the PUTDDREC function: Using Functions > System Functions > PUTDDREC: Writing a Character String as a Record in a Sequential File

Francis,
I will bookmark this post for future reference so when we do upgrade to 7.7.x I can refactor my existing code to apply this new Reporting Language Enhancement.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Virtuoso
posted Hide Post
ERINP, PUTDDREC has been available since WF 7.1.3 so it should be of use to you in 7.6.9. Please see my sample code above to determine if it meets your needs.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
njsden,
Sorry I mis-read your post. At 8am I wasn't quite thru my first cup of joe. I will look into the PUTDDREC as a potential option for my report.

Upon review of your code:
DUMMY/I2 = PUTDDREC('MYFILE',6,MARGIN,10,DUMMY);
DUMMY/I2 = PUTDDREC(ddname, dd_len, record_string, record_len, outfield)
ddname = 'MYFILE"
dd_len = 6    -*6 charcters in MYFILE
record_string = MARGIN
record_len = 10
outfield = DUMMY

Do I need to call/reference CLSDDREC within my code or will it close on its own (inferred)?
[edit]It appears i have answered this question by further reading: remain open until the end of a request or connection

ERINP

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


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Virtuoso
posted Hide Post
No worries Wink Hopefully it'll work for you ... I am already trying to get some ideas as to how to put this function to good use at my place. It has great potential.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
Yes it does have great potential...I have a current project where I need to flag some records when they have been processed and pushed to our end users via report caster. I think I can leverage this to create a flat file (.dat) that stores the records sent.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Platinum Member
posted Hide Post
As a result of the RECAP and RECOMPUTEs not being available from the hold file I had to create my own fields to create values equal to the RECAP/RECOMPUTE fields so that they were available to me in another request. I had to also create a SUBFOOT with these values since I no longer was using a GRAND-TOTAL for output.

Thanks,
ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Member
posted Hide Post
Hey guys, this is a great discussion. Can someone explain how to do all of this using the report painter? We do not allow modifications to the underlying focus code.


Release 7.7.03,
Windows platform,
Excel, PDF, HTML,Active Reports, Active/Flex,
 
Posts: 25 | Location: Michigan, USA | Registered: May 09, 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     [CLOSED]ON TABLE RECOMPUTE AS 'TOTAL'

Copyright © 1996-2020 Information Builders