SSRS report and multiple datasets

Question

Post reply

SSRS report and multiple datasets

jignesh209

SSC Eights!

Points: 959
More actions
January 23, 2014 at 10:17 pm

#303698

I need to create a report which will have 6 Tables (Tablix or Grid).The data to be displayed in the 6 tables is discrete.
I cannot use a single stored procedure to get all data required to populate these tables.
So,I need to create 6 different stored procedures and bind it to the respective datasets.
Is this the right way or is there a better way with which it can be done?

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

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

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 1

xsevensinzx

One Orange Chip

Points: 25560

January 24, 2014 at 7:27 am

#1683126

You mean that SSRS is blocking you from this attempt?

jignesh209 SSC Eights! Points: 959 More actions · Answer 2

SSRS is not blocking,but is it a good practice to create 6 different Datasets and tying it up with 6 different stored procedures.

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

There is no harm in doing that, with a couple of provisos:

Be aware of potential performance issues - each query will run one at a time; they are not submitted in parallel.

And the data that is retrieved may be inconsistent - for example if an update is run between the first and last query.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

pietlinden SSC Guru Points: 63425 More actions · Answer 4

Just wondering, but can you force the queries to run in a single transaction, so they all execute and then the report is rendered? (Sorry to hijack the post a little bit!)

Pieter

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

jignesh209 (1/24/2014)
SSRS is not blocking,but is it a good practice to create 6 different Datasets and tying it up with 6 different stored procedures.

There is nothing wrong with that at all; this is the standard practice. I have had a few jobs that included writing SSRS reports and it is not uncommon to see multiple datasets each with their own stored procedure.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

pietlinden (1/24/2014)
Just wondering, but can you force the queries to run in a single transaction, so they all execute and then the report is rendered?

If there are parameters (especially cascading parameters) this would not work. With a parameterized report the process is often: proc retrieves parameter values from db... user selects parameter value... if another parameter is present the first value is passed to next parameter... etc... then the final parameter value(s) are passed to another proc that provides the final result set that is used when the report is rendered. There would be no logical way to make this happen in one transaction.

Even if it were a report without any parameters, what would be the point of running multiple queries in a single transaction?

Edit: minor spelling error.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63425 More actions · Answer 7

I was just wondering how you could get all the datasets filled in such a way that they would be accurate as of a point in time, instead of having them run in sequence and the start times would all be staggered. (Maybe I'm thinking too hard.)