SSRS - Dataset refreshes with wrong fields


 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

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

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

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

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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Write 2 procedures and stop trying to cheese it :-)
sath350163Author 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?


Brian CroweDatabase AdministratorCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sath350163Author 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.
Jim HornMicrosoft SQL Server Data DudeCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sath350163Author 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.

Jim HornMicrosoft SQL Server Data DudeCommented:
>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.
sath350163Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.