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)
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 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 nulldrop 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.
No comments:
Post a Comment