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!

SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sath350163

ASKER

Thanks