I'm getting this error: (FOC32466) MERGE DIMENSIONS EXCEED THE NUMBER OF SORT FIELDS

For this GRAPH request:

DEFINE FILE HOLDFILE
DATWK/wt = DATE;
END
GRAPH FILE HOLDFILE
-* Created by Advanced Graph Assistant
SUM AMOUNT AS 'Amount'
BY GROUP AS 'Group' ROWS 'A' OVER 'B' OVER 'C' OVER 'D' OVER 'E' OVER 'Other'
ACROSS DATWK COLUMNS Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 455
ON GRAPH SET VAXIS 300
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 6
ON GRAPH SET GRXAXIS 7
END

I'm pretty sure that contains 7 weekdays and 6 groups (and 1 quantity per weekday), so why am I getting this error?

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

ON GRAPH SET GRMERGE ON

Not sure why, but although it does output a GRAPH, the legend labels got replaced by numbers and the O-axis labels are rotated somehow.

Can't this be done with an advanced graph?

Our original (advanced) graph doesn't have the ROWS OVER part in the BY clause and that works.

However, from the same data another graph is rendered as well and I want to get the same line colors and legend labels as in the first graph.
For example, this month the 2nd graph has no data for groups 'B' and 'C', which causes the colors for those columns to be applied to what are really groups 'D' and 'E'...

I was hoping that explicitly adding the groups as a ROWS OVER would fix that, but then I hit the snafu that prompted me to post the issue in here.

Would you be able to provide us with some data which we can use to duplicate your issue? Or, duplicate your issue with a BASEAPP FOC file?

Well, as it turns out the GGSALES sample tables are similar to what we have, so I can!

As you can see in the below, the color used for 'Gifts' in the first graph isn't the same color as the one in the second graph, due to there not being any data for 'Food' in the second (because I stripped out the result to make the issue clear).

I want to make sure that the second graph will use the same colors for the same categories, WITHOUT knowing beforehand which category will have results or not.

There is a fixed set of categories in our real situation, just as there is in the GGSALES example, which is why I hoped to be able to use ROWS OVER to fixate the categories.

DEFINE FILE GGSALES
	DATWK/wt = DATE;
END
GRAPH FILE GGSALES
SUM UNITS
BY CATEGORY
ACROSS DATWK COLUMNS Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 455
ON GRAPH SET VAXIS 300
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setFillColor(getSeries(0),new Color(153,255,153));
setFillColor(getSeries(1),new Color(153,153,255));
setFillColor(getSeries(2),new Color(255,128,128));
setFillColor(getSeries(3),new Color(255,255,153));
setFillColor(getSeries(4),new Color(255,153,255));
ENDSTYLE
END

GRAPH FILE GGSALES
SUM DOLLARS
BY CATEGORY
ACROSS DATWK COLUMNS Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed
WHERE CATEGORY NE 'Food';
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 455
ON GRAPH SET VAXIS 300
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setFillColor(getSeries(0),new Color(153,255,153));
setFillColor(getSeries(1),new Color(153,153,255));
setFillColor(getSeries(2),new Color(255,128,128));
setFillColor(getSeries(3),new Color(255,255,153));
setFillColor(getSeries(4),new Color(255,153,255));
ENDSTYLE
END

I changed the grouping into:

BY CATEGORY ROWS 'Coffee' OVER 'Food' OVER 'Gifts'

And changed the graph type with:

ON GRAPH SET GRMERGE ON

The result has '1', '2' and '3' in the legend. And still the wrong line color for 'Food' in the second graph I just noticed... (It should be red, just as in the first graph)

If you do a BY ... ROWS, you basically introduce a new temporary variable that holds the sort order you specify with the ROWS command. Graph can only show one BY, and since the sort order is the first, it wil show 1, 2 and 3 in stead of the text.
The wrong color for food? Take out the WHERE statement that excludes the Food column. You'll then get the same colors.

