Unable to return dataset fields?

Question

Post reply

Unable to return dataset fields?

jcrawf02

SSC-Insane

Points: 24198
More actions
March 22, 2010 at 9:39 am

#220180

Anybody have any idea why I can execute a stored procedure in SSRS, return the number of blank rows that apply to the resultset, but cannot see any fields in the dataset to add to the list?
What I've tried:
1 - The sp works in SSMS (copy/paste of the SQL to execute the sp from the report to check for syntax errors), returns the data I'm expecting, and I believe that it is returning values to the report somewhere, since I get the same number of blank rows in my list as I do results in SSMS. Ergo, sp is not the problem, nor is the code to execute the sp
2 - However, when I try to expand the dataset to drag/drop into the list field, nothing shows. Refreshing the dataset does not help.
3 - If I try to hardcode the list values by (3a) associating the list with a dataset in properties (since I have multiple datasets in the report) and (3b) specifying that I want the detail to, for example, =Fields!ProviderNumber.Value, I get the following error:
"The Value expression for the textbox ‘textbox7’ refers to the field ‘ProviderNumber’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
So, if I can't get fields back, I can't drag/drop, but if I can't drag/drop, apparently I can't get values by calling what I know the fields to be. And yet my completely empty rows match the correct number of records in the (desired) output?
Anyone have a suggestion for this Catch-22?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

with stored procs, it's a best practice to add SET NOCOUNT ON as the first line of the proc;

this prevents other applications from thinking subsets or commands like (x row(s) affected) are recordsets.

add that and let us know if you are still having trouble with the proc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Thanks Lowell, I'll keep that in mind.

Think this was actually a scope issue, I was using sp_executesql within my sp, which I realize I don't have to do, was learning it at the time and hadn't re-written it yet.

When I was just executing the sp with null parameters to return everything, it worked great, but when defining a value, my dynamic sql was puking because I was referencing variables that didn't exist in the scope being executed.

Re-write seems to work, so I'm good, returns values to the dataset and everything.

Thanks,

Jon

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."