SSRS Report with Multiple Datasets Using Stored Procedure

Posted on 2014-04-24
Last Modified: 2014-04-28
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!
Question by:KANEDA 0149
  • 4
  • 3
LVL 27

Expert Comment

ID: 40022710
I would change @Portfolios to something else. The name does not matter you are still passing the same data between the datasets.

Author Comment

by:KANEDA 0149
ID: 40022729
I will try that, thank you!

Author Comment

by:KANEDA 0149
ID: 40023334
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
LVL 27

Expert Comment

ID: 40023949
You have to set @Portfolios first.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

by:KANEDA 0149
ID: 40023985
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.
LVL 27

Assisted Solution

planocz earned 250 total points
ID: 40023998
the two datasets are standalone SP's. You have to set the second dataset to recieve the parameter answer from the first dataset.

Author Comment

by:KANEDA 0149
ID: 40025224
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!
LVL 37

Accepted Solution

ValentinoV earned 250 total points
ID: 40026706
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.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Storage Spaces 3 38
testing sql16 on win10 vs OS16 2 34
Sql query 107 24
SQL Server merge records in one table 2 11
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
Send secure, cloud-based, encrypted alerts and maintain HIPAA compliant messaging. Integrates priority & secure messaging into one application. Ensures IT, emergency respondents and healthcare professionals that their critical messages are never mis…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now