[SOLVED] How to use a table field as part of column title?
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
September 09, 2010, 03:12 PM
Kulot
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
WF767, WinServ2003, SQLServ2000 HTML
September 09, 2010, 03:13 PM
Kulot
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
WF767, WinServ2003, SQLServ2000 HTML
September 09, 2010, 04:17 PM
Hua
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
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
September 09, 2010, 04:20 PM
Darin Lee
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.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 09, 2010, 04:56 PM
Kulot
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!!!
WF767, WinServ2003, SQLServ2000 HTML
September 09, 2010, 04:59 PM
FrankDutch
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
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
September 09, 2010, 05:55 PM
Hua
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
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
September 10, 2010, 04:26 AM
GamP
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
....
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 13, 2010, 04:40 PM
Kulot
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,
WF767, WinServ2003, SQLServ2000 HTML
September 14, 2010, 03:57 AM
GamP
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.