I have a job that uses &variables to come up with the line #, where the text (Total Billed: and a field ($100,000) such as

Total Billed: $100,000

"should" be located, so that I can bold that line#. But it's a little complex, since the line # can change on many different scenarios (other lines with data may or may not appear above it). I would rather have a simpler method if possible. Is there a way to (DefMacro, etc) bold/format the Total Billed line based on "when a line in the heading contains "Total Billed Amount:", then bold that line that it's on?

The Total Billed Amount: line is always the last line in the Heading, so alternatively, so if there was a way to identify a line in reverse ("LINE=-1" etc), that would work also.

For example, how to bold the line that "Final Total Amount:" is on, without going through a complex computation of where it "should" be :



-SET &COMMENTOUT1 = '-*';
-SET &COMMENTOUT2 = ' ';
-SET &COMMENTOUT3 = '-*';
-SET &FIELD_A = 'FIELDA';
-SET &FIELD_B = 'FIELDB';
-SET &FIELD_C = 'FIELDC';
-SET &AMT_FIELD_D = '10000.00';
-SET &AMT_FIELD_E = '20000.00';
-SET &AMT_FIELD_F = '30000.00';
-SET &TOTAL_AMT_FIELD = '50000.00';

TABLE FILE CAR

HEADING
" "
"<+0> <+0> Car Report<+0>"
" "
" "
"&FIELD_A<+0>"
"&FIELD_B<+0>"
"&FIELD_C<+0>"
" "
" "
&COMMENTOUT1"<+0>Total Amount 1: <+0>&AMT_FIELD_D<+0> "
&COMMENTOUT2"<+0>Total Amount 2: <+0>&AMT_FIELD_E<+0>"
&COMMENTOUT3"<+0>Total Amount 3: <+0>&AMT_FIELD_F<+0>"
" "
" "
"<+0>Final Total Amount:<+0>&TOTAL_AMT_FIELD<+0>"
" "
" "
" "

PRINT

CAR
MODEL SEATS

BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Rodney and welcome to Focal Point.

I would use Boolean logic to determine the line number depending upon the values of the line commenting variables -

-SET &COMMENTOUT1 = '-*';
-SET &COMMENTOUT2 = ' ';
-SET &COMMENTOUT3 = '-*';
-SET &FIELD_A = 'FIELDA';
-SET &FIELD_B = 'FIELDB';
-SET &FIELD_C = 'FIELDC';
-SET &AMT_FIELD_D = '10000.00';
-SET &AMT_FIELD_E = '20000.00';
-SET &AMT_FIELD_F = '30000.00';
-SET &TOTAL_AMT_FIELD = '50000.00';

-SET &LineNum = 15 - ((&COMMENTOUT1.QUOTEDSTRING EQ '-*') + (&COMMENTOUT2.QUOTEDSTRING EQ '-*') + (&COMMENTOUT3.QUOTEDSTRING EQ '-*'))

TABLE FILE CAR

HEADING
" "
"<+0> <+0> Car Report<+0>"
" "
" "
"&FIELD_A<+0>"
"&FIELD_B<+0>"
"&FIELD_C<+0>"
" "
" "
&COMMENTOUT1"<+0>Total Amount 1: <+0>&AMT_FIELD_D<+0> "
&COMMENTOUT2"<+0>Total Amount 2: <+0>&AMT_FIELD_E<+0>"
&COMMENTOUT3"<+0>Total Amount 3: <+0>&AMT_FIELD_F<+0>"
" "
" "
"<+0>Final Total Amount: <+0>&TOTAL_AMT_FIELD<+0>"
" " 
" " 
" "

PRINT 

 CAR
 MODEL SEATS

BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE TOP
ON TABLE SET STYLE *
  type=heading, line=&LineNum, item=1, style=+bold, $
ENDSTYLE
END
-EXIT

Be aware the EXL2K will eventually become a dinosaur as Micro$oft are planning to drop support of Excel 2003 (allegedly!) - you should think about using EXL07 instead.

Also be aware that this embolding of the line and item will affect the entire cell contents, unless you use something like HEADALIGN=BODY etc.

T

Thanks for the response. I'm actually using that type of Boolean logic in the job, but it's conditional heavy, and a little more complex than I would like, and I always worry about a certain situation to come along in the data that would throw it off. I have multiple "comment out" &variables placed on each line for various lines in the Heading (not the best way, I know), which is why I was looking to use a simpler or alternative method.

So you would like it to be completely dynamic. How about objections to running the same request twice? If it is a lot of data that you have to go through it would be rather resource consuming, and in that case maybe not a good idea to run the request twice. But if the answer set or data set are not too big you might consider this:

-* File FocalPoint.fex

EX -LINES 3 EDAPUT MASTER,WPFILE,CV,FILE
FILENAME=WPFILE, SUFFIX=FIX, $
FIELDNAME=LINE, ACTUAL=A600, USAGE=A600, $

FILEDEF WPFILE DISK XYZZY.WP
-SET &LOOPCOUNT = 1;
-SET &LineNum = 0;

-:LOOP

-SET &COMMENTOUT1 = '-*';
-SET &COMMENTOUT2 = '  ';
-SET &COMMENTOUT3 = '-*';
-SET &FIELD_A = 'FIELDA';
-SET &FIELD_B = 'FIELDB';
-SET &FIELD_C = 'FIELDC';
-SET &AMT_FIELD_D = '10000.00';
-SET &AMT_FIELD_E = '20000.00';
-SET &AMT_FIELD_F = '30000.00';
-SET &TOTAL_AMT_FIELD = '50000.00';
TABLE FILE CAR
HEADING
" &LineNum "
"<+0> <+0> Car Report<+0>"
" "
" "
"&FIELD_A<+0>"
"&FIELD_B<+0>"
"&FIELD_C<+0>"
" "
" "
&COMMENTOUT1"<+0>Total Amount 1: <+0>&AMT_FIELD_D<+0> "
&COMMENTOUT2"<+0>Total Amount 2: <+0>&AMT_FIELD_E<+0>"
&COMMENTOUT3"<+0>Total Amount 3: <+0>&AMT_FIELD_F<+0>"
" "
" "
"<+0>Final Total Amount:<+0>&TOTAL_AMT_FIELD<+0>"
" "
" "
" "
PRINT CAR
      MODEL SEATS
BY    COUNTRY
-IF &LOOPCOUNT NE 1 THEN GOTO :FINAL;
ON    TABLE HOLD AS XYZZY FORMAT WP
END

TABLE FILE WPFILE
PRINT COMPUTE LINENUM/I5 = LINENUM+1;
WHERE TOTAL LINE CONTAINS 'Final Total Amount';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &LineNum.5.
-SET &LineNum = &LineNum-2;
-SET &LOOPCOUNT = 2;
-GOTO :LOOP
-:FINAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
  type=heading, line=&LineNum, item=1, style=+bold, $
ENDSTYLE
END

-EXIT

Hope this helps a bit...

GamP, this look like a possibility. I'll take a look.

Thanks.

You could also set up multiple stylesheet lines for the Total Amount line, and utilize the existing &COMMENTOUTn variables to comment out the inappropriate stylesheet lines...