SSRS Report using Stored Procedure

SSRS Report using Stored Procedure

I had been fretting over how to pass multiple valued parameter to a stored procedure. I was going to create a report which was going to use a Stored Procedure as the data source to fetch data from the SQL Server.
After a few glitches I finally found a way to do this. Lets go through the entire process. What went wrong and how I solved it.

I created a stored procedure named SalesValue with @Year as an input parameter.
-----------------------------------------------------------------------------------------------------------
if  OBJECT_ID('SalesValue') is not null
drop proc SalesValue
go

create proc SalesValue
(@Year nvarchar(100))
as
SELECT    SalesTerritoryCountry, 
          SalesTerritoryGroup,
          [MonthNumberOfYear],
          CASE
            WHEN monthnumberofyear between 1 and 3 then 'Quarter 1'
            WHEN monthnumberofyear between 4 and 6 then 'Quarter 2'
            WHEN monthnumberofyear between 7 and 9 then 'Quarter 3'
            WHEN monthnumberofyear between 10 and 12 then 'Quarter 4'
          end as [Quarter],
          case
            when monthnumberofyear between 1 and 6 then 'Half Year 1'
            when monthnumberofyear between 7 and 12 then 'Half Year 2'
          end as [Half Year],
          CalendarYear,
          SUM(FactResellerSales.SalesAmount) AS SalesAmount, 
          SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, 
          SUM(FactResellerSales.SalesAmount) / SUM(FactResellerSales.OrderQuantity) AS AvgSale
FROM  FactResellerSales INNER JOIN DimSalesTerritory 
ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 
INNER JOIN DimDate 
ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE  CalendarYear in (@Year)
GROUP BY [MonthNumberOfYear],DimSalesTerritory.SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear
ORDER BY DimSalesTerritory.SalesTerritoryGroup, CalendarYear,[MonthNumberOfYear]
go
----------------------------------------------------------------------------------------------------- 



The stored procedure was created successfully.

When I executed the above procedure with one single valued parameter it worked like a charm. and gave the below resultset.


 
But as soon as I entered another value, the query produced an error, which stated - 
Procedure or function SalesValue has too many arguments specified.



This error was pretty straight forward, since the parameter that I created was of an integer type, I couldn't pass more than one value of the integer.

So I tried to use my brain and convert the pair of values to a string and pass, which pretty obviously created another error stating -

Conversion failed when converting the nvarchar value '2007,2008' to data type smallint.

 

Now as per my report requirements, I needed to pass multiple values in a parameter which was in turn being passed to the above stored procedure.

I headed over to BIDS and into my report solution. I created two datasets namely SalesDepartment and YearDataSet



SalesDepartment dataset made use of the stored procedure SalesValue



In order to pass the parameter value to the stored procedure, I had to configure the parameter as below - 



Using the Join function would concatenate the values with a comma separating the values and it would be passed to the query.

The YearDataSet selected the distinct year values from the datasource.



I mapped the parameter @Year to the results produced by the YearDataSet as under -


After running the report it gave me the following error 


The error was being caused due to the multiple values being implicitly converted to nvarchar. The parameter cannot be implicitly converted to smallint. To resolve this problem, we need to use a UDF which will parse the multiple valued nvarchar string and convert it into individual integers which in turn will be passed to the stored procedure. 

I found this UDF code, which has been written by Jason Sansom (http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services)

CREATE FUNCTION [dbo].[fn_SplitInt]
(
    @List       nvarchar(4000),
    @Delimiter  char(1)= ','
)
RETURNS @Values TABLE
(
    Position int IDENTITY PRIMARY KEY,
    Year int
)

AS

  BEGIN

  -- set up working variables
  DECLARE @Index INT
  DECLARE @ItemValue nvarchar(100)
  SELECT @Index = 1 

  -- iterate until we have no more characters to work with
  WHILE @Index > 0

    BEGIN

      -- find first delimiter
      SELECT @Index = CHARINDEX(@Delimiter,@List)

      -- extract the item value
      IF @Index  > 0     -- if found, take the value left of the delimiter
        SELECT @ItemValue = LEFT(@List,@Index - 1)
      ELSE               -- if none, take the remainder as the last value
        SELECT @ItemValue = @List

      -- insert the value into our new table
      INSERT INTO @Values (Year) VALUES (CAST(@ItemValue AS int))

      -- remove the found item from the working list
      SELECT @List = RIGHT(@List,LEN(@List) - @Index)

      -- if list is empty, we are done
      IF LEN(@List) = 0 BREAK

    END

  RETURN

  END

This will create a function in the AdventureWorksDW2008R2 DB by the name of  dbo.fn_SplitInt. This function will take a nvarchar and a delimiter string. the result would be a table containing a column which would give all the year values in integer. There should not be any spaces between the values after the comma delimiter. Now we make a change to the stored procedure definition. 

if  OBJECT_ID('SalesValue') is not null
drop proc SalesValue
go

create proc SalesValue
(@Year nvarchar(100))
as
SELECT    SalesTerritoryCountry, 
          SalesTerritoryGroup,
          [MonthNumberOfYear],
          CASE
            WHEN monthnumberofyear between 1 and 3 then 'Quarter 1'
            WHEN monthnumberofyear between 4 and 6 then 'Quarter 2'
            WHEN monthnumberofyear between 7 and 9 then 'Quarter 3'
            WHEN monthnumberofyear between 10 and 12 then 'Quarter 4'
          end as [Quarter],
          case
            when monthnumberofyear between 1 and 6 then 'Half Year 1'
            when monthnumberofyear between 7 and 12 then 'Half Year 2'
          end as [Half Year],
          CalendarYear,
          SUM(FactResellerSales.SalesAmount) AS SalesAmount, 
          SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, 
          SUM(FactResellerSales.SalesAmount) / SUM(FactResellerSales.OrderQuantity) AS AvgSale
FROM  FactResellerSales INNER JOIN DimSalesTerritory 
ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 
INNER JOIN DimDate 
ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE  CalendarYear in (select Year from dbo.fn_SplitInt(@Year,','))
GROUP BY [MonthNumberOfYear],DimSalesTerritory.SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear
ORDER BY DimSalesTerritory.SalesTerritoryGroup, CalendarYear,[MonthNumberOfYear]
go

After the above changes are made to the stored procedure, the report can be generated easily with the data for all the selected years. 

 

This way I can use stored procedures to create SSRS Reports along with the use of User Defined Functions.