Access 2000: Passing parameters to a chart

[Roger Dias]

Access 2000 on SQL Server 7. I have a report containing a Microsoft Graph
2000 Chart (MSGraph.Chart.8). The row source for the chart is a stored
procedure but there doesn't appear to be any way to pass parameters to the
stored procedure. A regular report does this by using the Input Parameters
property but this property is missing in the chart object.

I could try creating a temporary table with the result set and set the row
source of the chart to the temporary table but that presents possible
problems with cleanup, database size, and concurrency.

Any suggestions? Thanks in advance.

Roger Dias
rd...@westeve.com

[Bill Beardshall]

Can you run the paramaters against the results of the stored procedure. In
other words, set the row source to something like "Select * from [stored
procedure?] WHERE [parameters = thingies goes here] [Group By/Order By
Cross-tab thingies goes here]"?

HTH,

Bill

Roger Dias wrote in message <93155314...@nnrp.gt.ca>...

[Steve Arbaugh]

Roger:

I was intrigued by your problem because we have lots of apps which use graph
on a report, accepting paramters (including using a current record field
value as a parameter for a specific graph on a page of the report). After a
lot of playing around
with this, I've only come up with two ways to do this.

1.) As your post suggests, use a temp table to build the recordset (messy
from the standpoint of having to refresh the temp table for each report page
if the chart is based on a parameter which is a field on the report, or
2.) Open your stored procedure as a recordset in the report's on open event
and then use VBA to set the range values of the chart object by looping
through the rows and columns of the recordset. Again very intensive
process.

This is a real weakness of the graph object in Access 2000. Although we
haven't had enough time to play with this, possibly another method entirely
would be to use a new data page with a pivot table as a row source and the
charting object from the Office Web components ala the Sales page from the
Northwind sample db.

Have you found any other hacks on this since your original post?
--
Steve Arbaugh
ATTAC Consulting Group
http://ourworld.compuserve.com/homepages/attac-cg

Roger Dias <rd...@westeve.com> wrote in message
news:93155314...@nnrp.gt.ca...

[rdia...@my-deja.com]

Good idea but it didn't quite work. You can't
refer to a stored procedure from a select
statement but you can refer to a view. When I
tried using a view, I found I couldn't find an
event in the report that would let me modify the
chart object's RowSource (Me!chart1.RowSource
= "select statment here"). The report's 'On
Open' event happens before the chart object's
RowSource property is exposed and if you try
using the On Format event, it complains that you
can't change the property after printing has
started! It's either too early or too late.
Thanks for the suggestion though. I'll post
something if I resolve this issue.

Roger

In article <7m5op4$7...@niven.ksc.nasa.gov>,
"Bill Beardshall" <bi...@treefrogdesigns.com>
wrote:

> Can you run the paramaters against the results
of the stored procedure. In
> other words, set the row source to something
like "Select * from [stored
> procedure?] WHERE [parameters = thingies goes
here] [Group By/Order By
> Cross-tab thingies goes here]"?
>
> HTH,
>
> Bill
>
> Roger Dias wrote in message
<93155314...@nnrp.gt.ca>...

> >Access 2000 on SQL Server 7. I have a report
containing a Microsoft Graph
> >2000 Chart (MSGraph.Chart.8). The row source
for the chart is a stored
> >procedure but there doesn't appear to be any
way to pass parameters to the
> >stored procedure. A regular report does this
by using the Input Parameters
> >property but this property is missing in the
chart object.
> >
> >I could try creating a temporary table with
the result set and set the row
> >source of the chart to the temporary table but
that presents possible
> >problems with cleanup, database size, and
concurrency.
> >
> >Any suggestions? Thanks in advance.
> >
> >Roger Dias
> >rd...@westeve.com
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[rdia...@my-deja.com]

I'm trying to modify the chart's RowSource property (eg. Me!
chart1.RowSource = "select statement here") through VBA but having
trouble finding a suitable event. On Open happens before RowSource is
exposed and On Format happens after it's begun printing. Will notify
if another solution found...

Roger

In article <7maec5$9bo$1...@nntp3.atl.mindspring.net>,

[Steve Arbaugh]

Roger:

To modify the rowsource of a chart, you have to open the report in design
mode. You can't change the chart object's rowsource at run time. Therefore
your first option of using a temp table is probably the best course.

HTH

--
Steve Arbaugh
ATTAC Consulting Group
http://ourworld.compuserve.com/homepages/attac-cg

<rdia...@my-deja.com> wrote in message news:7mdv15$30n$1...@nnrp1.deja.com...