Hi,

I am preparing a complex report for my business using custom Headers and Footers. But I have reached a roadblock and would seem some assistance from you guys.

So here is the basic overview. The report has a Fixed Header and Footer which it prints at the top and bottom.
The Body of the report is the value it gets from the Postgres view (via SQL passthru). Now I would like to have different rows printed in the body section depending on a certain condition

E.g. If type EQ '3' THEN PRINT
A
B
C
ELSE PRINT
D
E
F

I tried this logic but when I go to run my report it only prints the Header and Footer and completely ignores the body

My Code is like this. Sorry for providing the whole bit I just wanted to make it clear to you guys. Basically the check is on the variable transaction_type. If it is 3 then print a specific order of rows else it it's 2 print another order or rows

 
SET STYLEMODE = FIXED
SET HOLDLIST = PRINTONLY
-RUN

ENGINE SQLPSTGR SET DEFAULT_CONNECTION report
ENGINE SQLPSTGR SET VARCHAR ON
SQL SQLPSTGR PREPARE SQLOUT FOR
SELECT * from datafile;
END
-RUN

TABLE FILE SQLOUT
PRINT
COMPUTE DATE/A10 = TRIM('B', FPRINT(date, 'A10', 'A10'), 10, ' ', 1, 'A10');
COMPUTE LOCATION_NAME/A20 = TRIM('B',FPRINT(location_name, 'A60', 'A20'), 20, ' ', 1, 'A20');
COMPUTE TRANSACTION_TYPE/A1 = TRIM('B',FPRINT(transaction_type, 'A5', 'A1'), 1, ' ', 1, 'A1');
COMPUTE TRANSACTION_NAME/A32 = TRIM('B',FPRINT(transaction_name, 'A50', 'A32'), 32, ' ', 1, 'A32');
COMPUTE STATE/A3 = TRIM('B',FPRINT(state, 'A255', 'A3'), 3, ' ', 1, 'A3');
COMPUTE AMOUNT/A12 = TRIM('B',FPRINT(amount, 'I11', 'A12'), 12, ' ', 1, 'A12');
ON TABLE HOLD AS HOLD0
END
-RUN

-* Further Data Manipulations, Logic and declarations of the Header and Footer are carried out here
DEFINE FILE HOLD0

-*Headers
MAINHEADER/A122 = 'TRANSACTIONS   ';

-*Footers
FOOTER1/A122 = '000CORP      00135650';
END

-* A Template HOLD0 is defined which will hold all the values in it's final form. It includes all the relevant columns and rows
TABLE FILE HOLD0
PRINT
  MAINHEADER
  DATE
  LOCATION_NAME
  TRANSACTION_TYPE
  TRANSACTION_NAME
  STATE
  AMOUNT
  FOOTER1
ON TABLE HOLD AS LINKHOLD FORMAT FOCUS
END

-* A temporary ftm file is created that holds all the data from the queries below. So, it's broken into Header, Body and Footer
-* Finally this entire ftm file points to a predefined Master file (at the end of the procedure) where it is finally saved as a report

FILEDEF LINKOUT DISK foccache/link_datafile.ftm
-RUN

-* Print out MAINHEADER line 
TABLE FILE LINKHOLD
SUM
MAINHEADER AS '';
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE HOLD AS LINKOUT FORMAT ALPHA
END
-RUN

