Avatar of Fester7572
Fester7572
Flag for United Kingdom of Great Britain and Northern Ireland asked on

How to pass parameter count from report to SQL query

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_]

Open in new window


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.
Microsoft SQL Server 2005SSRSSQL

Avatar of undefined
Last Comment
Fester7572

8/22/2022 - Mon
flow01

depends on the language your are using to issue the sql-call.
If the sql-query is assigned as a text-value  you can build it by concatenating the parameter with the text

instead of
xxx.sqltext = 'select .. from where count(*) = 2 order by 1'
make it
xxx.sqltext = 'select .. from where count(*) = ' & Parameters!Skills.Count & 'order by 1'

(or whatever the concatenation looks like in your language)
Fester7572

ASKER
Thanks for the advice. I'm a bit of a novice so bear with me if I am not doing something obvious.

I am using SQL Business Intelligence Development Studio 2005 to design the report.

Here is what I am working with
Screenshot of Dataset

When I run the query I get this error:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read teh next data row for the data set Live_data.
Conversion failed when converting the varchar value ' & CInt(Parameters!Skills.Count) & ' to data type int.

I got this error both with and without the CInt.. Just tried that to see if that worked around the error.
ASKER CERTIFIED SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Fester7572

ASKER
You are a legend! Thank you. Just the tips I needed to crack this.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy