Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to run a report multiple times in a loop ?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How to run a report multiple times in a loop ?
 Login/Join
 
Gold member
posted
Hi Everybody !

I am trying to run a report multiple times like below.

  TABLE FILE REPORT_GETFILTERS_SKP
PRINT
     FILTERS
  ON TABLE SAVE AS BRANCHLIST
END
-RUN
-SET &NumberOfBranches  = &LINES;
-SET &Counter = 0;
-REPEAT BranchLoop &NumberOfBranches TIMES
-READ BRANCHLIST NOCLOSE &Filters.A6.
-SET &Counter = &Counter + 1;
-INCLUDE test_12345
-RUN
-IF &Counter EQ &NumberOfBranches GOTO EndFex;
-BranchLoop
-EndFex


I am hoping for the fex test_12345 to run multiple times, for each parameter value &Filters passed to it. But it is running just once. What am I doing wrong ? (Am I doing anything right ? Confused )

Thanks,
Shravan

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


7.7.03, Windows 7, SQL Server 2005
 
Posts: 92 | Location: Carmel, IN | Registered: May 09, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Shravan,

Here is an example of a loop process using the CAR file.

-***********************************************************
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE SAVE AS BRANCHLIST
END
-RUN
-* ******* BEGIN LOOP *******
-:LOOPER
-READ BRANCHLIST NOCLOSE &FILTERS.A10.
-IF &IORETURN NE 0 GOTO :EOF;
TABLE FILE CAR
HEADING
"THIS REPORT IS FOR - &FILTERS"
PRINT CAR
BY COUNTRY
WHERE COUNTRY EQ '&FILTERS'
END
-RUN
-GOTO :LOOPER
-:EOF
-CLOSE BRANCHLIST
-* ******* END LOOP *******
-****************************************************

Hope this helps.
Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 224 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
Jim,

Thanks for your reply. But, your code is almost same as mine and does what my code does.


7.7.03, Windows 7, SQL Server 2005
 
Posts: 92 | Location: Carmel, IN | Registered: May 09, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hmmmmm ..... I get 5 reports when I run the code from above. One for each country.

I just ran this code:

-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE SAVE AS BRANCHLIST
END
-RUN
-SET &NumberOfBranches = &LINES;
-SET &Counter = 0;
-REPEAT BranchLoop &NumberOfBranches TIMES
-READ BRANCHLIST NOCLOSE &Filters.A6.
-SET &Counter = &Counter + 1;
-*-INCLUDE test_12345
-TYPE Filters = &Filters ~~~ Counter = &Counter ~~~ NumberOfBranches = &NumberOfBranches
-RUN
-IF &Counter EQ &NumberOfBranches GOTO EndFex;
-BranchLoop
-EndFex
-*

and I get this result:

0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
ALPHANUMERIC RECORD NAMED BRANCHLI
0 FIELDNAME ALIAS FORMAT LENGTH
COUNTRY COUNTRY A10 10
TOTAL 10
Filters = ENGLAN ~~~ Counter = 1 ~~~ NumberOfBranches = 5
Filters = JAPAN ~~~ Counter = 2 ~~~ NumberOfBranches = 5
Filters = ITALY ~~~ Counter = 3 ~~~ NumberOfBranches = 5
Filters = W GERM ~~~ Counter = 4 ~~~ NumberOfBranches = 5
Filters = FRANCE ~~~ Counter = 5 ~~~ NumberOfBranches = 5

What do you get when you run this?

~Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 224 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
If you add in an echo statement, you could easily see what is going oN.

-SET &ECHO=ALL;


Kathy Phillips
Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03
Windows
 
Posts: 118 | Location: Livonia, MI | Registered: March 27, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
If the report runs multiple times, you have to do something with each report run or you will just get the last one in your browser.
1. Hold them to disk
or
2. Compound them together.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Jim,

I am getting multiple Filters values from BRANCHLIST that is why when I do
 -TYPE Filters = &Filters ~~~ Counter = &Counter ~~~ NumberOfBranches = &NumberOfBranches 
I get values that you mentioned. But,..

N.Selph,

What you said definitely makes sense: If the report runs multiple times, you have to do something with each report run or you will just get the last one in your browser. But only the first report is showing up for me (not the last one as you mentioned). I will try the first option you mentioned (HOLD), because in my case Compounding is not an option.

I will keep you guys posted. Thanks.


7.7.03, Windows 7, SQL Server 2005
 
Posts: 92 | Location: Carmel, IN | Registered: May 09, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Are you running this online, or scheduled in Report Caster? -- RC stops the job as soon as the first formatted output is produced, so you would wind up with only the first iteration's output.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
j.gross,

I am trying to run this online, but eventually I wanted to schedule this on Report Caster. It is comforting( Smiler ) to know that RC stops the job as soon as the first formatted output is produced. So, what is the best way to do make RC iterate through a list of filters and produce the reports and save them to library ? If there is no way for me to make RC do that, I would at least try to generate these reports online, and develop my own scheduling program which can run this report at a scheduled time. Any ideas how I can generate a report multiple times using a loop ?

Thanks,
Shravan


