How to get the number of elements in a SSRS multi-value paramter

I have a SQL Server Reporting Service form that is supposed to query and display metrics across a number of years.

The form can optionally receive years it is supposed to display as a parameter. The problem I have is specifying "if @year is not set then include everything, otherwise limit to the specified years."

The query I have currently is:

SELECT name, collected_year, value, [order]
  FROM rpt_competency_metric
 WHERE ( len( @year ) = 0 OR collected_year IN ( @year ) )
   AND competency_id = @competency_id

Which works when @year is blank, but fails with the error The len function requires 1 argument. when passing in multiple values.

I've tried count( @ye开发者_JAVA技巧ar ) and DataLength( @year ) without success.

OK, I know this is stale, but I think I have an answer now.

You can set another parameter to the count of your multi-valued parameter and pass that into the query. So you will end up with this on the SQL Side:

SELECT name, collected_year, value, [order]
  FROM rpt_competency_metric
 WHERE ( @mvpCount = 0 OR collected_year IN ( @year ) )
   AND competency_id = @competency_id 

On the RS side you would use an expression to set @mvpCount to the count of your @year:

=Parameters!Year.Count

I haven't tried it, but I think it would work.

Thanks, Queso.

The solution you suggested below works as a charm. I was fighting with it for over half a day.

SELECT 
    name
  , collected_year
  , value
  , [order] 
FROM rpt_competency_metric 
WHERE ( len('@year') = 0 
    OR collected_year IN ( @year ) )   
    AND competency_id = @competency_id 

I found no satisfactory answer to this on the web so after much thought and prayer I came up with this solution.

The trouble that I and everybody else is having is that the only way to handle an SSRS multi-value variable is by adding in the where clause:

Table.FieldName in (@MultiValue)

So to check to see if there is something in the @MultiValue variable you will need to do the following. In the query field or in a stored procedure you will need to find out if there were any selections made and stuff them into an in memory sql table. Then in another query get the row count and stuff that into a variable. Then use that variable in the query to determine if you should search on that criteria.

Here is an example in SQL:

DECLARE @GroupCnt int;
Declare @TblGroup Table (GroupID int);

Insert INTO @TblGroup (GroupID)
SELECT ItemID
FROM Groups
WHERE ItemId in (@Group)

SELECT @GroupCnt = COUNT(*)
From @TblGroup

SELECT        CAST(ItemId AS Varchar(10)) AS ProviderID, ProviderShortName AS Location
FROM            Provider as prov
WHERE        (IsActive = 1)
    and (@GroupCnt = 0 or prov.GroupId in (@Group)) 
UNION ALL
SELECT        '' AS ProviderID, '(NONE)' AS Location
ORDER BY Location

The "and (@GroupCnt = 0 or prov.GroupId in (@Group))" code is where the magic happens but in order to do this you must get the @GroupCnt variable from the first two queries.

FYI, I add the UNION ALL so that the user will have the option to not select any options.

Multi-Value Parameters are converted into a comma-separated list. So you are ending up with this when you use the len function:

SELECT name, collected_year, value, [order]
      FROM rpt_competency_metric
     WHERE ( len(2007,2008) = 0 OR collected_year IN ( 2007,2008 ) )
       AND competency_id = @competency_id

Which explains your error as well. One of these might work, but I haven't tested them:

SELECT name, collected_year, value, [order]
  FROM rpt_competency_metric
 WHERE ( @year is null OR collected_year IN ( @year ) )
   AND competency_id = @competency_id

Or:

SELECT name, collected_year, value, [order]
  FROM rpt_competency_metric
 WHERE ( rtrim(ltrim(convert(varchar,'@year'))) = '' OR collected_year IN ( @year ) )
   AND competency_id = @competency_id

Try the above with and without the single quotes around @year.

Don't know what SSRS does with a multi-value parameter in a literal string, but if it replaces it outright this might work, which is close to what you have:

SELECT name, collected_year, value, [order]
  FROM rpt_competency_metric
 WHERE ( len('@year') = 0 OR collected_year IN ( @year ) )
   AND competency_id = @competency_id