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

Multiple datasets vs multiple subreports? Expand / Collapse
Author
Message
Posted Monday, June 6, 2011 2:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 316, Visits: 1,488
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



Post #1120582
Posted Saturday, January 25, 2014 7:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 11:15 AM
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.
Post #1534764
Posted Monday, January 27, 2014 8:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 316, Visits: 1,488
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



Post #1535068
Posted Monday, January 27, 2014 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 11:15 AM
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!
Post #1535123
Posted Monday, January 27, 2014 2:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 316, Visits: 1,488
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



Post #1535219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse