August 25, 2009 at 10:11 am
Hi,
I have a stored procedure that generates different output datasets based on the parameter, but SSRS 2008 appears to be populating the column list from the first Select regardless of logic within the stored procedure and regardless of which parameter I feed it.
It needs to just execute the SP and run with what comes back, not evaluate the code behind the SP and try to guess what I want it to do, which it's doing incorrectly. I've tried setting-up the dataset using Text hard coding the parameter and also as Stored Procedure but same results either way. And I was able to verify SSRS is returning the first Select because i changed the order of the Selects and it changed my columns in the report.
Something else is if I open Query Designer it shows the correct dataset from the stored procedure, but clicking OK then Fresh Fields still shows the wrong dataset.
I'd really rather not split out the logic because it just doesn't make since to do so. I've done this before in other versions of SSRS, so does anyone know of a work around for this bug?
Thanks...
Sam Alex
August 25, 2009 at 10:28 am
Hi Everyone,
I found a 'solution' that works, but it's crummy I have to do this. I ended up bundling my SQL statements in to a variable and executing those variables at the end to make the report work. I guess if it doesn't find a valid Select it just executes it and runs with what comes back, which is what it should be doing from the start.
Still, any suggestions on how to avoid using Dynamic SQL to make this work? It's better then splitting the logic into multiple stored procedures, but still not what I hoped to do.
Thanks --
Sam Alex
August 26, 2009 at 7:39 am
SSRS stores the names of the columns as part of the report definition (rdl file), that's the only way it can know what to display where in the report itself. So if you save the report with the columns FirstName, LastName, and BirthDate, those are the only columns the report itself knows about. SO if you change the parameter and the stored procedure now returns CompanyName, ContactName, PhoneNumber the report can't know about those columns because they are not stored in the report definition (rdl file). If I were doing this I would probably write the stored procedure so that it always returns ALL the columns. In my example I would always return FirstName, LastName, BirthDate, CompanyName, ContactName, PhoneNumber, but the unused columns would be NULL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2009 at 9:28 am
Jack Corbett: SSRS stores the names of the columns as part of the report definition (rdl file), that's the only way it can know what to display where in the report itself. So if you save the report with the columns FirstName, LastName, and BirthDate, those are the only columns the report itself knows about. SO if you change the parameter and the stored procedure now returns CompanyName, ContactName, PhoneNumber the report can't know about those columns because they are not stored in the report definition (rdl file). If I were doing this I would probably write the stored procedure so that it always returns ALL the columns. In my example I would always return FirstName, LastName, BirthDate, CompanyName, ContactName, PhoneNumber, but the unused columns would be NULL.
Hi Jack,
Returning all rows is just as muddy as using dynamic SQL which is the best way I've found to make this work. This procedure will be used for processes outside of reporting, so I need the output to be clean.
My main gripe is that SSRS should see the output of the procedure, including parameters, and run with that instead of trying to get the column names from the procedure itself since the desired output wouldn't be known until it executes with parameters.
Thanks for the reply and take care --
Sam Alex
August 26, 2009 at 9:33 am
How would you suggest MS implement "dynamic" creation of columns within a dataset? At some point the engine has to know that FirstName goes in column1 if it is returned, but CompanyName goes in column1 if that is returned.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2009 at 4:38 am
Please correct if i am wrong. both select statements returns same number of fields and the names of the fields are also the same but the no of records would be different from both selected statemets. if this is the case i really think it is some issue with the parameter value being checked. try returning the condition output from the SP. because SSRS doesnt do anything in SP execution logic.
September 6, 2009 at 1:50 am
Hi!!
hers the solution which seems to be working for me
Scenario- I had the stored procedure with IF ELSE in it which returned multiple result set depending on Input parameter.
i had returning four colunms for each result set for (daily,monthly,weekly,quaterly,yearly)
eg: DAILY....ACTIVE...CLOSED... SUSPENDED colunms for daily parameter grouped by daily
Problem-the dataset returnrd only for the first IF condition in SSRS designer eg-DAILY(excluded the the other but the results were showed along with BLANK values for 1 st colunm)
SOlution- create query in Desiner SSRS which returns table variable
the table variable will contain the results by excecuting main stored proc.
eg-
DECLARE @DATE TABLE (
PERIOD NVARCHAR(20),
ACTIVE int,
CLOSED int,
SUSPENDED int
)
if(@Frequency='DAILY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
else if(@Frequency='MONTHLY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
else if(@Frequency='WEEKLY')
begin
INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)
exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency
select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE
end
this one works for me perfectly fine
Thanks & regards
Satyajit
April 25, 2016 at 6:52 pm
Hi Every one, Though this sounds a late response to a 6 years old question, I happen to feel it is beneficial to respond a best answer as others might have referred this website for their day to day need. in short the answer to this is fake SSRS by creating your stored procedure with a hint SET FMTONLY OFF. This is a dramatic trick.
July 1, 2016 at 6:33 pm
Using SET FMTONLY OFF does not work.
Some suggest using Exec under text in the dataset properties.
But doing this way won't allow me to pass parameters.
So if anyone has a solution for either of this, I would be really appreciated:
1) Have the stored procedure return the fields
2) Using the workaround of Exec, how do I pass parameter?
I have google everywhere.
This suggestion below does not work:
samuel.bayeta (4/25/2016)
Hi Every one, Though this sounds a late response to a 6 years old question, I happen to feel it is beneficial to respond a best answer as others might have referred this website for their day to day need. in short the answer to this is fake SSRS by creating your stored procedure with a hint SET FMTONLY OFF. This is a dramatic trick.
May 25, 2018 at 4:19 pm
I found that if I open the dataset properties I saw the incorrect fields. I deleted these fields and replaced them with the fields I was expecting instead and got the results I was looking for.
November 8, 2019 at 7:47 pm
Hello All, I have had the same scenario and found some work around for the issue. Just adding here as it might be a reference.
I have IF & ELSE statement in my stored procedure and both returning different set of fields and SP will return the fields based on the parameters passed.
SSRS Reports data set was showing the IF clause SELECT fields and not showing the ELSE clause SELECT fields initially.
I have added the ELSE clause SELECT fields manually to the SSRS data set and the issue fixed. Ignore if any FIELD name is same on both clauses. After this, these fields can be used in the reports without any issue.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply