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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial