CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2ssrs-2008-r2
d90
d90

Reputation: 787

SSRS Report fed by Stored Procedure \ IN operator

I created a report to get a bunch of data. The report calls a stored procedure called sp_get52week, which gets me some totalled data about our customers. The report has 3 parameters, @Year, @Week, @SalespersonNumber. @SalespersonNumber is a parameter that can accept multiple values. The report works when only one SalesPersonNumber is selected, but not multiple. When I select multiple, I get the error: 

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
Error converting data type nvarchar to int.

The Stored Procedure behind the report is the following: 

ALTER PROCEDURE [dbo].[sp_Get52Week]
    @Year INT,
    @Week INT,
    @SalesPersonNumber INT
AS
BEGIN
    SELECT  

a.[Group]
,a.[Owner]
,a.[OwnerLastName]
,SUM(a.[tot_sls_amt]) AS CYSales
,(b.[tot_sls_amt]) AS PYSales

  FROM 

[001].[MW].[MW_Submitted_Weeks_By_RBC_ROY] a
        Left Join 
            (SELECT sum([tot_sls_amt]) tot_sls_amt,[Group],[Owner] ,[SalesPersonNumber]
                 FROM [001].[MW].[MW_Submitted_Weeks_By_RBC_ROY]
                     WHERE ((Year = @Year-1 AND Week <= @Week) OR (Year = @Year - 2 AND Week > @Week)) AND cmp_status = 'A' AND [SalesPersonNumber] IN (@SalesPersonNumber)
                        group by [Group],[Owner] ,[SalesPersonNumber]) b on a.[Group] = b.[Group] and a.[Owner] = b.[Owner] and a.[SalesPersonNumber] =b.[SalesPersonNumber]



WHERE 
((Year = @Year AND Week <= @Week) OR (Year = @Year - 1 AND Week > @Week)) AND cmp_status = 'A' AND a.SalesPersonNumber IN (@SalesPersonNumber)


Group BY 
a.[Group] 
,a.[Owner]
,a.[OwnerLastName]
,(b.[tot_sls_amt])
END

How can I change my report to allow multiple values with a stored procedure using the IN operator?

Upvotes: 0

Views: 1075

Answers (2)

adrianm
adrianm

Reputation: 14726

I'll show this as an answer instead

CREATE FUNCTION [dbo].[sp_Get52Week] (
    @Year INT,
    @Week INT
)
RETURNS table AS
RETURN (
    SELECT a.[Group]
          ,a.[Owner]
          ,a.[OwnerLastName]
          ,a.[SalesPersonNumber]
          ,SUM(a.[tot_sls_amt]) AS CYSales
          ,(b.[tot_sls_amt]) AS PYSales
    FROM [001].[MW].[MW_Submitted_Weeks_By_RBC_ROY] a
         LEFT JOIN (SELECT sum([tot_sls_amt]) tot_sls_amt
                          ,[Group]
                          ,[Owner] 
                          ,[SalesPersonNumber]
                    FROM [001].[MW].[MW_Submitted_Weeks_By_RBC_ROY]
                    WHERE ((Year = @Year-1 AND Week <= @Week) OR 
                           (Year = @Year - 2 AND Week > @Week)) 
                          AND cmp_status = 'A' 
                    GROUP BY [Group]
                            ,[Owner] 
                            ,[SalesPersonNumber]) b 
             ON a.[Group] = b.[Group] 
                AND a.[Owner] = b.[Owner] 
                AND a.[SalesPersonNumber] = b.[SalesPersonNumber]
    WHERE ((Year = @Year AND Week <= @Week) 
           OR (Year = @Year - 1 AND Week > @Week)) 
          AND cmp_status = 'A' 
    GROUP BY a.[Group] 
            ,a.[Owner]
            ,a.[OwnerLastName]
            ,a.[SalesPersonNumber]
            ,(b.[tot_sls_amt])
)

In the report you do

SELECT * 
FROM [dbo].[sp_Get52Week](@Year, @Week)
WHERE [SalesPersonNumber] IN (@SalesPersonNumber)

Upvotes: 1

Dan
Dan

Reputation: 10680

The IN operator does not work with multiple values in one parameter. Furthermore, your @SalesPersonNumber parameter is of data type INT, which only accepts one value.

To work around this, what you could do is declare the @SalesPersonNumber as a VARCHAR(MAX) datatype, and then change your filter to something like this:

WHERE ... AND 
    (',' + @SalesPersonNumber + ',' LIKE
     '%,' + CAST(a.SalesPersonNumber AS VARCHAR) + ',%')

This assumes that you're assigning a comma-separated list of values to the @SalesPersonNumber parameter. You might need to use some SSRS array-to-string functionality to achieve this (take a look at the SSRS JOIN-function).

What the above code essentially does, is to check whether the SalesPersonNumber in your table matches one of the comma-separated numbers in the @SalesPersonNumber-string.

Upvotes: 0

Related Questions