base a subreport off a parameterized sp

I searched for a month or so before I found
the solution to this, so in the interests of
helping those to try this later, I'm posting
what I've found out.

Ordinarily, basing a subreport off a stored
procedure will only work if the recordsource
is the sp name. using "exec <spname>" will
fail when opened by the main report, despite
the subreport appearing to work when opened
on it's own. Further, I found no successful
way to filter the subreport's data more than
one way (troublesome if the subreport is
called more than once on different data).
Link master/child fields doesn't succeed
with stored procedures as the recordsource.
Lastly, because "exec <spname>" doesn't work,
this makes it hard if not impossible to pass
any parameters the sp might have.

so what's a poor boy looking to constrain
a subreport's data set to do?

The trick is this:
base the subreport off of a sp where the
parameters have NO default values. When
you open the subreport on it's own, it
should pop up a dialog box for manual
entry of the missing parameter (much like
in a jet engine when a query has a param).
When the subreport is opened by the main
report, the subreport will attempt to
populate the missing parameters using the
main report's dataset, should the field
names be the exact same.
(Should they not be the same, apparantly
using the new report open arguments feature
will help, but I've not tested this.)

Here is an example where I have a main
report query and a subreport query in the
same stored procedure, differentiated by
an int argument (ususual and slightly convoluded
in this particular case; I've had a running goal
to keep all the logic for each report together
in one place.)


--------begin sp-------------
ALTER PROCEDURE ProcLockRpt (
@qtype int = 0,
@FromDate datetime,
@ThruDate datetime,
@sales_region varchar(2)
) AS
SET NOCOUNT ON

IF (@qtype = 0) BEGIN
/*sums info by region, client, product_code*/
SELECT dbo.t_client.sales_region, dbo.t_client.client_id,
dbo.t_client.c_name, dbo.t_loan.product_code, SUM(dbo.t_loan.l_orig_loan_amt) AS sumOrigLnAmt, COUNT(dbo.t_loan.l_orig_loan_amt) AS cntLoans, @FromDate as FromDate, @ThruDate as ThruDate, 10 as qtype
FROM <omitted>
WHERE dbo.t_loan.l_dt_lock between @FromDate and @ThruDate
END

IF (@qtype = 10) BEGIN
/*sums info by region, product_code*/
SELECT TOP 100 PERCENT dbo.t_loan.product_code,
SUM(dbo.t_loan.l_orig_loan_amt) AS sumOrigLnAmt, COUNT(dbo.t_loan.l_orig_loan_amt) AS cntLoans, dbo.t_client.sales_region
FROM <omitted>
WHERE (dbo.t_client.sales_region = @sales_region)
and (dbo.t_loan.l_dt_lock between @FromDate and @ThruDate)
END
SET NOCOUNT OFF
--------end sp-------------

The last 3 fields in the main report query,
fromdate, thrudate, and qtype, exist strictly
so that the main report has fields to match the
parameters the subreport will request.

The reports have the following vba:

main report:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "exec ProcLockRpt 0, '" & Forms!fdlgLock!txt_BeginDate & "', '" & Forms!fdlgLock!txt_EndDate & "', null"
End Sub

subreport:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "ProcLockRpt"
End Sub

The subreport then draws in the qtype,
fromdate, thrudate, and sales_region
parameters from the main report's dataset,
and correctly displays summary information
for each sales_region.

Using a separate sp for the subreport
would be clearer, but I hope this
demonstrates the concept well enough.

-g