troubleshooting Question

How to pass parameter count from report to SQL query

Avatar of Fester7572
Fester7572Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005SSRSSQL
4 Comments1 Solution862 ViewsLast Modified:
I need some help on how to pass the count of the number of parameter values selected for a report in to the SQL query.

My query is as follows:
SELECT        ActiveCrew.[Resource No_], Crewtbl_1.[Global Dimension 2 Code], Crewtbl_1.Team
FROM            (SELECT        No_ AS [Resource No_]
                          FROM            dbo.[Gallowglass Live$Resource]
                          WHERE        ([Global Dimension 2 Code] IN (@Region)) AND (Team IN (@Team)) AND (Blocked = 0) AND (Type = 0) AND (No_ <> 'ZZZ') AND (No_ <> 'CC1') AND 
                                                    (No_ <> 'CC2') AND (No_ <> 'CC3') AND (No_ <> 'CC4') AND (No_ <> 'CC5') AND (No_ <> 'CC6') AND (No_ <> 'CC7') AND (No_ <> 'CC8')) 
                         AS ActiveCrew INNER JOIN
                             (SELECT        TOP (100) PERCENT No_, [Global Dimension 2 Code], Team
                               FROM            dbo.[Gallowglass Live$Resource] AS [Gallowglass Live$Resource_1]
                               ORDER BY [Global Dimension 2 Code], Team, No_) AS Crewtbl_1 ON ActiveCrew.[Resource No_] = Crewtbl_1.No_
WHERE        (NOT EXISTS
                             (SELECT        No_
                               FROM            (SELECT        No_
                                                         FROM            dbo.[Gallowglass Live$Gallowglass Resource Skill]
                                                         WHERE        (Type <> 4) AND ([Skill Code] IN (@Skills))
                                                         GROUP BY No_
                                                         HAVING         (COUNT(*) = 2)) AS d
                               WHERE        (ActiveCrew.[Resource No_] = No_)))
ORDER BY ActiveCrew.[Resource No_]

I can display the number of skills selected with teh Skills parameter using =Parameters!Skills.Count.

I need to get that value in to the part of the query that is COUNT(*) = 2

Theoretically it would be:

COUNT(*) = Parameters!Skills.Count

This doesn't work. Is there a way of doing this and if so how.

Thanks very much for any pointers you can give.
ASKER CERTIFIED SOLUTION
flow01
IT-specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros