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


Multiple datasets vs multiple subreports?


Multiple datasets vs multiple subreports?

Author
Message
peterzeke
peterzeke
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1766
Just wondering if anyone has a list of pros and cons to using multiple datasets vs multiple subreports in a report.

I recently developed a patient profile report that pretty much includes every health data nugget we have (demographics, pharmacy history, lab results, claims data for costs and utiiization by claim type, service providers seen, case managent history...). I took the approach of using multiple datasets where each dataset is supported by a specific stored procedure.

But, I've had this nagging feeling about whether using subreports could be more advantageous -- not necessarily in terms of performance, but rather as building blocks for any other types of reports that might be able to consume any of the subreports.

So, I'd like to hear from other report developers whether one technique is better than the other or if it merely "depends."

--Pete



homebus
homebus
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
Did you ever get any responses to this? I would be interested in hearing how this ended up as I have pretty much the same type report.

Don.
peterzeke
peterzeke
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1766
Hi Don:

Congratulations, you're the first person to respond in 2.5 years! I'd forgotten that I had ever posted this question on SSC.

Since my posting, I've continued to maintain the patient profile report using the multiple datasets approach (which essentially means having multiple data regions). Performance is still very good. I never did test an approach using subreports, probably because the multiple datasets performed well and maintaining the report has been fairly easy.

Interestingly enough, I haven't come across much of a need to build subreports that could be included in different reports -- i.e., "build once, use everywhere". The concept is somewhat interesting, I suppose, but one of the "gotchas" of subreports is that they take on the page layout of the master/container report.

--Pete



homebus
homebus
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
Hi Pete,
I have a specific problem that I'm trying to solve and based on your post you may have faced this same situation.
I have a report with a very similar design as your Patient Profile report. Meaning that in a single report I have several tables each with it's own stored proc. The tables are logically grouped into modules and I allow the user to choose which modules they want to see via checkboxes that hide or display the associated module. My issue is that when the report is executed it runs ALL modules in the background but only displays the one the user selects. So if the user choses to view say module 2,5, & 7 only those modules will display to the user but the report is still executing the stored procedures for all modules in the background. Performance is good but clearly it would be ideal if only the selected modules are executing their associated stored procedures instead of all modules executing all stored procedures every time. I'm trying to figure out if it's possible to only execute the selected modules (conditional execution) but I haven't found a straightforward way to make this happen.

I would love to hear your thoughts or experiences with this situation.
thx!
peterzeke
peterzeke
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1766
Hmmm... Yeah, running queries that don't need to run isn't quite ideal. Got it.

Although I haven't had a need to control, dynamically, whether a dataset needs to be returned, I have an idea that I've just briefly tested, successfully. I presume you are using a multi-valued parameter to allow users to select various modules to display, right?

Here's what I'm thinking. Change a given dataset's command type from Stored Procedure to Text so that you can explicitly call a sql command to execute a stored procedure based on whether a particular value exists in a multi-value parameter.

1) To better explain, in my test (using Northwind database) I created a single multi-value parameter called "prmModules". I populated the choices with "Customers" (C) and "Orders" (O).

2) I then created a dataset, called dsCustomers, and set the Command to Text.

3) I ran a simple query (Select top 10 CustomerID from Customers) so that SSRS would know what the dataset (i.e., fields) would contain.

4) I then changed the sql of the dataset to the following conditional situation (see below):

/* Test if this dataset should be populated based on the multi-value parameter */

IF EXISTS ( SELECT 1 WHERE 'C' IN ( @prmModules ) )

BEGIN
/* Run a SQL statement or call a stored procedure here */
SELECT TOP 10
CustomerID
FROM northwind.dbo.customers
END
ELSE
/* Don't populate dataset since a key parameter value was not selected */
BEGIN
Return
END



As you can see above, the Select statement won't run unless the parameter contains a "C". I believe you could adopt this approach for each of your datasets, and in so doing, end up executing only the relevant stored procedures.

What do you think?

--Pete



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