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] Variable where filter and filename

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Variable where filter and filename
 Login/Join
 
Silver Member
posted
I have to produce a report with a variable filename. This variable that is used as the filename is also used on the where filter.

Example:

TABLE FILE CAR
PRINT
CAR
SEATS
DEALER_COST
RETAIL_COST
BY COUNTRY
WHERE COUNTRY EQ &COUNTRY;
ON TABLE NOTOTAL
ON TABLE SAVE FILENAME
'C:\DATA\REPORT_&COUNTRY.XLS'
FORMAT EXL2K
END
-RUN

I'd like to have the &COUNTRY variable as part of the filename for each individual report that will be generated.


So the results should be:

Report_England.xls - contains all the cars info for England
Report_France.xls - contains all the cars info for France
Report_Italy.xls - contains all the cars info for Italy
and so on ...

Thank you.

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


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
There are many examples of this already in the forum.

e.g. How to send a file to specific folder on my hard drive


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<JG>
posted
Of cause it does help to get the syntax for the table request correct.

The variable must be enclosed in single quotes or it will be interpreted as a verb object
never mind the problem of spaces in the value. (ignore this if the variable comes in as a quoted string)

Also &COUNTRY.XLS will not work you either need .EVAL.XLS or ...XLS

quote:

TABLE FILE CAR
PRINT
CAR
SEATS
DEALER_COST
RETAIL_COST
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE NOTOTAL
ON TABLE SAVE FILENAME
'C:\temp\REPORT_&COUNTRY...XLS'
FORMAT EXL2K
END
-RUN


If you want the file to be saved to the client PC look at this post

[SOLVED]How to change the suggested name of the file to be saved

This message has been edited. Last edited by: <JG>,
 
Report This Post
Silver Member
posted Hide Post
Thanks Waz and JG for your inputs.

With a set number of departments, I was able to create a variable name for the file. Basically hardcoding the filter based on the number of departments.

-SET &NBR = 39;
-LOOP01
-SET &NBR = &NBR + 1;
-IF (&NBR GT 52) AND (&NBR LT 97) GOTO LOOP01 ;
-IF &NBR EQ 98 GOTO EXIT2 ;
-SET &FIL = 'FILEDEF PRC_DTLRPT DISK C:\DATA\Price_DEPT_' | &NBR | '.xls' ;
&FIL
-RUN
-* create report for Dept *
TABLE FILE HLDPRCFINAL
PRINT
PLAN_AMT/P12.2C AS 'Eff Price'
BY DEPT_ID AS 'Department'
BY DEPT_DESCR AS ''
BY PROMOTION_ID AS 'Event ID'
BY PROMOTION_DESCR AS ''
BY SKU_ID AS 'SKU'
BY SKU_DESCR AS ''
BY CURR_RETAIL_MAX AS 'Current Retail'
BY START_DATE AS 'Start Date'
BY END_DATE AS 'End Date'
BY PLAN_TYPE AS 'Plan Type'
WHERE DEPT_ID EQ '&NBR';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS PRC_DTLRPT FORMAT EXL2K
END
-* run the report
-RUN
-GOTO LOOP01
-EXIT2

My issue now is that if I don't know the departments on the Hold file, how can I use the distinct departments in the Hold File as the variable for the filename and the filter.

Thanks


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
Your HOLD AS name can be an amper variable as well.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<JG>
posted
Joel,

Where does your list of departments come from.

Do a pre table step and save the output then -READ it inside the loop.
The number of lines for the save gives you the iterations for the loop.

Something like this

TABLE FILE HLDPRCFINAL
BY DEPT_ID
-* WHATEVER WHERE test
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &LOOPCNT=&LINES;
-REPEAT LOOP01 &LOOPCNT TIMES
-READ SAVE &NBR.A2.
-SET &FIL = 'FILEDEF PRC_DTLRPT DISK C:\DATA\Price_DEPT_' | &NBR | '.xls' ;
&FIL
-RUN
-* create report for Dept *
TABLE FILE HLDPRCFINAL
PRINT
PLAN_AMT/P12.2C AS 'Eff Price'
BY DEPT_ID AS 'Department'
BY DEPT_DESCR AS ''
BY PROMOTION_ID AS 'Event ID'
BY PROMOTION_DESCR AS ''
BY SKU_ID AS 'SKU'
BY SKU_DESCR AS ''
BY CURR_RETAIL_MAX AS 'Current Retail'
BY START_DATE AS 'Start Date'
BY END_DATE AS 'End Date'
BY PLAN_TYPE AS 'Plan Type'
WHERE DEPT_ID EQ '&NBR';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS PRC_DTLRPT FORMAT EXL2K
END
-* run the report
-RUN
-LOOP01
-EXIT2
 
Report This Post
Silver Member
posted Hide Post
Thanks JG! That worked.


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Silver Member
posted Hide Post
Another question. It looks like it's just reading the first record of the SAVE file when it loops because of the -RUN statement. It looks like the -RUN statement resets the pointer of the SAVE file READ back to the top and just reads the first record.

How can I preserve the pointer so that it would read the next record on ths SAVE file and not reset the pointer. I still need to set my FILEDEF that's why I would need to issue the -RUN statement.

Thanks


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
You need to add the NOCLOSE option:

-READ filename[,] [NOCLOSE] &name[.format.][,]...


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
Silver Member
posted Hide Post
I just took the -RUN command and everything works fine now.


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
Keep in mind that with removing the -RUN, your TABLE FILE's generated by the loop will be run in one block, if you need to have some sort of checking for errors, then put the -RUN back in and use the NOCLOSE option.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 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     [SOLVED] Variable where filter and filename

Copyright © 1996-2020 Information Builders