Avatar of sath350163
sath350163
 asked on

SSRS - Dataset refreshes with wrong fields

Hello,

 I have a SSRS report containing two data sets which calls a stored procedure to get the data.
 The Stored procedure code is something like below:

USE AdventureWorks
GO

CREATE PROCEDURE dbo.usp_get_data
(@output VARCHAR(50))
AS
BEGIN

	IF @Output = 'details'
	BEGIN
		   SELECT * FROM AdventureWorks.dbo.details;
	END

	IF @Output = 'summary'
	BEGIN
		 SELECT * FROM AdventureWorks.dbo.Summary;
	END
END

Open in new window


In SSRS, one data set is for summary and another one is for details.
In the data set,  I have hardcode the @output = 'details' for data set 1, and hardcoded @output = 'summary' for data set 2.

When I refresh data set 2 which is for summary, SSRS refreshes the dataset with details fields instead of the summary fields. I think it is doing that because details IF block is the first one in the code.

Can you please help me in fixing this issue and get SSRS wit refresh with correct list of fields?

Thanks!
SSRSMicrosoft SQL Server

Avatar of undefined
Last Comment
sath350163

8/22/2022 - Mon
SOLUTION
Brian Crowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sath350163

ASKER
Thanks Brian. So does that mean that SSRS cannot handle/work with the approach that I'm trying to do. But it works fine when I execute it from SSMS.

Can you please clarify the reason on why SSRS cannot handle this approach?

In the real stored procedure, I have 10 IF blocks. So I would have to end up creating 10 stored procedures and feel like I may be polluting the database with too many object.

Is there any other work around apart from creating separate stored procedures?

Thoughts?

Thanks!
SOLUTION
Brian Crowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sath350163

ASKER
Thanks Brian.
I have already spent a lot of time trying to troubleshoot this behavior and have given up now.
For now, my only option is to create separate stored procedures.
Please let me know your findings.
Thanks for the response.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sath350163

ASKER
Thanks Jim.
Luckily, details result set is a direct SELECT from detail table, and Summary result set is a direct SELECT from summary table. So for atleast this report, I can just include the SELECT statement directly in the dataset as adhoc query. I was thinking that it would be easier to maintain if the code was in a stored procedure instead of directly on the report.

Thanks.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sath350163

ASKER
Thanks