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 AdventureWorksGOCREATE PROCEDURE dbo.usp_get_data(@output VARCHAR(50))ASBEGIN IF @Output = 'details' BEGIN SELECT * FROM AdventureWorks.dbo.details; END IF @Output = 'summary' BEGIN SELECT * FROM AdventureWorks.dbo.Summary; ENDEND
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?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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.
Unlimited question asking, solutions, articles and more.
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
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!