SSRS - Dataset refreshes with wrong fields

sath350163
sath350163 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005
Commented:
Write 2 procedures and stop trying to cheese it :-)

Author

Commented:
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!
Brian CroweDatabase Administrator
Top Expert 2005
Commented:
It makes no difference in this case it will still be creating 10 different execution plans.  You have just condensed them into a single procedure.

To be honest I don't know why SSRS is behaving strangely on this one.  I would expect it to work but you can spend a few hours trying to trouble-shoot and figure out what's going on or you can spend 20 minutes copy/pasting and adjusting your SSRS definition.

It just depends whether your curiosity is more powerful than your need to accomplish the task in a timely manner.  When I get back to work on Monday I might spend some time playing with the scenario myself to see if I can reproduce it.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Agree with Brian.  SSRS, like SSIS, requires a 'contract' between source data and destination data set in order to validate correctly, and when the schema can change (table summary, detail) based on a parameter @output it forces a re-mapping, which may or may not work correctly.

Far better to split this into different SP's, one for details, one for summary.

As an aside, explain what's in the 'summary' and 'detail' return sets, as there is likely a far better way to pull this off.

Author

Commented:
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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>details result set is a direct SELECT from detail table, and Summary result set is a direct SELECT from summary table
You may be missing the point.  Script out the CREATE TABLE statement for both Summary and Detail tables, and post it into this question.  If they are different other than the name, that is going to cause SSRS to re-validate every time and possibly fail.

One of SSRS's greatest strengths is the ability to accommodate multiple datasets, so you *should* be able to create two data sets, one each for summary and detail, then map to both, and never have to worry about remapping errors.

Also, mapping to a query that is a 'SELECT *' is not a best practice either, as any added columns will cause remapping, and any deleted columns that the SSRS reports depends upon would throw an error.   AND do you really need every * column and row?  Remember, the more columns/rows you ask for = longer processing time = longer users stare at a blank screen until the report generates.

>I was thinking that it would be easier to maintain if the code was in a stored procedure instead of directly on the report.
In general this is correct, as it is far easier to do impact analysis (i.e. if we change x, what would break?) if all code was in the database as opposed to 100ish SSIS and SSRS files out there.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial