Solved

SSRS Report with Multiple Datasets Using Stored Procedure

Posted on 2014-04-24
9
4,105 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
 
LVL 27

Expert Comment

by:planocz
ID: 40023949
You have to set @Portfolios first.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

21 Experts available now in Live!

Get 1:1 Help Now