Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to use a table field as part of column title?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How to use a table field as part of column title?
 Login/Join
 
Member
posted
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
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks GamP


WF767, WinServ2003, SQLServ2000
HTML
 
Posts: 25 | Registered: March 17, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to use a table field as part of column title?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.