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]SET COMPOUND=BYTOC EXCEL Question...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]SET COMPOUND=BYTOC EXCEL Question...
 Login/Join
 
Platinum Member
posted
Is it possible to do the following...
-* 
File BYTOCtest.fex
-SET &BY_CNTRY = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN 'FOC_NONE' ELSE '';

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
&BY_CNTRY     'CAR.BODY.BODYTYPE'
&BY_CNTRY     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END  


What I am attempting to do is if the Country EQ 'ENGLAND' then 'FOC_NONE' the (2) two fields for BODYTYPE and SEATS so that they do not display. I need the &var in order for the 'FOC_NONE to work in this way. I am trying to create an amper variable for the BYTOC value.

Any ideas or suggestions

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
Dialogue Manager cannot be combined with data requests this way.

By the time your TABLE FILE is running, Dialogue Manager has long been resolved.

Let me understand your requirement ... you want an Excel report with an individual tab for each COUNTRY. Now, when COUNTRY = 'ENGLAND' you don't want 2 of the fields to show up ...

I don't think you can do that in one single pass ...

Having (or not) a field in a TABLE FILE statement is applicable to the request as a whole and not for certain records.

You can resort to some techniques such as styling those fields to display in "white" foreground and background colours depending on the value of COUNTRY but,
- They will still use up space on your spreadsheet
- The column title will still be there (unless you fake it with HEADING)

The other approach would be to run 2 requests using compound: one for all countries except ENGLAND including all columns, a second one only for ENGLAND without the columns. I've never used BYTOC and COMPOUND in combination though.

This last approach will also cause ENGLAND to show up at the end of the document instead of "somewhere in the middle" where it would normally appear due to sorting.

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



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 think the last option you mentioned is the one I will have to use. I can get the report output I am looking for with:
 
 SET COMPOUND=BYTOC

This creates a worksheet for each Country. If I exclude England from the BYTOC list I do not know how to add an additional worksheet to the existing workbook so that England would be included in the spreadsheet. I have found a couple discussions talking about combining to one worksheet but not adding a worksheet.

Any suggestions??

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
Gold member
posted Hide Post
FOC_NONE is for use in Where statemnts when yopu wish to omit the Where clause.
If you are just trying to comment out the 2 fields try this.

 
-SET &CMT = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN '-*' ELSE '';

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
&CMT.EVAL     'CAR.BODY.BODYTYPE'
&CMT.EVAL     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END  


WF 7703M, XP/Win7, MRE, RC, BID, PMF, HTML, PDF, Excel 2000/7/10
 
Posts: 73 | Location: NY | Registered: February 06, 2007Report This Post
Virtuoso
posted Hide Post
FOC_NONE can be used *anywhere* you want a particular line of code not to be processed by the FOCUS engine; this is not limited to WHERE statements.

Regardless of that, for the case being discussed the line:
-SET &CMT = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN '-*' ELSE '';


is a Dialogue Manager statement that is evaluated *before* any FOCUS statement is processed; though you might think that in that context CAR.ORIGIN.COUNTRY represents a field read from the table, Dialogue Manager actually sees it as the constant value: 'CAR.ORIGIN.COUNTRY' as it is too early in the process to even have the table involved.

Think of Dialogue Manager as a macro language or a pre-processor if you wish.



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
ERINP, I think the only choice (unless Compound Composer can help there) is to do it on a country-by-country basis, that is, getting your base list of countries and then looping through them so you can have more control over each of them (the reports, not the countries Wink ).

The code below creates a report for each country, but does not include MODEL when COUNTRY='ITALY'.

TABLE FILE CAR
PRINT DST.COUNTRY AS 'COUNTRY'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HCOUNTRY FORMAT ALPHA
END
-RUN
-SET &CNTRYCNT = &LINES;
-REPEAT :DOBYTOC FOR &I FROM 1 TO &CNTRYCNT STEP 1
-READFILE HCOUNTRY

-SET &COMPOUND  = IF &I EQ 1 THEN 'OPEN' ELSE IF &I EQ &CNTRYCNT THEN 'CLOSE' ELSE 'FOC_NONE';
-SET &HIDEFIELD = IF &COUNTRY EQ 'ITALY' THEN 'FOC_NONE' ELSE '';

SET COMPOUND=&COMPOUND
TABLE FILE CAR
PRINT
   CAR
   MODEL  &HIDEFIELD
   SEATS
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TITLETEXT='&COUNTRY', $
ENDSTYLE
END
-:DOBYTOC



Hope that gives you some ideas.



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
quote:
I've never used BYTOC and COMPOUND in combination though


Doh! BYTOC is just a special flavour of COMPOUND Red Face Oh well, I didn't lie either.



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
I think I am going to try the SET COMPOUND=BYTOC... PCHOLD EXL2K OPEN the first report all countries NE 'England'... Then do a second report PCHOLD EXL2K CLOSED for Country EQ 'England' to see if that works.

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
njsden,
the line
 -READFILE HCOUNTRY 

is throwing a FOC303 error:CONTROL LINE NOT RECOGNIZED IN FOCEXEC: -READFILE HCOUNTRY
I also tried it as
 -READ FILE HCOUNTRY

(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ
 FILE HCOUNTRY



both throw errors??


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
I think -READFILE is a 7.7.x feature ... you can replace it in 7.6.9 with something like:

-READ HCOUNTRY NOCLOSE &COUNTRY.A10.



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,
Thanks again for all of your help. I was able to manipulate the CAR file example and incorporate it into my actual report. My final code ends up being a loop within a loop within a loop but it does work. Since this is a report caster ran job the performance impact of the multiple loops is a non-issue.

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
Hmmm, I don't see why you'd need 2 nested loops for that but I'm assuming it's due to the complexity of your actual requirement.

Glad the idea was of help! Smiler



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
  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]SET COMPOUND=BYTOC EXCEL Question...

Copyright © 1996-2020 Information Builders