[CLOSED] Get data from a table and use it as parameter...
Here I go again...
I have to create this report with outputting in EXCEL.
If I was using the CAR file, I would have to create the report with one CAR.COMP.CAR per sheet
And each sheet would contain 2 reports (two .fex) with informations about that particular CAR
I was thinking about to make a LOOP reading the CAR file (just the the CAR.COMP.CAR column - DISTINCT), and call the 2 reports in that loop as many times as I have the CAR.COMP.CAR
Am I in the right way ? There's a way to do it ?
Thanks !This message has been edited. Last edited by: <Kathryn Henning>,
Webfocus 7.7.0.3 (Windows), printing PDF and AHTML reports
August 26, 2015, 06:13 PM
Waz
Yes, you can do it this way.
Look into ON TABLE SAVE and -READ or -READFILE and -REPEAT commands.
Where will be many on the forum and also check the documentation.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 27, 2015, 06:59 AM
MAdams1
*Makes the PRINT fields amper variables when READFILE is done, and then used in WHERE clause in TABLE FILE
TABLE FILE TERM_DIM
PRINT
TERM_DIM.TERM_DIM.START_DATE
TERM_DIM.TERM_DIM.END_DATE
WHERE TERM_DIM.TERM_DIM.TERM_CODE EQ '&TERM_CODE';
ON TABLE NOTOTAL
ON TABLE HOLD AS HTDATES
END
-RUN
-READFILE HTDATES
-RUN
WHERE HOLD_TO_DATE GE &START_DATE.QUOTEDSTRING AND HOLD_FROM_DATE LE &END_DATE.QUOTEDSTRING;
WebFOCUS Server 8.1.05 Windows 2008 Server WebFOCUS AppStudio 8.1.05 Windows 7 Professional IE 11 and Chrome Version 43.0.2357.124 m. Mostly HTML, PDF, Excel, and AHTML
August 27, 2015, 12:50 PM
Nakiri
Wow ! You guys are saving my... you know... LOL
Found an example that works. But isn't perfect yet.
Example of my first code:
-DEFAULTH &FILTER_TEXT=''
-DEFAULTH &CAR=''
TABLE FILE CAR
PRINT DST.CAR.COMP.CAR
ON TABLE HOLD AS HOLDFILE FORMAT ALPHA
END
-RUN
-SET &LNS=&LINES;
-REPEAT :LOOP1 &LNS TIMES
-READFILE HOLDFILE
-SET &FILTER_TEXT = 'WHERE CAR EQ ''' || &CAR || ''' ';
-TYPE &FILTER_TEXT
-:LOOP1
It runs and sets the &FILTER_TEXT ok. Then I went to the next step:
-DEFAULTH &FILTER_TEXT=''
-DEFAULTH &CAR=''
TABLE FILE CAR
PRINT DST.CAR.COMP.CAR
ON TABLE HOLD AS HOLDFILE FORMAT ALPHA
END
-RUN
-SET &LNS=&LINES;
-INCLUDE myproj/app/file1.fex
-REPEAT :LOOP1 &LNS TIMES
-READFILE HOLDFILE
-SET &FILTER_TEXT = 'WHERE CAR EQ ''' || &CAR || ''' ';
-SET &EXCEL_BRAKE = 'OPEN NOBREAK';
-INCLUDE myproj/app/file2.fex
-SET &EXCEL_BRAKE = 'CLOSE';
-INCLUDE myproj/app/file3.fex
-:LOOP1
Well, I just included 2 files: file1.fex - Creates a HOLD file with summarized info file2.fex - The report 1 that has to be printed taking account to the &FILTER_TEXT file3.fex - The report 2 that has to be printed taking account to the &FILTER_TEXT
The report will be created in EXCEL, and the variable &EXCEL_BRAKE is being used in
ON TABLE PCHOLD FORMAT EXL2K &EXCEL_BREAK
What is happening is that EXCEL opens just the first sheet and ignoring the other "CARS"
Am I missing something ?
Thank you !!!This message has been edited. Last edited by: Nakiri,
Webfocus 7.7.0.3 (Windows), printing PDF and AHTML reports
August 27, 2015, 06:05 PM
Waz
Sounds like the second fex isn't adding to the excel file.
Have you turned &ECHO to ON and checked the resulting code ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 28, 2015, 03:29 AM
Tony A
quote:
-REPEAT :LOOP1 &LNS TIMES
You need a semicolon at the end of this statement.
You probably want to think about using Excel Templates to achieve multi worksheets with two reports on each worksheet.
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
August 28, 2015, 06:11 AM
Nakiri
Error found !
Actually, the problem was the "OPEN NOBREAK / CLOSE" They were messing with EXCEL sheets.
What I've changed:
-DEFAULTH &FILTER_TEXT='' -DEFAULTH &CAR=''
TABLE FILE CAR PRINT DST.CAR.COMP.CAR ON TABLE HOLD AS HOLDFILE FORMAT ALPHA END -RUN -SET &LNS=&LINES; -SET &I=0;
-INCLUDE myproj/app/file1.fex
-REPEAT :LOOP1 &LNS TIMES -SET &I=&I+1; -READFILE HOLDFILE -SET &FILTER_TEXT = 'WHERE CAR EQ ''' || &CAR || ''' ';
-SET &EXCEL_BREAK = IF &I EQ 1 THEN 'OPEN NOBREAK' ELSE 'NOBREAK'; -INCLUDE myproj/app/file2.fex
-SET &EXCEL_BREAK = IF &I EQ &LNS THEN 'CLOSE' ELSE ''; -INCLUDE myproj/app/file3.fex
-:LOOP1
I thought that I had to OPEN/CLOSE for each sheet, but just leaving with NOBREAK/empty do the job.
WAS, I didn't know about &ECHO, I'll try to use it next time. Thank you !
Tony, I didn't know about the semicolon too... I just copied an example from GamP and adapted to what I needed. Apparently the lack of it isn't affecting anything, but thanks for the tip !This message has been edited. Last edited by: Nakiri,
Webfocus 7.7.0.3 (Windows), printing PDF and AHTML reports
August 28, 2015, 10:09 AM
Tony A
quote:
Originally posted by Tony A:
quote:
-REPEAT :LOOP1 &LNS TIMES
You need a semicolon at the end of this statement.
This is still the case.
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
August 28, 2015, 10:52 AM
Nakiri
Changed !
Now I'm trying to understand how to use a template to do it.
I just made a simple test and it worked. Just a single report, with a single page. That's fine.
Well, if I want to create this same report using a template, how should I go ?
Imagine that I have to print 3 reports per sheet. And I'll have as many sheets as that first LOOL has lines. So, I will never know how many sheets the report will have. But all sheets will have the same 3 reports, filtered by the LOOP.
The using of a template is really applicable here ?
Thank you !
Webfocus 7.7.0.3 (Windows), printing PDF and AHTML reports