Pie Chart in Report

[MSchultz]

This is my first crack at developing reports in Access. We're using a Access
2002 project and connecting to SQL Server 2000.

I have an extremely simple subreport that contains one Pie Chart. The pie
chart needs to show two values as a percentage of the total; i.e. if Jobs
Complete = 10 and Jobs Pending = 30, then the pie chart should show 25% and
75% for each respectively.

Since I'm not familiar at all with Access I have a couple of issues I've
been trying to tackle.

1. The query that retrieves these numbers must use date parameter values
entered by the user on a form (Name = myForm).

2. The only way I've been able to get the graph to appear the way I want it
to is to return the data so it looks like a datasheet; i.e. I return two rows
the first has the series (I think) names that appear in the legend and the
second row has the values I want in the graph. I have to assume there is way
to make this work with a query that returns only the values, but I haven't
had any success with that.

3. I currently have a rather ugly solution that "sort of" works. I have a
stored proc that takes two date parameters and retrieves the data desired (in
the data-grid like format). In the Open Event of the report I set the Row
Source property of the graph = "EXECUTE spGetData '" & Forms!myForm!StartDate
& "', " & Forms!myForm!EndDate & "'". This works intermittently. Some
times I get a Run-Time 2455 which I've searched this forum for. The solution
appears to be to use a Saved Query, which I don't really have the option to
do since I'm using an Access Project.

I would be eternally grateful to anyone that can lend some guidance on what,
to me, should be an easy task. I think I'm making it harder than it needs to
be.

Mike Schultz

 

[MSchultz]

Being the "newbie" to this that I am, I believe I asked to wide-open of
questions, in my previous post. The following are much more specific and
will help me a great deal in trying to wrap up this issue.

The following questions pertain to using an Access Project (not Access
database)

1. Can I use a Inline Fuction or a Stored Procedure to retrieve data for a
Pie Chart and/or a Stacked Bar chart?

2. If so, can I use parameters in the Inline Function/Stored Proc?

3. How?

4. If not, what recommendations are there for retrieving data for the chart
in which I can use parameters (or simulated parameters)?

Thanks in advance,

M Schultz