Subreport's RecordSource

[Todd]

I am baffled by Access's inability to show related records
when the subreport's RecordSource is a stored procedure.
Maybe someone here can shed some time on the subject for
me.

Here's what I have:
My main report's RecordSource is a stored procedure which
returns a recordset including the primary key. This works
great by itself as I use stored procedures almost
exclusively as report recordsources.

Next I have added a subreport to the main report. Its
recordsource is also a stored procedure which returns a
recordset including a primary key that matches the main
report's primary key. I have also included both primary
key fields on both reports. The Link Child Fields and Link
Master Fields have been set appropriately.

When I open the main report the subreport shows ALL the
records for each subreport for each record instead of each
sub-record for each main-record. (O.K. That's not too
clear of a sentence.)

It looks like this:

Main Report Record 1
Sub Report Record 1
Sub Report Record 2
Sub Report Record 3
Main Report Record 2
Sub Report Record 1
Sub Report Record 2
Sub Report Record 3
Main Report Record 3
Sub Report Record 1
Sub Report Record 2
Sub Report Record 3

I want it to look like this:

Main Report Record 1
Sub Report Record 1
Main Report Record 2
Sub Report Record 2
Main Report Record 3
Sub Report Record 3

Now I have made a mock view of my stored procedure for the
subreport and it works correctly. But when I use the
stored procedure, it doesn't. Unfortunately, I cannot use
a simple view in my subreport. I contains parameters and
calls to other procedures in order to render it's
recordset.

Again, I have set the Link Child Fields and Link Master
Fields appropriately, checked for mispellings, etc. This
was my first thought when I ran into this.

 

[Vadim Rapp]

T> I am baffled by Access's inability to show related
T> records when the subreport's RecordSource is a
T> stored procedure. Maybe someone here can shed some
T> time on the subject for me.

This is quite natural.

Suppose the recordsource of the subreport is

select column from table

And you have specified parent and child fields for the subreport.

What Access does is, it modifies the recordsource of the subreport, changing
it into

select column from table where childfield = parentvalue *

Access in fact has to parse the sql; for example it might include ORDER BY
clause, then Access would have to insert its WHERE before ORDER BY.

Or, easier, Access could construct the following sql:

select * from (select column from table) where childfield = parentvalue


Now, if the recordsource of the subreport is stored procedure, Access can't
do anything. The s.p. can be very complex, and it's unrealistic to think
that Access could parse it and modify with WHERE in the right place. Stored
procedure can't be used as subselect either, so SELECT * FROM (EXEC
MYSTOREDPROCEDURE) WHERE ... wouldn't work.

As you can see, Access can't apply the criteria to the stored procedure.

You may want to reconsider your habit of "I use stored procedures almost
exclusively as report recordsources", in case your stored procedures are
mere sql statements. In sql server 2000, stored procedures don't have any
real performance advantage over the plain sql.


Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

*) this is quite simplified. In fact Access usually uses sp_executesql. It
may be interesting to run Profiler and see how it works and what Access is
sending to the server.

 

[Todd]

Thanks for the insite into Access's inner workings. I was
able to make it work with views just fine. I was just
trying to return as few of records as possible, limiting
the underlying recordsets of the subreports. It looks from
your explanation that access is already doing this for me.