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] Carry sum value in one hold to a report down the line

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Carry sum value in one hold to a report down the line
 Login/Join
 
Platinum Member
posted
Hello,

Sorry, this may be long winded, but it's hard to explain.

I have a hold file that has one field in it, which is a summed value from a previous hold file that essentially calculates the count of closed records.

Beyond that I then need to create a couple hold files to calculate some fees and expenses.

Lastly I will then join together the fee and expense hold files and then report off of those. All of that goes well...Where I'm getting hung up is the initial count of closed records, I need to somehow record that value and bring it into the final report to calculate fees and expenses per the closed record count. Being there is really nothing for me to join that hold file to the fee and expense hold files I was wondering if there was a way to set a global variable or something so it can be carried to the end. I'll post my code below, but I tried to do a -SET &&COUNTVAR = HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS, which carried the variable to the end but the value was set to the text of what I tried to set "HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS". I also tried to create a dummy office and attach it to the summed hold file so that I could join it in with the others, but that doesn't work because then the count value only exists for the office number I attached it to and can't seemingly be used as an individual number to do my calculates. Lastly I tried to do a define for the HOLDEXPENSES and tried to do -INCLUDE HOLDCLOSEDTOTAL, but received an error. I'm all out of ideas, anyone else with any suggestions?

TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTAL FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
TABLE FILE VWSBLCEXPENSES
PRINT
     VWSBLCEXPENSES.VWSBLCEXPENSES.EXPENSE_AMOUNT
BY  LOWEST VWSBLCEXPENSES.VWSBLCEXPENSES.OFFICE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDEXPENSES FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
TABLE FILE VWSBLCFEES
PRINT
     VWSBLCFEES.VWSBLCFEES.FEE_AMOUNT
BY  LOWEST VWSBLCFEES.VWSBLCFEES.OFFICE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDFEES FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
 FILE HOLDEXPENSES AT HOLDEXPENSES.SEG01.OFFICE TO MULTIPLE FILE HOLDFEES
 AT HOLDFEES.SEG01.OFFICE TAG J0 AS J0
 WHERE HOLDEXPENSES.SEG01.OFFICE EQ J0.SEG01.OFFICE;
 END
TABLE FILE HOLDEXPENSES
PRINT
     J0.SEG01.FEE_AMOUNT AS 'Fee Income'
     HOLDEXPENSES.SEG01.EXPENSE_AMOUNT AS 'Expenses'
     COMPUTE NFI/D20.2 = J0.SEG01.FEE_AMOUNT - HOLDEXPENSES.SEG01.EXPENSE_AMOUNT; AS 'Net Fee Income'
     -*need to pull in the count for closed items for division
BY  LOWEST HOLDEXPENSES.SEG01.OFFICE
WHERE ( HOLDEXPENSES.SEG01.EXPENSE_AMOUNT GT 0 ) OR ( J0.SEG01.FEE_AMOUNT GT 0 );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
 

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


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Master
posted Hide Post
If you HOLD your total count of closed records you can subsequently use -READFILE to retrieve the value as an amper variable with the same name as the original variable (plus the ampersand of course).

Another method would be to use MATCH FILE with a dummy variable - DUMMY/A1=' '; in each of the two files. That will allow you to carry the value from your inital calculation into the second part.

I'm guessing you must be using the GUI because your initial 3 HOLDs have all kinds of useless styling appended at the end. Personally I would strip it out because it's just code bloat.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Expert
posted Hide Post
If it is only one record, then I would suggest the -READFILE suggested by George.

If you really want to make your life complicated though, you can issue a WHERE based join with no WHERE -

TABLE FILE GGSALES
  SUM DOLLARS
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE GGSALES
  SUM UNITS
      BUDUNITS
      BUDDOLLARS
   BY ST
ON TABLE HOLD AS TEMPHLD2
END
-RUN

JOIN CLEAR *
JOIN          FILE TEMPHLD2 AT ST TAG T1
  TO MULTIPLE FILE TEMPHLD1 AT DOLLARS TAG T2
END

?FF TEMPHLD2

TABLE FILE TEMPHLD2
  SUM
      DOLLARS
      BUDDOLLARS
      UNITS
      BUDUNITS
   BY ST
END
-RUN


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
Platinum Member
posted Hide Post
Thanks for the suggestions! I'm trying to work through these. First I'm trying the readfile option. I've read up on it and have implemented what seems to be the right syntax, but I'm having some trouble. If I use single amper as it currently looks below and I go to open or run the output report it asks me to manually enter a value for CLREQS. When I use double amper and run the report I get the following error: "(FOC295) A VALUE IS MISSING FOR : &&CLREQS". I've only included the code in which I've modified from that of the original post. Going to keep working through this and then the other options, but if you see where I may be going astray please let me know.

TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS AS 'CLREQS'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTALS FORMAT FOCUS
END

-RUN
-READFILE HOLDCLOSEDTOTALS
-TYPE CLREQS IS &CLREQS
  



One other tidbit, I found one forum post where a user specified to set the FORMAT of the hold to ALPHA and I tried that to no avail as well.

And yes, in part I use GUI and in part I use the text. Hadn't gotten around to cleaning up the code yet Smiler.

Thanks,
Joe


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Master
posted Hide Post
Oh ya.... Use format ALPHA ...

And before your first TABLE put this:

SET ASNAMES=ON


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Platinum Member
posted Hide Post
Hi George,

I set the hold file format back to ALPHA and also set ASNAMES to ON at the beginning of the procedure, and still when opening the final report it asks me to manually enter a value for CLREQS. Something must be amiss, and I can't seem to find what it is.

Thanks,
Joe


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Expert
posted Hide Post
quote:
Use format ALPHA

When you use -READ then you need to use FORMAT ALPHA, but using -READFILE uses the formats within the synonym.

Use SET ASNAMES = ON as George suggests and also use -DEFAULTH &CLREQS = 0 to get rid of the FOC295 error message.

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
Platinum Member
posted Hide Post
Thank you kindly Gents, that did the trick.

So my hold with the single sum value looks like this:
TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS AS 'CLREQS'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTALS FORMAT FOCUS
END


Then just before my final table file I have this:
-DEFAULTH &CLREQS = 0
-RUN
-READFILE HOLDCLOSEDTOTALS
-TYPE CLREQS IS &CLREQS


From there in my final table file I'm able to call &CLREQS and display that value (for example in a subfoot):

ON TABLE SUBFOOT
"&CLREQS<+0> <+0> <+0> "


Now I just need to work on my math Smiler.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report 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] Carry sum value in one hold to a report down the line

Copyright © 1996-2020 Information Builders