Focal Point
AHTML Graphs: Customizing the legend
November 05, 2009, 11:47 AM
sleepyEDBAHTML Graphs: Customizing the legend
Hello,
I have an active report that I'm displaying as a bar graph. I'm happy with the graph except for the names in the legend, which are displaying the 'Sum.' prefix:
Does anyone know a way in which I can remove or otherwise stop the 'Sum.' prefix from displaying on the graph?
Here is the code behind the graph:
-* File projects-hrstodatevsestgraph.fex
DEFINE FILE PROJECTSTATUSDASHBOARD
REVEST/D12.1=IF REVISEDESTIMATE EQ '.' THEN ORIGINALESTIMATE ELSE REVISEDESTIMATE;
END
TABLE FILE PROJECTSTATUSDASHBOARD
SUM
'MAX.REVEST'
AS 'Estimated Hours'
'DST.PROJECTTODATE'
AS 'Hours to Date'
BY 'SSRNUMBER' AS 'SSR'
HEADING
"In-Process SSRs: Estimated vs Actual Hours"
FOOTING
""
WHERE JOBSTATUS EQ 'InProcess';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
LINES-PER-PAGE=20,
REPORT-VIEW=CHART,
CHART-TYPE=BAR,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
ENDSTYLE
END
thanks,
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX
November 05, 2009, 11:57 AM
Tom Flynn
SET ASNAMES=ON
DEFINE FILE PROJECTSTATUSDASHBOARD
REVEST/D12.1=IF REVISEDESTIMATE EQ '.' THEN ORIGINALESTIMATE ELSE REVISEDESTIMATE;
END
TABLE FILE PROJECTSTATUSDASHBOARD
SUM
MAX.REVEST AS 'Estimated_Hours'
DST.PROJECTTODATE AS 'Hours_to_Date'
BY SSRNUMBER AS 'SSR'
ON TABLE HOLD AS HOLD1
END
-RUN
TABLE FILE HOLD1
PRINT
Estimated_Hours AS 'Estimated Hours'
Hours_to_Date AS 'Hours to Date'
BY SSR
HEADING
"In-Process SSRs: Estimated vs Actual Hours"
FOOTING
""
WHERE JOBSTATUS EQ 'InProcess';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
LINES-PER-PAGE=20,
REPORT-VIEW=CHART,
CHART-TYPE=BAR,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
ENDSTYLE
END
Going back to bed
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
November 05, 2009, 12:05 PM
sleepyEDBTom,
Thanks for your quick reply!
Unfortunately, your code produced the following error:
0 NUMBER OF RECORDS IN TABLE= 333 LINES= 52
(BEFORE DISTINCT TESTS)
0 ERROR AT OR NEAR LINE 16 IN PROCEDURE projects-hrstodate
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: Estimated_Hours
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX
November 05, 2009, 12:09 PM
Tom FlynnDid you copy this:
SET ASNAMES=ON
After
ON TABLE HOLD AS HOLD1
END
-RUN
Do:
?FF HOLD1
-EXIT
It will show you the column names; those are used in the subsequent step...
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
November 05, 2009, 12:11 PM
GinnyJakesOpen the Report Painter for the hold request. Go to Report/Features and make sure that the calculation for the columns is set to None, not Sum. That may fix your problem since the data is already summed.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
November 05, 2009, 12:12 PM
Tom FlynnOOHHHH!! GUI stuff!
Thanks Ginny!
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
November 05, 2009, 12:14 PM
GinnyJakesYou may still have to fool around with the AS names in Tom's request as that seems to be where the syntax error is coming from.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
November 05, 2009, 01:41 PM
sleepyEDBAh, sorry. I should have specified that I used the GUI to create the report.
quote:
Originally posted by Tom Flynn:
Did you copy this:
SET ASNAMES=ON
Yes I did.
quote:
Originally posted by Tom Flynn:
After
ON TABLE HOLD AS HOLD1
END
-RUN
Do:
?FF HOLD1
-EXIT
It will show you the column names; those are used in the subsequent step...
Thanks for the tip. That produced:
0 NUMBER OF RECORDS IN TABLE= 333 LINES= 52
(BEFORE DISTINCT TESTS)
FILENAME= HOLD1
SSR E01 I11
HOURS_TO_DATE E02 F8.1
ESTIMATED_HOURS E03 D12.1
PROJECTTODATE E04 F8.1
I then changed my fex to the following:
-* File projects-hrstodatevsestgraph.fex
SET ASNAMES=ON
DEFINE FILE PROJECTSTATUSDASHBOARD
REVEST/D12.1=IF PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.REVISEDESTIMATE EQ '.' THEN PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.ORIGINALESTIMATE ELSE PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.REVISEDESTIMATE;
END
TABLE FILE PROJECTSTATUSDASHBOARD
SUM
'DST.PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.PROJECTTODATE'
AS 'Hours_to_Date'
'MAX.PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.REVEST'
AS 'Estimated_Hours'
BY 'PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.SSRNUMBER' AS 'SSR'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
PRINT
E03 AS 'Estimated Hours'
E02 AS 'Hours to Date'
BY SSR
HEADING
"In-Process SSRs: Estimated vs Actual Hours"
FOOTING
""
WHERE 'PROJECTSTATUSDASHBOARD.PROJECTSTATUSDASHBOARD.JOBSTATUS' EQ 'InProcess';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
LINES-PER-PAGE=20,
REPORT-VIEW=CHART,
CHART-TYPE=BAR,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
ENDSTYLE
END
which produced the following:
No HTML Output!
0 NUMBER OF RECORDS IN TABLE= 333 LINES= 52
(BEFORE DISTINCT TESTS)
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
quote:
Originally posted by GinnyJakes
Open the Report Painter for the hold request. Go to Report/Features and make sure that the calculation for the columns is set to None, not Sum. That may fix your problem since the data is already summed.
Thanks Ginny, but there is no 'Features' option under the 'Report' menu in my Painter. If I go to Report > Output > Features, I don't see anything relating to calculations. There's an area for Totals, but it's already set to 'No totals'.
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX
November 05, 2009, 01:46 PM
Tom FlynnThe column names are in CAPS, so, change it to CAPS, not the alias!
SET ASNAMES=ON
DEFINE FILE PROJECTSTATUSDASHBOARD
REVEST/D12.1=IF REVISEDESTIMATE EQ '.' THEN ORIGINALESTIMATE ELSE REVISEDESTIMATE;
END
TABLE FILE PROJECTSTATUSDASHBOARD
SUM
MAX.REVEST AS 'Estimated_Hours'
DST.PROJECTTODATE AS 'Hours_to_Date'
BY SSRNUMBER AS 'SSR'
ON TABLE HOLD AS HOLD1
END
-RUN
TABLE FILE HOLD1
PRINT
ESTIMATED_HOURS AS 'Estimated Hours'
HOURS_TO_DATE AS 'Hours to Date'
BY SSR
HEADING
"In-Process SSRs: Estimated vs Actual Hours"
FOOTING
""
WHERE JOBSTATUS EQ 'InProcess';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
LINES-PER-PAGE=20,
REPORT-VIEW=CHART,
CHART-TYPE=BAR,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
ENDSTYLE
END
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
November 05, 2009, 02:00 PM
sleepyEDBquote:
Originally posted by Tom Flynn:
The column names are in CAPS, so, change it to CAPS, not the alias!
D'oh! Wow, I feel smart!!
I made the change, and also realized that my WHERE statement needed to be moved above the HOLD command.
The following code has gotten me back to a working graph, but still includes the 'Sum.' prefix.
-* File projects-hrstodatevsestgraph.fex
SET ASNAMES=ON
DEFINE FILE PROJECTSTATUSDASHBOARD
REVEST/D12.1=IF REVISEDESTIMATE EQ '.' THEN ORIGINALESTIMATE ELSE REVISEDESTIMATE;
END
TABLE FILE PROJECTSTATUSDASHBOARD
SUM
'MAX.REVEST'
AS 'Estimated_Hours'
'DST.PROJECTTODATE'
AS 'Hours_to_Date'
BY 'SSRNUMBER' AS 'SSR'
WHERE JOBSTATUS EQ 'InProcess';
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1
END
-RUN
TABLE FILE HOLD1
PRINT
ESTIMATED_HOURS AS 'Estimated Hours'
HOURS_TO_DATE AS 'Hours to Date'
BY SSR
HEADING
"In-Process SSRs: Estimated vs Actual Hours"
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
LINES-PER-PAGE=20,
REPORT-VIEW=CHART,
CHART-TYPE=BAR,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
ENDSTYLE
END
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX
November 05, 2009, 02:37 PM
GinnyJakesI tried to repro your problem with the car file and couldn't do it. I have a 765 client. Maybe the problem in in your release. Sorry, but I am out of ideas.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
November 05, 2009, 02:44 PM
sleepyEDBGinny,
No worries. It isn't a showstopper, just a bit of an OCD-thing.
I appreciate your efforts,
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX
November 05, 2009, 02:46 PM
GinnyJakesYou should open a case. BTW, I grew up in Detroit. Have a home in A-squared right now but I'm on the road.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
November 05, 2009, 04:23 PM
sleepyEDBThanks, I'll check with the report requestor and see if it's an issue for them. I'll open a case if need be.
It's funny, the more places I go, the more people I meet that are or were from Michigan. The way things are going here lately, it's mostly the latter. :-\
sleepy
WebFOCUS (DEV and PROD): DevStudio 7.6.10 HF2 Servlet - MRE/Dashboard/Self Service/Report Caster - MS Windows XP SP2 - IIS & Apache - MS SQL 2005
Output: HTML (Internet Explorer 7), Excel 2003, PDF, Active Reports and FLEX