-* A new FILEDEF so that we now append new data
FILEDEF LINKOUT DISK foccache/link_datafile.ftm (APPEND
-RUN

-* Print out the Report Details i.e. Body
TABLE FILE LINKHOLD

IF TRANSACTION_TYPE EQ '3' THEN PRINT
DATE AS ''
LOCATION_NAME AS ''
TRANSACTION_TYPE AS ''
TRANSACTION_NAME AS ''
ELSE PRINT
STATE AS ''
AMOUNT ''
ON TABLE HOLD AS LINKOUT FORMAT ALPHA
END
-RUN

-* Print out FOOTER1
TABLE FILE LINKHOLD
SUM
FOOTER1 AS '';
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE HOLD AS LINKOUT FORMAT ALPHA
END
-RUN

-* This FILEDEF points to the premade MASTER FILE (link_datafile.mas) for this XSL output - basically 1 line per record of 150 characters (A150)
-* Each line is saved in a variable INREC
FILEDEF LINK_DATAFILE DISK foccache/link_datafile.ftm
-RUN

TABLE FILE LINK_DATAFILE
PRINT INREC
ON TABLE PCHOLD FORMAT DFIX DELIMITER TAB
END
-EXIT

If there is any way to achieve this using WebFocus it would be great. It seems so hard to carry out data manipulations using their FOCUS language as it's so hard to understand it!!

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

Dear touch,

It seems you're missing some vital knowledge about reporting-language and Dialogmanager commands.

stuff like:

TABLE FILE LINKHOLD
IF TRANSACTION_TYPE EQ '3' THEN...

Will just give you a syntax error.

Focus-language is not 'BASIC'

You must use the - commands ( e.g. -IF etc. ) to control what is executed and what not.

Please read some documentation. Even the 'help' in Developer Studio can help you. Just look up IF and you'll see it can not be used the way you're trying.

This might not be the answer you want...
...but you really shouldn't start using webfocus without some education. Either from a course or perhaps some collegues.

G'luck

Dave has a valid point, training and just playing with the product. There is always a mindset when using WebFOCUS, and data manipulation is really easy when you understand the product. That is what is does.

I would take a different approach to yours when getting to your requirement, this is really mountain and molehill, but rather than totally reinventing the wheel:

-* Print out the Report Details i.e. Body
TABLE FILE LINKHOLD
PRINT
COMPUTE LINE/A122 =
IF TRANSACTION_TYPE EQ '3' THEN DATE | LOCATION_NAME | TRANSACTION_TYPE | TRANSACTION_NAME 
ELSE STATE | AMOUNT;  AS ''
ON TABLE HOLD AS LINKOUT FORMAT ALPHA
END
-RUN

I have selectively printed columns on a report using parameters and amper variables. Here is a quick example using the CAR file. When the PRINT FLAG is 1 then the 'stats' are not printed, when PRINT FLAG NE 1 then the 'stats' are printed.

 -* File focal_point_print.fex
-SET  &PRINT_NOPRINT = IF &PRINT_FLAG EQ 1 THEN 'NOPRINT' ELSE ' ';
TABLE FILE CAR
PRINT 
     CAR.ORIGIN.COUNTRY
     CAR.COMP.CAR
     CAR.CARREC.MODEL
     CAR.BODY.BODYTYPE
     CAR.BODY.SEATS
     CAR.BODY.DEALER_COST
     CAR.BODY.RETAIL_COST
     CAR.BODY.SALES
     CAR.SPECS.LENGTH &PRINT_NOPRINT
     CAR.SPECS.WIDTH &PRINT_NOPRINT 
     CAR.SPECS.WHEELBASE &PRINT_NOPRINT 
     CAR.SPECS.FUEL_CAP &PRINT_NOPRINT
     CAR.SPECS.BHP &PRINT_NOPRINT 
     CAR.SPECS.RPM &PRINT_NOPRINT 
     CAR.SPECS.MPG &PRINT_NOPRINT 
     CAR.SPECS.ACCEL &PRINT_NOPRINT
     CAR.WARANT.WARRANTY
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END 

Thanks a lot guys. and Also Dave for your valuable thoughts

I am new to webFocus and especially the FOCUS language. I usually build reports through SQL queries and then apply the appropriate formatting in WebFocus. This custom report requires some FOCUS knowledge so came across a roadblock.

I played around with it and got it as follows

PRINT
A
B
C
IF TRANSACTION_TYPE EQ '3'

It works now when the IF statement is after what is printed