Hi, The code below is an OLAP report. Without the IF statement,the OLAP report works. Retain the IF statement and the column sort on the OLAP report does not work.It gives out error messages saying 'Field or Computational element not recognized...'
How do I work around this?
Thanks.
-OLAP ON
-STEP_SQL ENGINE DB2 SET DEFAULT_CONNECTION XXX SQL DB2 PREPARE SQLOUT FOR SELECT col1,col2,col3 FROM Table WHERE col1 = 'ABC' ; END
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS HOLD1 END
-REP1 DEFINE FILE HOLD1 field1/a10=.... field2/a10=.... field3/a10=.... END
TABLE FILE HOLD1 PRINT col1 col2 col3 field1 field2 field3 ON TABLE HOLD AS HOLD2 END
TABLE FILE HOLD2 PRINT col1 col2 col3 field1 field2 field3 ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET OLAPPANE CONTROL ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $ ENDSTYLE END
-GOTO EXIT
-QRY2 -EXITThis message has been edited. Last edited by: Kerry,
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 09, 2009, 11:55 AM
GinnyJakes
Try putting a -RUN in front of the -IF &RECORDS statement. It is evaluating the -IF before the data is retrieved such that &RECORDS doesn't exist. BTW, you should probably use &LINES instead. That is a better variable to test if you want to know whether the request retrieved any records.
Istead of &LINES, I SET a variable and used it in the IF, it worked fine.
The &LINES is causing the problem.
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 09, 2009, 02:48 PM
dev532
I tested this code below, which doesn't work for me.
-OLAP ON
TABLE FILE CAR PRINT COUNTRY CAR MODEL SALES ON TABLE HOLD AS HOLDB1 END
-RUN -IF &LINES NE 0 THEN GOTO REP ELSE GOTO FINISH ;
-REP TABLE FILE HOLDB1 PRINT COUNTRY ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET OLAPPANE NONE ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $ ENDSTYLE END
-EXIT -FINISH -TYPE NO RECORDS -EXIT
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 09, 2009, 03:07 PM
GinnyJakes
One of the problems is that OLAP doesn't know how to find your hold file. I think you'd have to have a permanent allocation and a filedef. Secondly, your hold master wouldn't have any dimensions or measures in it.
As I said before,if I replace the &LINES with a SET variable value, it works fine and can find my hold files without using any filedefs or APP HOLD. wondering how does it find the HOLD file in that case?
I tried doing the below but it didn't work. APP HOLD BASEAPP TABLE FILE HOLD ... ON TABLE HOLD... END APP HOLD
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 09, 2009, 04:03 PM
GinnyJakes
I ran your CAR code above and I get a report. However, if I click on the OLAP control arrows, I get a file not found. Your basic design may be flawed as OLAP reruns the request when you do anything from the control. So you need to be running from some permanent file.
I don't understand why the -IF is not working. After the -RUN, do a -TYPE &LINES followed by a -EXIT. What do you get?
-TYPE &LINES gives out the number of lines. works fine.
I need to report from a HOLD file. How to make that permanent? The APP HOLD approach is not working.
Thanks.
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 09, 2009, 04:28 PM
GinnyJakes
The APP HOLD is only going to tell WebFOCUS where you want to put the file. If you want to access it separately, you need to do an APP FI or FILEDEF. In fact, it might be better if you made your hold file a FOCUS file. The first time you create it, do an APP HOLD. Then edit the master and put a DATASET parameter in it. Then the next times you do it, change the APP HOLD to an APP HOLDDATA so it won't overwrite the master. And you won't need the app path if the directory of the file is in the global path.
SOLVED!!! for now I Guess. OLAP reports are tricky.Everyday I come in and work on the OLAP reports, something doesn't work. Well, for today, it works!
Thanks a lot Ginny. You have a nice weekend!
-------------------------------- I did this and it is now working
APP HOLD BASEAPP TABLE FILE CAR PRINT COUNTRY CAR SALES ON TABLE HOLD AS HOLD1 END APP HOLD
-RUN -IF &LINES NE 0 GOTO REP ELSE GOTO FINISH ;
-REP APP FI HOLDR1 mydirectory/HOLD1.ftm TABLE FILE HOLD1 PRINT COUNTRY CAR SALES ... END -FINISH -EXIT
WF 7.1.1, WF Developer studio 7.1.1, Windows & Mainframe, HTML
October 10, 2009, 04:55 AM
<JG>
When working with OLAP it is very important to remember that any request issued from the OLAP report only knows about what it has in the stack at the time of the initial output and that any hold files and associated masters disapear as soon as WebFOCUS returns the report (was always true but not neccessarily any more).
That means that anything that is flushed from the stack by a -RUN, DM command etc. such as DEFINES, JOINS are not in the code that is generated to drive the OLAP functionality, take a look using view source to see what I mean.
If you have a version of WebFOCUS that can use the server FOC Cache functionality then hold files are not an issue because the data and master should be available for your session (depends on settings for maxage, default 3hrs) but this is not available in 7.1x
The only alternative, is as suggested by Ginny of using APP HOLD. FORMAT FOCUS is best as you do not need the FILEDEF or APP FI statements.