SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS report and multiple datasets


SSRS report and multiple datasets

Author
Message
jignesh209
jignesh209
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 185
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?
xsevensinzx
xsevensinzx
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 2696
You mean that SSRS is blocking you from this attempt?
jignesh209
jignesh209
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 185
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
Richard Fryar
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 1172
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
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
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.B
Alan.B
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5124 Visits: 7708
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5124 Visits: 7708
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
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.)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search