August 23, 2011 at 9:14 am
Hi
I've created a SP which contains 8 parameters, so when values are passed through it they should return the corresponding data, this works when I've tested it within the SP.
However, when I've connected it to Reporting Services(SSRS) no data is returned with the values that i know exist in DB, when entered in 'Define Query Parameters' windows within SSRS.
Can someone have a look at the SP and let me know where I'm going wrong or what I'm missing.
(please not if you need any additional information just let me know)
Thanks in advance
USE [DSReports]
GO
/****** Object: StoredProcedure [dbo].[Waterfall_New] Script Date: 08/24/2011 14:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Waterfall_New]
-- declaring the variables to be used in the procedure
(
@FieldDate VARCHAR(20) = null,
@Item varchar(50) = null ,
@Company varchar(50) = null,
@Division varchar(50) = null,
@Corporation varchar(50) = null,
@SoldTo varchar(50) = null,
@Department varchar(50) = null,
@ShipTo varchar(50) = null
)
AS
-- STAGE 1
select Item as SysDate
into #SystemDateTable1
from January
where item = '01 2011'
select Item as sysDate
into #systemDateTable2
from February
where item = '02 2011'
-- STAGE 2
--select * from #HorizontalTable1
--select * from #HorizontalTable2
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,Class1,ShipTo,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into #HorizontalTable1
from January Cross Join #SystemDateTable1
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,ShipTo,Class1,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into #HorizontalTable2
from February Cross Join #systemDateTable2
-- Stage 3
Select 'Fcst' as DataType
,CaptureDate
,DateAdd(MONTH,-1, (Cast(Right(CaptureDate,4) + '/' + Left(CaptureDate,2) +'/01' as Date )))AS FieldDate
,Item
,Company
,Division
,Corporation
,SoldTo
,Department
,ShipTo
,Class1
,Class2
,Class3
,Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
INTO #VerticalTable
FROM #HorizontalTable1
select 'Fcst' as DataType
,CaptureDate
,DateAdd(MONTH,0, (Cast(Right(CaptureDate,4) + '/' + Left(CaptureDate,2) +'/01' as Date )))AS FieldDate
,Item
,Company
,Division
,Corporation
,SoldTo
,Department
,ShipTo
,Class1
,Class2
,Class3
,Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
INTO #VerticalTable2
FROM #HorizontalTable2
--Drop table #VerticalTable2
--STAGE 4
--DECLARE @Item varchar(10)
--set @Item = 'B905-50'
--declare @FieldDate varchar(15)
--set @FieldDate = '20101201'
--declare @ShipTo varchar(15)
--set @ShipTo = 'CUST0015'
select V.DataType, V.CaptureDate, V.FieldDate,V.Item,V.Company,V.Division,V.Corporation,V.SoldTo,V.Department,V.ShipTo
,V.Class1, V.Class2, V.Class3, V.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from #VerticalTable V
where V.FieldDate = @FieldDate
and V.Item = @Item
AND V.Company = @Company
AND V.Division = @Division
AND V.Corporation = @Corporation
AND V.SoldTo = @SoldTo
AND V.Department = @Department
AND V.ShipTo = @ShipTo
union all
select V2.DataType, V2.CaptureDate, V2.FieldDate,V2.Item,V2.Company,V2.Division, V2.Corporation,V2.SoldTo,V2.Department,V2.ShipTo
,V2.Class1, V2.Class2, V2.Class3, V2.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from #VerticalTable2 V2
where V2.FieldDate = @FieldDate
and V2.Item = @Item
AND V2.Company = @Company
AND V2.Division = @Division
AND V2.Corporation = @Corporation
AND V2.SoldTo = @SoldTo
AND V2.Department = @Department
AND V2.ShipTo = @ShipTo
--select * from #VerticalTable2
--select * from #VerticalTable
[/Code]
CREATE THE TABLE SCRIPT
-- Create the table
-- Can use the same script to create the feb table and populate it
CREATE TABLE [dbo].[January](
[Item] [varchar](32) NOT NULL,
[Company] [varchar](3) NOT NULL,
[Division] [varchar](8) NOT NULL,
[Corporation] [varchar](12) NOT NULL,
[SoldTo] [varchar](8) NOT NULL,
[Department] [varchar](3) NOT NULL,
[ShipTo] [varchar](12) NOT NULL,
[Class1] [varchar](20) NOT NULL,
[Class2] [varchar](20) NOT NULL,
[Class3] [varchar](20) NOT NULL,
[Class4] [varchar](20) NOT NULL,
[SysFcst#1] [int] NULL,
[SysFcst#2] [int] NULL,
[SysFcst#3] [int] NULL,
[SysFcst#4] [int] NULL,
[SysFcst#5] [int] NULL,
[SysFcst#6] [int] NULL,
[SysFcst#7] [int] NULL,
[SysFcst#8] [int] NULL,
[SysFcst#9] [int] NULL,
[SysFcst#10] [int] NULL,
[SysFcst#11] [int] NULL,
[SysFcst#12] [int] NULL,
[AdjFcst#1] [int] NULL,
[AdjFcst#2] [int] NULL,
[AdjFcst#3] [int] NULL,
[AdjFcst#4] [int] NULL,
[AdjFcst#5] [int] NULL,
[AdjFcst#6] [int] NULL,
[AdjFcst#7] [int] NULL,
[AdjFcst#8] [int] NULL,
[AdjFcst#9] [int] NULL,
[AdjFcst#10] [int] NULL,
[AdjFcst#11] [int] NULL,
[AdjFcst#12] [int] NULL
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Inserting data into the table
BULK INSERT Test.dbo.January FROM 'C:\KeyFields5.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
I've attached a file called Keyfields5
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
August 23, 2011 at 9:28 am
I would double check your parameter assignment to the dataset in the SSRS report. You may have a report parameter pointed to the wrong sp parameter
August 23, 2011 at 1:42 pm
I suspect that all of the parameter values are not being filled in or you have tried to customize the parameters in SSRS.
One issue I see is that you have defaulted the parameters to NULL. Because the corresponding columns are marked as NOT NULL, this will cause an issue if any of the parameters are skipped over in SSRS and assigned a NULL value.
First thing I would do is go to the parameter properties for each parameter and make sure NULL values are not allowed. This will force the user to enter a value for every parameter.
Most of this is automatic when you hook the stored procedure up in the the data set.
August 24, 2011 at 9:12 am
Many thanks for your reply
@Neal I've checked through the parameters in SSRS and that doesnt seem to be the problem. I've attached a word document of the steps that i have followed.
@jerry the reason I've set the parameters to null as sometime there might be no data for any of the data fields set as parameters. For example, there might be data for Item and ShipTo parameters but none for Company, Division, Corporation, SoldTo and Department.
The document i've attached should give a clear indication of whats happen in SSRS (marked The SSRS Problem.)
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
August 24, 2011 at 11:22 am
Steve,
I am still thinking that the issue is your NULL values. You only used the date in your example which means your WHERE clause
where V2.FieldDate = @FieldDate and V2.Item = @Item AND V2.Company = @Company AND V2.Division = @Division AND V2.Corporation = @Corporation AND V2.SoldTo = @SoldTo AND V2.Department = @Department AND V2.ShipTo = @ShipTo
is trying to match the valuse to an undefined value.
You may try rewriting the WHERE clause to look more like
where V2.FieldDate = @FieldDate
AND (@Item IS NULL OR V2.Item = @Item)
AND (@Company IS NULL OR V2.Company = @Company)
AND (@Division IS NULL OR V2.Division = @Division)
AND (@Corporation IS NULL OR V2.Corporation = @Corporation)
AND (@SoldTo IS NULL OR V2.SoldTo = @SoldTo)
AND (@Department IS NULL OR V2.Department = @Department)
AND (@ShipTo IS NULL OR V2.ShipTo = @ShipTo)
If your issue is the NULL values in your parameters, this should help avoid the no data returned issue you are experiencing.
August 24, 2011 at 12:30 pm
Your problem is that <blank> is not the same as <null>
When you're executing from SSRS, I believe it's passing parameter values of '' instead of just not passing the parameter, or passing NULL
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 25, 2011 at 7:26 am
Thanks for all the replies much appreciated
@ weitzera your spot on I've changed the other parameters to null and its finally returned data in the 'Query Designer' window, thanks for the advise.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply