As @StevenWhite pointed out, you probably need to rethink your approach but if you really want to do this.
You need to add an additional parameter to your report (you can hide it once it's all working OK)
The dataset for this parameter would be your existing dynamic sql code, but just the WHERE clause part, so hte end of the dataset query just do something like SELECT @where.
So, this new parameter will be populated once the other parameters have been populated and it's value will be your where clause. You can then pass that as a parameter to your other datasets where applicable.
If that doesn't make sense, let me know and I'll do a more complete answer soon.
More Complete Answer
In this example I've used hte Northwind sample database
I show how to generate a WHERE clause that can be used in another dataset (or as many as you like). In this exmaple I'll just do it with one.
I will have two parmeters for the where clause selections
- A List of ProductID
- A List or EmployeeID
Our final dataset query will be dynamic sql that forms the statement something like this..
SELECT
o.*
, d.Discount, d.ProductID, d.Quantity, d.UnitPrice
FROM Orders o
JOIN [Order details] d on o.OrderID = d.OrderID
WHERE ProductID in (11,42,72) and EmployeeID IN (3,5,6)
Heres the steps I took:
Created a new blank report
Added a conncetion to the Northwind database
Created a dataset called dsProd
Set the query for this dataset to be SELECT ProductID, ProductName FROM Products ORDER BY ProductName
Created a dataset called dsEmployee
Set the query for this dataset to be SELECT EmployeeID, FirstName FROM Employees ORDER BY FirstName
Added a parameter called pProd
Set the parameter to be Mutil-value
Set the available values to the dsProd dataset
Set the Value field to ProductID
Set the Label field to ProductName
Added a parameter called pEmp
Set the parameter to be Mutil-value
Set the available values to the dsEmployee dataset
Set the Value field to EmployeeID
Set the Label field to FirstName
Added a final parmater called pWHERE
Set the default value (Specify values) for this to the following Expression
="WHERE ProductID IN (" & Join(Parameters!pProd.Value, ",") & ") " &
" AND EmployeeID IN (" & JOIN(Parameters!pEmp.Value, ",") & ")"
Next added a datset called dsResults
Set the dataset Query to
DECLARE @SQL varchar (1000)
SET @SQL = 'SELECT
o.*
, d.Discount, d.ProductID, d.Quantity, d.UnitPrice
FROM Orders o
JOIN [Order details] d on o.OrderID = d.OrderID '
+ @pWHERE
EXEC (@SQL)
Finally I added a table to the report pointing to dsResults to display the output.
Now, when you choose the employees and products, the where clause is constructed in the pWHERE parameter and passed to the final query's dataset.
NOTE: Going back to my original point, reiterating what @StevenWhite was saying, all this is probably unneccessary. In this simple case you could have simply set the final dataset query to
SELECT
o.*
, d.Discount, d.ProductID, d.Quantity, d.UnitPrice
FROM Orders o
JOIN [Order details] d on o.OrderID = d.OrderID
WHERE ProductID in (@pProd) and EmployeeID IN (@pEmp)
This would do exactly the same job, it would be quicker, you would not need the pWHERE parameter at all and it would be more reliable, the example above will probably have issues after the first run as the pWHERE parameter may not refresh correctly.
Anyway, that's up to you but doing it the right way is always quicker in the long run..
