Solved

SSRS Report with Multiple Datasets Using Stored Procedure

Posted on 2014-04-24
9
4,194 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
length of the password hash sha1:64000 to set sql field property. 13 66
SSRS 2016 Rendering HTML tables 3 31
SQL query and VBA 5 46
TSQL convert date to string 4 37
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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