Solved

SSRS Report with Multiple Datasets Using Stored Procedure

Posted on 2014-04-24
9
4,284 Views
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!
0
Comment
Question by:KANEDA 0149
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 27

Expert Comment

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

Author Comment

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

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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:planocz
ID: 40023949
You have to set @Portfolios first.
0
 

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.
0
 
LVL 27

Assisted Solution

by:planocz
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.
0
 

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!
0
 
LVL 37

Accepted Solution

by:
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.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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