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

-*IF &RECORDS EQ 0 THEN GOTO QRY2 ELSE GOTO REP1 ;

-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
-EXIT

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

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.

Hi Ginny,
I put a -RUN before the IF statement and every TABLE statement.

When i try to sort, it is complaining about the first DEFINED field saying -'The field name is not recognized'

The &LINES(&RECORDS) in the IF statement is causing the problem. Don't know why!

I want you to verify that your -IF is a -IF and not an IF.

Lastly, you don't need a -RUN after each grouping; just one in front of the -IF.

Yes,it's -IF

Took off the RUN from the rest of the groupings.

Istead of &LINES, I SET a variable and used it in the IF, it worked fine.

The &LINES is causing the problem.

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

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

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.

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

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.