7.7.03, Windows 7, SQL Server 2005
 
Posts: 92 | Location: Carmel, IN | Registered: May 09, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Use MORE to combine the several reports; include as first sort field (BY ... NOPRINT) an indicator of the filter, and burst on that.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi,

You can use EX command.


WebFOCUS 7.7.03
Windows 2008 srv
PMF 5.3.1 HF5
 
Posts: 54 | Registered: January 05, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
Carlos,

Using EX command worked just the same as -INCLUDE for me (producing just one report). Thanks.


7.7.03, Windows 7, SQL Server 2005
 
Posts: 92 | Location: Carmel, IN | Registered: May 09, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Shravan,

Here's the CAR example again that creates a pdf report for each country in the CAR file. A new pdf file is created for each iteration thru the loop. The last part of this runs a DIR command that lists all files in the temp directory.

-***************************************************************************
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE SAVE AS BRANCHLIST
END
-RUN
-SET &NumberOfBranches = &LINES;
-SET &Counter = 0;
-REPEAT BranchLoop &NumberOfBranches TIMES
-READ BRANCHLIST NOCLOSE &Filters.A10.
-SET &Counter = &Counter + 1;
-*-INCLUDE test_12345
-*******************************************
-** CREATES PDF REPORT NAMED MyReport_X.pdf
-** - WHERE X IS THE COUNTER IN LOOP PROCESS
-*******************************************
-SET &MyReport = 'MyReport_' || &Counter || '.pdf';
-*
FILEDEF OUT_PDF DISK &MyReport
-*
TABLE FILE CAR
HEADING
"THIS REPORT IS FOR:"
" "
"Filters: &Filters"
"MyReport: &MyReport"
" "
PRINT CAR
BY COUNTRY
WHERE COUNTRY EQ '&Filters'
ON TABLE HOLD AS OUT_PDF FORMAT PDF
END
-*******************************************
-** THIS IS WHERE YOU WOULD NEED TO COPY
-** THE REPORT TO A NETWORK DRIVE
-*******************************************
-TYPE Filters = &Filters ~~~ Counter = &Counter ~~~ NumberOfBranches = &NumberOfBranches ~~~ MyReport = &MyReport
-RUN
-IF &Counter EQ &NumberOfBranches GOTO EndFex;
-BranchLoop
-EndFex
-RUN
-*******************************************
-** LISTS ALL FILES IN TEMP DIRECTORY
-*******************************************
CMD DIR *.*
-*************************************************************************************************

Hope this helps.

~Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 224 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
Or, keep it simple:
-REPEAT EndOfLoop FOR &LoopCntr FROM 1 TO 5 ;
TABLE FILE CAR
HEADING
"REPORT NUMBER &LoopCntr"
SUM DCOST RCOST BY COUNTRY
END
-EndOfLoop




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Currently with Fidelity Investments
 
Posts: 3066 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
Hi shravan,

I am guessing you are using PCHOLD on the test_12345.fex. If so, please change it to

[code]
ON TABLE HOLD AS HOLD&Filters FORMAT FOCUS
[\code]

When PCHOLD is used, the loop breaks in the first iteration. So try to get all the informations in multiple HOLD files and then Merge them into one using "MORE" command. Let me know if this helps.


Release : 7.7.02
OS : Linux
App Server : Tomcat
all output
 
Posts: 36 | Registered: February 03, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
almost...
you're going to loop twice.
...initialize your kounter
first loop creates the individual HTML output each time, and gives it a name
ON TABLE HOLD AS MYTAB&kounter FORMAT HTML
END
... and keep track of however many you have creaated
...reinitialize a kounter
then in your output area
-HTMLFORM BEGIN
-* looop in here from 1 to however many you've made
!IBI.FIL.MYTAB&kounter;
-* end of 2nd loop
-HTMLFORM END

another advantage of such an approach
is that you can use html table tags inside the -HTMLFORM calls to place reports next to each other, etc, really design your output page




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Of course this is only the LOOPING portion:
quote:
Originally posted by Doug:
Or, keep it simple:
-REPEAT EndOfLoop FOR &LoopCntr FROM 1 TO 5 ;
TABLE FILE CAR
HEADING
"REPORT NUMBER &LoopCntr"
SUM DCOST RCOST BY COUNTRY
END
-EndOfLoop
 
Posts: 3066 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
but that only works for a very few very fast reports. the [u]whole point[/u] of a non-persistent connection is to drop the server connection as soon as the report hits the screen. therefore the outputs must be saved to the agent, and then dumped out together , allowing one final disconnect.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Excellent point Susannah... Wink
 
Posts: 3066 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Master
posted Hide Post
If your goal is to do this with report caster, here is what we do:

Over time (mostly an hour), parameters for a report are submitted and stored in a file. Every hour we run a job that does this:

1. Pre-process that checks for a row to process, if no row, end job:
2. If row found in 1 above, process and distribute the report and mark row as processed
3. Post process to resumbit the job.

This loop continues until there is nothing left to process.

We use the DSTRUN subroutine in the post-process.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 950 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to run a report multiple times in a loop ?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.