INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

base a subreport off a parameterized sp by evilmousse
Posted: 11 Jun 03


  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

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close