SELECT * FROM Widget W
WHERE WM.WidgetManagerId IN (@WidgetManagers)
And here's the query to select the Widget Managers:
SELECT FirstName + ' ' + LastName AS WidgetManagerName, WidgetManagerId
FROM WidgetManager
ORDER BY WidgetManagerName
Here's what the parameter definition looks like:
This works okay, but for my purposes there were 2 issues. First of all, my client wanted all items to be selected by default. That's an easy problem to handle, all I need to do is to set the Default values for the WidgetManagers report parameter to the same dataset I use for Available values.
Unfortunately, that query ran really slowly. When I looked at the query using SQL Profiler, it looked like this:
SELECT * FROM Widget W
INNER JOIN WidgetManager WM ON
W.WidgetManagerId = WM.WidgetManagerId
WHERE WM.WidgetManagerId IN
(N'0fa07056-8e50-43a0-b72d-000a68d17be1',
N'8eff8f59-ca6a-4eed-a434-016a8831c7ec',
N'2a3a885e-8fb2-4e8b-b55a-02a7225a1143',
...)
I only showed 3 of the GUIDS, but the query sent to SQL had all 1000+ of them inline in the query. No wonder it was slow.
I'll skip to the final solution. I changed the query for the Widget Managers to look like this:
SELECT 'All Widget Managers' AS WidgetManagerName,
'00000000-0000-0000-0000-000000000000' AS WidgetManagerId, 1 AS OrderBy
UNION
SELECT FirstName + ' ' + LastName AS WidgetManagerName,
WidgetManagerId, 2 AS OrderBy
FROM WidgetManager
ORDER BY OrderBy, WidgetManagerName
Then I changed the query for the Widgets themselves to look like this:
DECLARE @WidgetManagerCount AS INT
SET @WidgetManagerCount = (SELECT COUNT(*) FROM WidgetManager
WHERE WidgetManagerId IN (@WidgetManagers))
SELECT * FROM Widget W
WHERE
(1 =
CASE
WHEN @WidgetManagerCount = 0 THEN 1
ELSE
CASE
WHEN W.WidgetManagerId IN (@WidgetManagers) THEN 1
ELSE 0
END
END)
And finally, I changed the report parameter to look like this:
The basic idea is that when the report first comes up, "All Widget Managers" is chosen, and all other Widget Managers are unchecked. When the Widget query runs, @WidgetManagerCount will be set to 0, and only the top part of the outer case statement will be evaluated, and all Widgets will display on the report.
If any other Widget Managers are checked, @WidgetManagerCount will then be greater than zero, which will cause the inner case statement to be evaluated for each Widget. If a Widget Manager has been checked, all related Widgets will be displayed on the report.
This version of the report ran much, much faster than the original. What I'm showing above is a stripped down version of the report, but the real one had 4 filter criteria, and a couple of those filter criteria had thousands of choices.
