Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Graph sorting on SUM..
 Go New Search Notify Tools Reply
 [CLOSED] Graph sorting on SUM..
Member
 posted December 13, 2007 03:20 AM
hello,

i have a problem sorting on a SUM in a GRAPH!
i found a few old topics about sorting on a Sum in a GRAPH! but i thought maybe there is a easier way to solve this problem in WF 7.6.4!

Maybe there is a new function in WF 7.6.4 that i don't know off?
Is there a WAY to use 2 across fields with NOPRINT?

greetZ,

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

WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.

 Posts: 7 | Registered: November 29, 2007 IP
Virtuoso
 posted December 13, 2007 04:37 AM Hide Post
Anoir,
A way to use 2 across fields with graph using NOPRINT - to my knowledge, this is not possible.
What I do to get this to work, is to combine both acrossfields into one, store it in a hold file, and then use this hold file to create the graph, using just the one across. This method also takes care of the sorting on a SUM field, since the hold file will contain the summed values, which makes it very easy to sort on.

Hope this helps.

GamP

 - Using AS 8.2.01 on Windows 10 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Member
 posted December 13, 2007 04:52 AM Hide Post
Hi Gamp,

thnx for your answer.. But i don't know how to combine those Accross fields in the holdfile? do you have an example code?
I want to sort the SUM from the Highest to the lowest value?

WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.

 Posts: 7 | Registered: November 29, 2007 IP
Virtuoso
 posted December 13, 2007 05:13 AM Hide Post
Anoir,
You haven't provided any sample of your code to work with, so I'll make something up.
Suppose you would want to across on year (A4) and on month (A2), which are two fields in your db.
And you need to do a sum for number of occurences, highest first, within some other sort field.
This would result in code like:
```DEFINE FILE MYFILE
YRMND/A6 = YEAR | MONTH;
END
TABLE FILE MYFILE
SUM OCCURS
BY YRMND
BY SORTFLD
ON TABLE HOLD
END
GRAPH FILE HOLD
PRINT OCCURS
BY SORTFLD
ACROSS YRMND
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRAPHEDIT SERVER
END
```

Hope this helps a little bit to get you going.

GamP

 - Using AS 8.2.01 on Windows 10 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Guru
 posted December 13, 2007 05:42 AM Hide Post
The solution of GamP will work fine if you're working with years and months. But it will not work if you wan't to sort numeric fields, like profit. Anyway, it will work, but you get \$1000,may|\$950,january|\$700,november etc.

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 453 | Location: Europe | Registered: February 05, 2007 IP
Member
 posted December 13, 2007 06:15 AM Hide Post
Here is the CODE.. I want to sort on the Dealer COST from Highest to lowest!

GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN

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

WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.

 Posts: 7 | Registered: November 29, 2007 IP
Virtuoso
 posted December 13, 2007 07:33 AM Hide Post
This is not as easy as it seems to be.
Because of the ACROSS, you ormally would not have control over the presentation of the dcost field. It would be sorted WITHIN the scope of each individual model-value.
To overcome this, you have to give up the sorting of the model, while the across should still be in place. There is (as far as I know) only one solution for this, please correct me if I'm wrong.
Consider the next piece of code:
```TABLE FILE CAR
SUM DCOST
BY TOTAL HIGHEST DCOST
BY MODEL
ON TABLE HOLD
END

DEFINE FILE HOLD
SORTNR/I2 = SORTNR + 1;
ACRFLD/A30 = FTOA(SORTNR,'(F3)','A3') || ('. ' | MODEL);
END

GRAPH FILE HOLD
SUM DEALER_COST
ACROSS ACRFLD
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT PNG
END
```

The first table will sort by highest sum of dcost and store it in a hold file, which can then be graphed using a custom made across field.
Hope this helps....

GamP

 - Using AS 8.2.01 on Windows 10 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Member
 posted December 13, 2007 08:00 AM Hide Post
Hey GamP..
It works like it has to.. But i don't want to see the numbers like 1. 2. 3. 4. 5.

Is there a way to disable those? of an other way that doesn't add numbers?

Thnx!

greetz.

WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.

 Posts: 7 | Registered: November 29, 2007 IP
Virtuoso
 posted December 13, 2007 08:12 AM Hide Post
Because you have to have an across, it will do a sort on the across field and (in this case) only on the across field. That means that you will have to provide your own sorting algorithm, to prevent graph from applying the normal alphabetic sort (thereby disrupting the order in which you want the graph to be displayed), and this can be done in two ways:
1. by specifying your own sort order: ACROSS FIELD COLUMN VAL1 AND VAL2 AND VAL3 etc. Disadvantgae of course is that you would need to know up front what your sort values would be and how many there are.
2. the way I coded it, providing a correct alphabetic sort field. Of course, you coudl also provide a b c in stead of 1 2 3, as long as it is in alphabetic correct sort order, it does not really matter what character(s) you use.

GamP

 - Using AS 8.2.01 on Windows 10 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Guru
 posted December 13, 2007 08:18 AM Hide Post
This will work:

-SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST= PRINTONLY
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
TABLEF FILE PREPREGRAPH
SUM
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE SAVE AS PREGRAPH
END
-RUN
GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 453 | Location: Europe | Registered: February 05, 2007 IP
Member
 posted December 13, 2007 08:43 AM Hide Post
Thnx. Frans! It is working!
@Gamp thnx for your help!

WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.

 Posts: 7 | Registered: November 29, 2007 IP
Virtuoso
 posted December 13, 2007 08:48 AM Hide Post
Frans,

Nice job! I was thinking that this technique would do the trick and you proved it. This is something 'Tony A' does all the time to control sort order.

Now IBI just needs to enhance the GRAPH FILE language.

Thanks!

Mickey

 FOCUS/WebFOCUS 1990 - 2011

 Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003 IP
Guru
 posted December 13, 2007 10:11 AM Hide Post
Hi Mickey,

Thanks, actually I've ripped this technique once from Tony A

But now it's clear to everybody with a CAR example!

I agree with you that this should be indeed in the GRAPH FILE language!

Frans

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 453 | Location: Europe | Registered: February 05, 2007 IP
Virtuoso
 posted July 10, 2009 11:27 AM Hide Post
I'm trying to use this technigue...but I have an additional sort field
So it would be
BY CAR
ACROSS MODEL
Trying to get it from the highest to lowest cost.
When I tried that in my real report...it did not sort higHest to lowest. What should I do different.

quote:
SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST= PRINTONLY
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
TABLEF FILE PREPREGRAPH
SUM
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE SAVE AS PREGRAPH
END
-RUN
GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN

 Posts: 1903 | Location: San Antonio | Registered: February 28, 2005 IP
Expert
 posted July 10, 2009 12:31 PM Hide Post
Hi Linda,

Hope all is well in Texas!

I modified this to run in MRE, and, it works fine, creating the Pareto.

```
-SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST= PRINTONLY
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
-RUN
TABLEF FILE PREPREGRAPH
SUM
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS PREGRAPH FORMAT ALPHA
END
-RUN
GRAPH FILE CAR
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-MRNOEDIT -INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-EXIT
```

HTH

Tom

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1973 | Location: Centennial, CO | Registered: January 31, 2006 IP
Virtuoso
 posted July 10, 2009 01:29 PM Hide Post
Hey Tom - where ya been? We are sweltering in Texas...18th day or something like that of record over 100 temps.

I got the orginal I have above to work...I was asking if you threw in an extra sort field - BY CAR...in the mix...it did not.

 Posts: 1903 | Location: San Antonio | Registered: February 28, 2005 IP
<FreSte>
 posted January 24, 2011 03:17 AM
Hi,

I was looking for some GRAPH setting the other day and then I came across this topic.

The solution for this problem is actually in "GRAPH FILE"
I know, for this topic it's a kind of "almost 2 years too late" but maybe for future use I'm posting it.

GRAPH FILE CAR
SUM
DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON GRAPH SET 3D OFF
ON GRAPH SET GRMERGE ON
ON GRAPH SET LOOKGRAPH VBRSTK1
END
-RUN

 IP