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.
Talk To Other Members