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.
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".
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!
DataSet1 SP Query
EXEC [UserCustom].[FAS_pReportAppraisal_RiskRollup]
@Portfolios = @Portfolios,
@Date = @Date,
@ClassificationID = @ClassificationID,
@IncludeUnsupervisedAssets =@IncludeUnsupervisedAssetsWhen 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 = @FeeMethodI 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!
Howard Cantrell
I would change @Portfolios to something else. The name does not matter you are still passing the same data between the datasets.
ASKER
I will try that, thank you!
ASKER
Hi planocz, I changed the parameters to something different but I still get the darn SQL Execution Error message (see below).
You have to set @Portfolios first.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.