SSRS Report with Multiple Datasets Using Stored Procedure

I have an SSRS report with 1 dataset using the following stored procedure passing the following required parameters.  With just the 1 dataset, the report works perfectly.


DataSet1 SP Query
EXEC [UserCustom].[FAS_pReportAppraisal_RiskRollup] 
@Portfolios = @Portfolios, 
@Date = @Date,
@ClassificationID = @ClassificationID,
@IncludeUnsupervisedAssets =@IncludeUnsupervisedAssets

Open in new window


When I try to add a new DataSet2 with another stored procedure using similar required parameters, I am getting the following error when I select the EXECUTE icon to view my new DataSet2 saying 'Error Message: Must declare the scalar variable "@Portfolios".

DataSet2 SP Query
EXEC [UserCustom].[pPerformanceHistory_KW] 
@Portfolios = @Portfolios, 
@Date = @Date, 
@Period1 = @Period1, 
@Period2 = @Period2, 
@Period3 = @Period3, 
@FeeMethod = @FeeMethod

Open in new window


I don't understand why I can't create a 2nd DataSet.  The reason why I want to create another dataset is to use the LOOKUP function based on a field called "MemberID" in DataSet1 with "ClassificationMemberID" in DataSet2.  I am using SSRS 2008 R2.  Thanks!
KANEDA 0149Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Is Portfolios a report parameter?  You can re-use the same report parameters and pass them into both SPs, that should work fine.  But you do have to ensure the correct mapping.

Perhaps it's more convenient to not use EXEC but to select the Stored Procedure radio button as Query Type.  This is located in the Query page of the Dataset Properties.  You can then just use the dropdown to select your SP and use the Parameters page to link the SP parameters to the report parameters.
0
 
planoczCommented:
I would change @Portfolios to something else. The name does not matter you are still passing the same data between the datasets.
0
 
KANEDA 0149Author Commented:
I will try that, thank you!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
KANEDA 0149Author Commented:
Hi planocz, I changed the parameters to something different but I still get the darn SQL Execution Error message (see below).
DataSet2 SQL Execution Error
0
 
planoczCommented:
You have to set @Portfolios first.
0
 
KANEDA 0149Author Commented:
Hmmm.... I thought I did set @Portfolios in the first stored procedure for DataSet1.  

Sorry, I'm a newb.  Both datasets are complicated stored procedures which can not be combined or if combined might be too much for a single report to run.
0
 
planoczConnect With a Mentor Commented:
the two datasets are standalone SP's. You have to set the second dataset to recieve the parameter answer from the first dataset.
0
 
KANEDA 0149Author Commented:
So I have to modify the 2nd DataSet parameter to pull from DataSet1 paramaters.  Is that correct?  If so, sorry how do you do that?  Thank you planocz!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.