June 6, 2011 at 2:05 pm
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
January 25, 2014 at 7:29 pm
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.
January 27, 2014 at 8:54 am
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
January 27, 2014 at 11:01 am
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!
January 27, 2014 at 2:50 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply