I have a numeric table field that I want to use as part of the column title. I don't get any error, but the value of the field won't display.

Please see the code below for Loss_Ratio_YTD.

TABLE FILE SQLOUT
PRINT
Month_End_Date
Region
Agency_Name
Agent_Name
Quotes_YTD/I5C AS '# Quotes YTD'
Loss_Ratio_YTD/I5% AS 'Re Avg Region_YTD/I5%
Countrwide_YTD/I5% NOPRINT
Loss_Ratio_5Yr/I5%
Region_5Yr/I5%
Countrwide_5Yr/I5% NOPRINT

I even tried defining a field to convert the Region_YTD to alpla using FTOA function but it still didn't display.

Thanks in advance!!!

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

WF767, WinServ2003, SQLServ2000
HTML

Somehow the code won't paste properly.
Here it is again...

TABLE FILE SQLOUT
PRINT
Month_End_Date
Region
Agency_Name
Agent_Name
Quotes_YTD/I5C AS '# Quotes YTD'
Loss_Ratio_YTD/I5% AS 'Re Avg [Region_YTD] ,Loss Ratio YTD'
Region_YTD/I5%
Countrwide_YTD/I5% NOPRINT
Loss_Ratio_5Yr/I5%
Region_5Yr/I5%
Countrwide_5Yr/I5% NOPRINT

Oh well I guess it doesn't like less than sign.

[Region_YTD] is the table field I am trying to display as part of the column title for Loss_Ratio_YTD

Are you looking for ACROSS?

TABLE FILE SQLOUT
SUM
Quotes_YTD/I5C AS '# Quotes YTD'
Loss_Ratio_YTD/I5% AS 'Re Avg' 
BY Region
ACROSS Region_YTD/I5%
END
  

You can't use a field value as part of the column title except in the case of ACROSS where the field values become the column titles. The only way around this is to run a procedure to produce the desired title text and then read that back in as a parameter value.

You also might want to make use of SUBHEAD to create individual sections for the Regions instead of trying to put it in the column title. In ANY case, the column title will not vary with the different region values. Column titles are the same for EVERY instance in the report.

No, not trying to do an ACROSS.
Been tying to make it work using subhead using alignment grid and HEADALIGN=BODY, but the less than sign when referencing the table field is conflicting with the less than and greater than sign of the grid markers (the ones that go on the +0 sides that I can't type here coz it messes up the message).

A procedure sounds interesting. But it's starting to look like that a less appealing report is better than not being able to meet the deadline for now.

Any other suggestions are welcome!
THANKS!!!

so create the average value first

something like

TABLE FILE XXX
SUM AVE field
ON TABLE HOLD FORMAT ALPHA
END
-READ HOLD &AVERAGE.A15

this &AVERAGE value can now be used in the report

quote:

I even tried defining a field to convert the Region_YTD to alpla using FTOA function but it still didn't display.

Have you check the converted value indeed has the negative sign?

DEFINE FILE CAR
NEG/D2-=IF COUNTRY EQ 'ENGLAND' THEN -1 ELSE -2;
FLD/A5=FTOA(NEG,'(D2-)','A5');
END
TABLE FILE CAR
PRINT 
     CAR
BY COUNTRY
BY FLD
     
ON COUNTRY SUBHEAD
"Negative subhead <FLD "
ON TABLE NOTOTAL
END
  

I agree with Frank. In order for the contents of a field to be used as (a part of) the column name you need to calculate it first. Your code would then look possibly look like this:

TABLE FILE SQLOUT
SUM AVE.Region_YTD/I5
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-READ SAVE ®IONYTD.5.
TABLE FILE SQLOUT
PRINT Month_End_Date
      Region
      Agency_Name
      Agent_Name
      Quotes_YTD/I5C AS '# Quotes YTD'
      Loss_Ratio_YTD/I5% AS 'Re Avg ®IONYTD ,Loss Ratio YTD'
      Region_YTD/I5%
      Countrwide_YTD/I5% NOPRINT
      Loss_Ratio_5Yr/I5%
      Region_5Yr/I5%
      Countrwide_5Yr/I5% NOPRINT
....

Here is the work around that I was able to put together:

Basically, I have 3 lines on my heading, and used alignment grid to line them up with my data using the HEADALIGN style command.
Since the less than sign is read as a beginning of another cell grid, I defined a field that will contain the text that I want to display so I end up using just the defined field instead of a combination of text and a field.

DEFINE FILE SQLOUT
Region_YTD_Int/I3      = Region_YTD;
Countrywide_YTD_Int/I3 = Countrywide_YTD;
Region_Avg_YTD/A9      = 'Reg: '|TRIM('L',EDIT(Region_YTD_Int,'999'),3,'0',1,'A3')||'%';
Countrywide_Avg_YTD/A8 = 'Co: ' |TRIM('L',EDIT(Countrywide_YTD_Int,'999'),3,'0',1,'A3')||'%';

Region_5Yr_Int/I3      = Region_5Yr;
Countrywide_5Yr_Int/I3 = Countrywide_5Yr;
Region_Avg_5Yr/A9      = 'Reg: '|TRIM('L',EDIT(Region_5Yr_Int,'999'),3,'0',1,'A3')||'%';
Countrywide_Avg_5Yr/A8 = 'Co: ' |TRIM('L',EDIT(Countrywide_5Yr_Int,'999'),3,'0',1,'A3')||'%';
END
SET LINES = 999999
SET BYDISPLAY = ON
TABLE FILE SQLOUT
PRINT
     Agency_Name AS ''
     Agent_Name AS ''
     Quotes_YTD/I5C AS ''
     Loss_Ratio_YTD/I5% AS ''
     Region_YTD/I5% NOPRINT
     Countrywide_YTD/I5% NOPRINT
     Loss_Ratio_5Yr/I5% AS ''
     Region_5Yr/I5% NOPRINT
     Countrywide_5Yr/I5% NOPRINT
BY Month_End_Date AS ''
BY Region AS ''

ON Region PAGE-BREAK
ON TABLE SUBHEAD
"Agency Benchmark"
HEADING
" <+0> <+0> <+0> <+0> <Region_Avg_YTD<Region_Avg_5Yr"
" <+0> <+0> <+0> <+0> <Countrywide_Avg_YTD<Countrywide_Avg_5Yr"
"Month End Date<+0>Region<+0>Agency Name<+0>Agent Name<+0># Quotes YTD<+0>Loss Ratio YTD<+0>Loss Ratio 5Yr"

NOTE: It is very important that there's no space between

 <Region_Avg_YTD<Region_Avg_5Yr 

or else the space will insert a cell between the two which will not work for my report.
It can also be coded as

 <Region_Avg_YTD<+0><Region_Avg_5Yr 

if that is more readable for your taste.

Here is how the report looks like:

Agency Benchmark 
 
                                                                                                 Reg: 59%       Reg: 51% 
                                                                                                  Co: 71%        Co: 32% 
Month End Date Region Agency Name                    Agent Name               # Quotes YTD Loss Ratio YTD Loss Ratio 5Yr 
2010/07/31     CR     XXXXXMA AGENCY, INC.           XXXXXRD J XXRDSMA* (002)            2            48%            40% 
2010/07/31     CR     XXXXXON AGENCY INC             XXXXXRA E XXY (003)                 1            72%            66% 
2010/07/31     CR     XXXXXCAN XXXXXH GROUP - MN LLC XXXXXEN XXNRY* (001)                2            69%            61% 
2010/07/31     CR     XXXXXCAN XXXXXH GROUP - MN LLC XXXXXN XXDD (004)                  74            37%            59% 

NOTE: You have to cut and paste it to a notepad to give you a better idea how it looks like. I don't know how to change the font to courier.
(Can somebody please show me how to do that in Focal Point?)

Reg means Regional
Co means Countrywide

In order for the column titles to align with the data automatically, I used HEADALIGN=BODY in the STYLE section under TYPE=HEADING.

TYPE=HEADING,
     STYLE=BOLD,
     HEADALIGN=BODY,

Thanks for the ideas and suggestions!

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

quote:

(Can somebody please show me how to do that in Focal Point?)

When you're in reply, click the red rightmost icon < / >. Put your code between the two tags that get inserted.

Thanks GamP