I need to sort the bar chart by HIGHEST .My sample code is :-

TABLE FILE CAR
SUM SALES BY HIGHEST TOTAL SALES
BY COUNTRY
ON TABLE HOLD AS HOLD1
END
-****
GRAPH FILE HOLD1
SUM SALES
BY HIGHEST E01 NOPRINT
BY COUNTRY
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET 3D OFF
ON GRAPH SET GRMERGE ON
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH SET GRAPHSTYLE *
setSeriesType(0,1);
setLegendDisplay(true);
setLegendMarkerPosition(0);
setMarkerDisplay(true);
setUseSeriesShapes(false);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(true);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setY1LabelDisplay(false);
setY1AxisSide(0);
setTextFormatPreset(getY1Label(),1);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setFontSize(getO1Label(),12);
setFontSizeAbsolute(getO1Label(),true);
setPlace(true);
ENDSTYLE
END

The output of this code will give the Y axis value is appended with X axis.Is there any way to display only the X axis like Germany,Japan etc .

Since you can only sort by one thing in GRAPH, you have to fake it out, i.e. sort by a number but display the literal.

Below is the code, a variation of which is posted on this forum. Make sure that you do a search before posting.

And secondly, please check the Friendly Reminders link at the top and update your profile signature with your product suite, release, and platform so that we may better help you.

TABLE FILE CAR
SUM SALES 
RANKED BY HIGHEST TOTAL SALES
BY COUNTRY
ON TABLE HOLD AS HOLD1
END
TABLE FILEF HOLD1
PRINT COUNTRY
BY RANK NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &SER_CNT=&LINES;
-SET &I=0;
-* Put all the countries in unique variables.
-REPEAT GRAPH &SER_CNT TIMES
-READ SAVE NOCLOSE &CNAME.A10.
-IF &RETCODE NE 0 GOTO GRAPH;
-SET &I=&I+1;
-SET &CNAME.&I=&CNAME;
-GRAPH
-****
GRAPH FILE HOLD1
PRINT SALES
BY RANK
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET 3D OFF
ON GRAPH SET GRMERGE ON
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH SET GRAPHSTYLE *
setSeriesType(0,1);
setLegendDisplay(true);
setLegendMarkerPosition(0);
setMarkerDisplay(true);
setUseSeriesShapes(false);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
-*  This is where we start the loop to populate the setGroupLabel method once for each series on the graph.
-*  Since series numbers are relative to zero, we have to have two counters.
-*  &J will count the series.  In this case, it will go from 0 to 4.
-*  &I will match the index variable name set above.  It will go from 1 to 5.
-SET &I=1;
-SET &J=0;
-REPEAT ENDGROUP &SER_CNT TIMES
setGroupLabel(&J,"&CNAME.&I");
-SET &I=&I+1;
-SET &J=&J+1;
-ENDGROUP
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(true);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setY1LabelDisplay(false);
setY1AxisSide(0);
setTextFormatPreset(getY1Label(),1);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setFontSize(getO1Label(),12);
setFontSizeAbsolute(getO1Label(),true);
setPlace(true);
ENDSTYLE
END

Thanks.It works fine .But in my report I have to show X axis in Mon ,yy format.Example :- Jan ,08 format.I used MtY format in my code but it doesn't work .It displays like 0808 format.


Sample code:-

APP HOLD TESTFOLDER
-SET &ECHO = ALL;
SQL SQLORA SET SERVER MYDB;

SQL SQLORA
SELECT sum(volume) VOL ,tr_date DATE1 FROM TABLE1 WHERE TR_DATE >='01-aug-2008' group by tr_date;

TABLE FILE SQLOUT
PRINT
VOL
DATE1
COMPUTE TRANSDATE/YYMD = HDATE(DATE1, 'YYMD');
TIMELINE/MtY=TRANSDATE;
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
VOL
BY
TIMELINE
ON TABLE HOLD AS HD1 FORMAT ALPHA
END

-RUN
-SET &I=0;
-SET &SER_CNT=&LINES;
-REPEAT GRAPH &SER_CNT TIMES
-READ HD1 &CNAME.A10.

-SET &I=&I+1;
-SET &CNAME.&I=&CNAME;

-GRAPH

GRAPH FILE HD1
SUM
VOL
BY TIMELINE
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET GRMERGE ON
ON GRAPH SET VZERO OFF
ON GRAPH SET VAXIS 230
ON GRAPH SET HAXIS 431
ON GRAPH SET GRAPHSTYLE *
-SET &I=1;
-SET &J=0;
-REPEAT ENDGROUP &SER_CNT TIMES
setGroupLabel(&J,"&CNAME.&I");
-SET &I=&I+1;
-SET &J=&J+1;
-ENDGROUP

ENDSTYLE
END

I am using Webfocus 7.6.4 Version

You need to change the dates to alpha format

COMPUTE IDATE/I6YMD =DATECVT(DATE1, 'DMYY', 'I6YMD');
COMPUTE TIMELINE/A7=EDIT(CHGDAT('YMD', 'MTYY', EDIT(IDATE), 'A17'),'9999,$$99');

Hi,

Thanks for the solution.
Input column,TRANSDATE,is holding the date which of this format,2008/05/01.
To convert it to the format, Mon-YY, Iam using this piece of code.

TRANSDATE/YYMD = HDATE(TIMECOL, 'YYMD');
COMPUTE TIMELINE1/I6YMD =DATECVT(TRANSDATE, 'YYMD', 'I6YMD');
COMPUTE TIMELINE2/A7 = EDIT(CHGDAT('YMD','A6MTYY',EDIT(TIMELINE1),'A17'),'9999,$$99');

This is the error that Iam getting :

