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