I can't remove the WHERE-clause from our actual situation as it would get us the wrong results: We filter on a criterium for which there's no equivalent in GGSALES and that sometimes causes there to be no results in the time-range we're looking at, which causes the undesirable colour-shift.

I just simulated the same effect in GGSALES with that specific WHERE-clause. The effect is the same.

Hi,

What has worked for me in the past was the following:

1) I create a HOLD file with my filters applied e.g.

SUM DOLLARS BY CATEGORY BY DATWK WHERE .. 
ON TABLE HOLD AS GRPHHOLD

2) I then read this file an generate my colors, based on a DECODE of colour to category
e.g.

TABLE FILE GRPHHOLD
PRINT
COMPUTE SEQ/I1 = IF LAST CATEGORY EQ ' ' THEN 0 ELSE (SEQ + 1) ; NOPRINT
COMPUTE COLR/A11 = DECODE CATEGORY(
'Coffee' '153,255,153'
'Food' '153,153,255'
'Gifts' '255,128,128'
ELSE '255,255,153'); NOPRINT
COMPUTE PRINT_LINE/A80 = 
'setFillColor(getSeries(' || (EDIT(SEQ)) ||
'),new Color(' || COLR || 
'));' ;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS GRPHCOLR
END

3)
Now you have your dynamically generated colour lines which you can include in your stylesheet with "-INCLUDE GRPHCOLR"
You however run your GRAPH off the HOLD file.

Something along those lines!

Sorry ... coding without testing ;-)

Step 2 will need to be along these lines:

quote:

TABLE FILE GRPHHOLD
SUM
COMPUTE SEQ/I1 = IF LAST CATEGORY EQ ' ' THEN 0 ELSE (SEQ + 1) ; NOPRINT
COMPUTE COLR/A11 = DECODE CATEGORY(
'Coffee' '153,255,153'
'Food' '153,153,255'
'Gifts' '255,128,128'
ELSE '255,255,153'); NOPRINT
COMPUTE PRINT_LINE/A80 =
'setFillColor(getSeries(' || (EDIT(SEQ)) ||
'),new Color(' || COLR ||
'));' ;
BY CATEGORY NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS GRPHCOLR
END

Thanks for the suggestion, but this weekend I realised what the answer is. We can use "normal" WF table styling to match line colours to categories! For the legend, you still need to supply the same set of colours in the graph style section (in the same order!).

Here it is:

DEFINE FILE GGSALES
	DATWK/wt = DATE;
END
GRAPH FILE GGSALES
SUM UNITS
BY CATEGORY
ACROSS DATWK COLUMNS Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 455
ON GRAPH SET VAXIS 300
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET STYLE *
$
TYPE=DATA, COLOR=RGB(153 255 153), WHEN=CATEGORY EQ 'Coffee',$
TYPE=DATA, COLOR=RGB(153 153 255), WHEN=CATEGORY EQ 'Food',$
TYPE=DATA, COLOR=RGB(255 128 128), WHEN=CATEGORY EQ 'Gifts',$
ENDSTYLE
ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setFillColor(getSeries(0),new Color(153,255,153));
setFillColor(getSeries(1),new Color(153,153,255));
setFillColor(getSeries(2),new Color(255,128,128));
setFillColor(getSeries(3),new Color(255,255,153));
setFillColor(getSeries(4),new Color(255,153,255));
ENDSTYLE
END

GRAPH FILE GGSALES
SUM DOLLARS
BY CATEGORY
ACROSS DATWK COLUMNS Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed
WHERE CATEGORY NE 'Food';
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 455
ON GRAPH SET VAXIS 300
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET STYLE *
$
TYPE=DATA, COLOR=RGB(153 255 153), WHEN=CATEGORY EQ 'Coffee',$
TYPE=DATA, COLOR=RGB(153 153 255), WHEN=CATEGORY EQ 'Food',$
TYPE=DATA, COLOR=RGB(255 128 128), WHEN=CATEGORY EQ 'Gifts',$
ENDSTYLE
ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setLegendDisplay(false);
ENDSTYLE
END