Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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:
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!
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
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!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.
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!
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!
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.
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.
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.
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.
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.
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
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.
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.
>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.
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.
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
SSRS
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Inter… 277 lessons
-
Business CommunicationCertification: PMI ACP Project Management Institute Agile Certified Pra… 283 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2016… 224 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.