Link to home
Start Free TrialLog in
Avatar of Fester7572
Fester7572Flag 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_
                             (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.
Avatar of flow01
Flag of Netherlands image

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)
Avatar of Fester7572


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
User generated image

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.
Avatar of flow01
Flag of Netherlands image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are a legend! Thank you. Just the tips I needed to crack this.