CODEWITHSUNDEEP
t-sqlreporting-servicesssis
dysbulic
dysbulic

Reputation: 3105

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( @year ) and DataLength( @year ) without success.

Upvotes: 1

Views: 6568

Answers (5)

ItsANoBrainer
ItsANoBrainer

Reputation: 1

Here are my solutions without needing any any extra parameters.

If you only need to know if there exists values or not, you can adjust your conditional based on your needs.

-- Returns total number of characters.
SELECT LEN(CONCAT(@multiValueParam,''));

If you need to get each value separately:

-- Returns multiple rows, one for each element in the parameter.
SELECT * 
FROM STRING_SPLIT( SUBSTRING(CONCAT_WS(',',@multiValueParam,''), 1, LEN(CONCAT_WS(',',@multiValueParam,''))-1) ,',');

If you need to get just the number of elements:

-- Returns total number of elements in parameter.
SELECT COUNT(*) 
FROM STRING_SPLIT( SUBSTRING(CONCAT_WS(',',@multiValueParam,''), 1, LEN(CONCAT_WS(',',@multiValueParam,''))-1) ,',');

MSSQL will get angry about not enough parameters in the functions, so we need to use a dummy value of empty string to get around it.

We use CONCAT_WS to turn our multi-values into a single string. This causes our concats with separators to have an extra separator at the end, which splits into an extra multi-value.

We use SUBSTRING to remove this extra comma at the end of our CONCAT_WS string.

We use STRING_SPLIT with our separator to pull our the individual values.

You can test by replacing @multiValueParam with 'test1','test2' exactly, which is basically what SSRS does when putting multi-value parameter into your query. You can also use any separator if you data happens to have commas.

Upvotes: 0

Captain America
Captain America

Reputation: 1932

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.

Upvotes: 1

Silvano
Silvano

Reputation: 11

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

Upvotes: 1

Quesi
Quesi

Reputation: 736

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.

Upvotes: 3

Quesi
Quesi

Reputation: 736

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

Upvotes: 0

Related Questions