Reporting Services - Using Sproc as Dataset

Question

Post reply

Reporting Services - Using Sproc as Dataset

Rich Parsons

SSC Veteran

Points: 268
More actions
August 2, 2007 at 6:18 am

#177761

I'm using a stored procedure as my dataset and when I add the fields to the report layout - only the first row of data is being returned. I have tried to remove the 'First' function which, for some reason, has been automatically added to each field in the dataset. Then I just get the last row of data returned by the sproc.
Does anyone know of a way around this problem? It just seems that I can't return more than one row of data from a stored procedure dataset.
Thanks, in advance....
Rich

Viewing 7 posts - 1 through 6 (of 6 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi There,

When you are setting up your dataset and you execute it from teh datasets tab. How many rows do you get?

If you get the number you expect then I suggest checking to see if you have any filters on your data on the report.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Rich Parsons SSC Veteran Points: 268 More actions · Answer 2

Hi Chris,

Yes, I'm getting the correct number of row when I exec the sproc from the tab. There's no filter at all, except for the parameter chosen at run-time. It's strange that when I drop the fields onto the report, they are all preceded with First() and I only ever get one row. Any other ideas?

Thanks

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Hi Richard,

The First is what is causing it you need to start a new report and either add your fields manually through expression in the tables details section or try and change First to Field instead because First is a function.

I had a 165 pages report got the data from the stored proc and First ate it so manually added my fields and got my data. When a Report acts funny I create a new one and delete the old one.

Kind regards,
Gift Peddie

Sameer Sait A SSCommitted Points: 1556 More actions · Answer 4

Hi Richard,

Can you recheck whether you are putting the fields in the details row itself. You might have accidently put in header or footer.

Also do check whether the table is mapped to the dataset that you have created.

It might be also that the table is been binded to a different dataset.

These are the possible things that i remember might create the problem.

Thanks -- Sameer

Rich Parsons SSC Veteran Points: 268 More actions · Answer 5

Thanks for all your replies and suggestions. The solution was, as it were, under my nose. I was using a text box, rather that a table object. Doh!

It now all works fine. This is my first foray into RS so I'm pretty happy.

Cheers,

Richard

Brian McDonald Valued Member Points: 54 More actions · Answer 6

Richard,

You may also try to add your fields into a list object. This will produce all of the records as well and will give you the ability to make the rows not in table format. For example if you wanted to print say an address book you could add the fields to a listbox as below:

FirstName LastName

Address

City, State ZIP

Take care,

Brian K. McDonald

mcdonaldb@dreamsbeginhere.org