0 ERROR AT OR NEAR LINE 9 IN PROCEDURE SQLOUT FOCEXEC *
(FOC257) MISSING QUOTE MARKS: ,'9999,$$9
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

This is because of the comma that Iam using in the format. I tried some of the escape sequence character (\), but is not working out. Please help.

Thanks
Poongs

quote:

A6MTYY

This does not look correct, Try just using MTYY

Sorry..That was a typing mistake.I tried using MTYY too
Still did not work.

In that case can we assume that the lack of

COMPUTE

at the begining of the first line is also a mistake?

Post an example built agianst the centord file, which has dates, with the changes
so we can play. Car is no good as there are no dates.

Iam pasting the entire code that Iam using.

SQL SQLORA SET SERVER OMNI.PERSHING.COM
SQL SQLORA

Select /*+ */COUNT(PROBLEM_MGMT_METRICS_FACT.PROBLEM_ID) AS "metric1" ,
COUNT(PROBLEM_MGMT_CATEGORY_DIM.PRODUCT_TYPE) AS "metric2" ,
DATE_DIMENSION.FIRST_DAY_OF_MONTH AS TIMECOL
FROM omni_user.PROBLEM_MGMT_METRICS_FACT,
omni_user.PROBLEM_MGMT_CATEGORY_DIM,
omni_user.DATE_DIMENSION
WHERE OMNI_USER.DATE_DIMENSION.FIRST_DAY_OF_MONTH BETWEEN
ADD_MONTHS(TRUNC(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -0),'MM'), +1 - 13)
AND ADD_MONTHS(TRUNC(SYSDATE,'MM'),-0)
AND OMNI_USER.PROBLEM_MGMT_METRICS_FACT.RCA_REMEDY_7_CATEGORY_KEY =
OMNI_USER.PROBLEM_MGMT_CATEGORY_DIM.PROBLEM_MGMT_CATEGORY_KEY AND
OMNI_USER.PROBLEM_MGMT_METRICS_FACT.DATE_KEY = OMNI_USER.DATE_DIMENSION.DATE_KEY
GROUP BY DATE_DIMENSION.FIRST_DAY_OF_MONTH;
SQL SQLORA END SESSION;

TABLE FILE SQLOUT
PRINT
metric1
metric2
TIMECOL
COMPUTE TRANSDATE/YYMD = HDATE(TIMECOL,'YYMD');
COMPUTE TIMELINE1/I6YMD =DATECVT(TRANSDATE, 'YYMD', 'I6YMD');
COMPUTE TIMELINE/A7=EDIT(CHGDAT('YMD', 'MTYY', EDIT(TIMELINE1), 'A17'),'9999,$$99');

END


This is the error that Iam getting :

0 ERROR AT OR NEAR LINE 9 IN PROCEDURE SQLOUT FOCEXEC *
(FOC257) MISSING QUOTE MARKS: ,'9999,$$9
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

These are the sample values :

metric1 metric2 TIMECOL TRANSDATE TIMELINE1
573.00 497.00 2008/05/01 00:00:00 2008/05/01 08/05/01
776.00 691.00 2008/03/01 00:00:00 2008/03/01 08/03/01
675.00 538.00 2008/07/01 00:00:00 2008/07/01 08/07/01
745.00 638.00 2007/11/01 00:00:00 2007/11/01 07/11/01

Iam trying to convert TIMELINE1 value (say 08/05/01) to the format May,08.
If i replace the line
COMPUTE TIMELINE/A7=EDIT(CHGDAT('YMD', 'MTYY', EDIT(TIMELINE1), 'A17'),'9999,$$99');
with
COMPUTE TIMELINE/A7=EDIT(CHGDAT('YMD', 'MTYY', EDIT(TIMELINE1), 'A17'),'9999,');
Iam getting the TIMELINE value as JAN,FEB,MAR,...But Iam not able to give any character after ,(COMMA)

Please let me know how I can bring this format using EDIT keyword

Thanks
Poongs.

I would try putting a hold step before applying the computes,
Working directly against SQLOUT has been known to cause problems on occasions.

TABLE FILE SQLOUT
PRINT
metric1
metric2
TIMECOL
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
PRINT
metric1
metric2
TIMECOL
COMPUTE TRANSDATE/YYMD = HDATE(TIMECOL,'YYMD');
COMPUTE TIMELINE1/I6YMD =DATECVT(TRANSDATE, 'YYMD', 'I6YMD');
COMPUTE TIMELINE/A7=EDIT(CHGDAT('YMD', 'MTYY', EDIT(TIMELINE1), 'A17'),'9999,$$99');
END

Thanks for the solution. It works fine.
But Iam getting the output in CAPITAL letters
like JAN,08 etc., Is there a way to make it in small letters like Jan,08 etc ?

I tried using 'MtYY' instead of 'MTYY' .
But that is not working.

Please help.

Thanks
Poongs.

Yes

Stick the edit into the LCWORD function

LCWORD(7, EDIT(CHGDAT('YMD', 'MTYY', EDIT(TIMELINE1), 'A17'),'9999,$$99'), 'A7')

Hi,

Thanks for the solution. It works well.
Can you please provide me some link to the documents that has more of the date formats that can be used with the EDIT function ?
I need the formats for displaying the dates in the YY-MM,MM-YY,YYYY-MM,MM-YYYY formats.
Please let me know if there is any document for the date functions.

Thanks
Poongs.

The EDIT function can work on anything that is alphanumeric in its 'mask' mode regardless of whether it is a date or not.

All documentation for functions regardless of whether they are dates or not can be found in the "Using Functions" manual.

Go to the doc link in the upper right corner of this page to find the one for your release of WebFOCUS.

If you are using dev studio then most of the documentation is available simply by clicking on help
and selecting contents