John

Here is the code I use to do that.

The report prints the MSGraph in the header and then another table is created to populate the detail lines.

The format that is used to create the data for the graph is just the opposite of the way that you would want to view the data in the detail lines of the report.

#define crlf chr(13)+chr(10)
#define tab chr(9)
set safety off

local lcdata, lcexpyr, lnYsize, lnXsize, lcTabString, lccri1, lccri2

lccri1 = 'spruce + balsam + pine + tamarack > 0'
lccri2 = 'expire > {^1900-01-01} '

select agency, year(expire) as yr_exp, sum(spruce + balsam + pine + tamarack) as totvolume from i2 where agency <> ' ' and &lccri2. and &lccri1. order by agency, yr_exp desc group by agency, yr_exp into cursor tempvol

* select distinct from tempvol to create x axis and y axis arrays
select distinct agency from tempvol into array ydata
select distinct yr_exp from tempvol order by yr_exp desc into array xdata

lcTabString = "(yseries c(10), "
FOR sn = 1 TO ALEN(xdata,1)
if sn <> alen(xdata,1) then
lcTabString = lcTabString + "X"+alltrim(str(xdata(sn))) +" n (10),"
else
lcTabString = lcTabString + "X"+alltrim(str(xdata(sn))) +" n (10)"
endif
endfor
lcTabString = lcTabString + ")"

* Create xtab table structure
create table xtab &lcTabString.
for recno = 1 to alen(ydata,1)
append blank
replace yseries with ydata(recno)
endfor
locate record 1
* Populate table with zeros in blank fields
scan
for sn = 1 to alen(xdata,1)
xitem = "X"+alltrim(str(xdata(sn)))
replace &xitem. with 0
endfor
endscan

* Update total volumes from totvol cursor to xtab table.
select tempvol
scan
lcag = tempvol.agency
lcex = "X"+alltrim(str(tempvol.yr_exp))
update xtab set xtab.&lcex. = tempvol.totvolume where alltrim(xtab.yseries) = alltrim(tempvol.agency)
endscan

* Build opposite xtab table "repxtab" for report clarity
* This is just the opposite of the first table

select distinct yr_exp from tempvol into array rydata
select distinct agency from tempvol into array rxdata

lcTabString = "(yseries c(10), "
FOR sn = 1 TO ALEN(rxdata,1)
if sn <> alen(rxdata,1) then
lcTabString = lcTabString + "X"+alltrim(rxdata(sn)) +" n (10),"
else
lcTabString = lcTabString + "X"+alltrim(rxdata(sn)) +" n (10)"
endif
endfor
lcTabString = lcTabString + ")"
* Create xtab table structure
create table repxtab &lcTabString.
for recno = 1 to alen(rydata,1)
append blank
replace yseries with alltrim(str(rydata(recno)))
endfor
locate record 1
* Populate table with zeros in blank fields
scan
for sn = 1 to alen(rxdata,1)
xitem = "X"+alltrim(rxdata(sn))
replace &xitem. with 0
endfor
endscan

* Update total volumes from totvol cursor to repxtab table.
select tempvol
scan
lcag = "X"+alltrim(tempvol.agency)
lcex = "X"+alltrim(str(tempvol.yr_exp))
update repxtab set repxtab.&lcag. = tempvol.totvolume where alltrim(repxtab.yseries) = alltrim(str(tempvol.yr_exp))
endscan

select xtab
locate record 1
lcdata = " "
for sn = 1 to alen(xdata,1)
xitem = alltrim(str(xdata(sn)))
if sn <> alen(xdata,1) then
lcdata = lcdata + tab + xitem
else
lcdata = lcdata + tab + xitem + crlf
endif
endfor
*lcdata = " "+ tab + "Boise" + tab + "County" + tab + "Federal" + tab + "Private" + tab + "State" + crlf

scan
for sn = 1 to alen(xdata,1)
xitem = "X"+alltrim(str(xdata(sn)))
do case
case xtab.yseries = "B"
sname = "Boise"
case xtab.yseries = "C"
sname = "County"
case xtab.yseries = "F"
sname = "Federal"
case xtab.yseries = "P"
sname = "Private"
case xtab.yseries = "S"
sname = "State"
endcase
if sn = 1
lcdata = lcdata + sname + tab + str(xtab.&xitem.)
else
if sn = alen(xdata,1)
lcdata = lcdata + tab + str(xtab.&xitem.) + crlf
else
lcdata = lcdata + tab + str(xtab.&xitem.)
endif
endif
endfor
endscan
release tempvol
use graph
select graph
locate for name = "Aspen Vol"
append general ms_graph data lcdata
use