Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS report and multiple datasets Expand / Collapse
Author
Message
Posted Thursday, January 23, 2014 10:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:20 PM
Points: 24, Visits: 182
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?
Post #1534340
Posted Friday, January 24, 2014 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 84, Visits: 424
You mean that SSRS is blocking you from this attempt?
Post #1534489
Posted Friday, January 24, 2014 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:20 PM
Points: 24, Visits: 182
SSRS is not blocking,but is it a good practice to create 6 different Datasets and tying it up with 6 different stored procedures.
Post #1534541
Posted Friday, January 24, 2014 9:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
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
Post #1534553
Posted Friday, January 24, 2014 9:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 839, Visits: 5,423
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
Post #1534560
Posted Friday, January 24, 2014 2:55 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 612, Visits: 2,855
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1534664
Posted Friday, January 24, 2014 3:04 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 612, Visits: 2,855
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1534668
Posted Saturday, January 25, 2014 8:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 839, Visits: 5,423
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.)
Post #1534765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse