October 7, 2011 at 2:08 am
Sorry I've been quite busy with a some other work.
I seems like a lot of effort to get a distinct list of Items.
A couple of suggestions, dont use SELECT....INTO they may have been more optimal in previous versions due to lack of logging, but everything I've read on 2008 suggests that this is no longer the case with Temp tables.
I would issue a Truncate Table for the [dbo].[HorizontalTable1] rather than a Drop Table, and change the select into an INSERT INTO, it will save a bit of overhead on the table creation with the select.
Secondly, I would manually create the #Temp table used at the bottom, as one of the first tables.
This would be my revised script.
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
/*
This fools SSRS into recognising the data set thus it shouldnt Error.
If you're only returning 1 column limit the set to that
*/
if 1=2
begin
select
Cast (null AS <datatype>) Item
,Cast (null AS <datatype>) ShipTo
,Cast (null AS <datatype>) Class1
,Cast (null AS <datatype>) Class2
,Cast (null AS <datatype>) Class3
,Cast (null AS <datatype>) Class4
,Cast (null AS <datatype>) SysFcst#1
,Cast (null AS <datatype>) SysFcst#2
,Cast (null AS <datatype>) SysFcst#3
,Cast (null AS <datatype>) SysFcst#4
,Cast (null AS <datatype>) AdjFcst#1
,Cast (null AS <datatype>) AdjFcst#2
,Cast (null AS <datatype>) AdjFcst#3
,Cast (null AS <datatype>) AdjFcst#4
end
CREATE TABLE #temp (
Item As <datatype>
, ShipTo As <datatype>
, Class1 As <datatype>
, Class2 As <datatype>
, Class3 As <datatype>
, Class4 As <datatype>
, SysFcst#1 As <datatype>
, SysFcst#2 As <datatype>
, SysFcst#3 As <datatype>
, SysFcst#4 As <datatype>
, AdjFcst#1 As <datatype>
, AdjFcst#2 As <datatype>
, AdjFcst#3 As <datatype>
, AdjFcst#4 As <datatype>
)
-- 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'
TRUNCATE TABLE [dbo].[HorizontalTable1]
-- STAGE 2
--select * from HorizontalTable1
--select * from #HorizontalTable2
Insert into [dbo].[HorizontalTable1]
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
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 *
-- 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 (@Item IS NULL OR V.Item = @Item)
AND (@Company IS NULL OR V.Company = @Company)
AND (@Division IS NULL OR V.Division = @Division)
AND (@Corporation IS NULL OR V.Corporation = @Corporation)
AND (@SoldTo IS NULL OR V.SoldTo = @SoldTo)
AND (@Department IS NULL OR V.Department = @Department)
AND (@ShipTo IS NULL OR 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 (@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)
--select * from #VerticalTable2
--select * from #VerticalTable
--select * from #VerticalTable
--where FieldDate = '20101201'
--and Item = 'B905-50'
--drop table HorizontalTable1
--drop table
---- Temp table to populate primary key and item
--select distinct Item as work
-- from dbo.January
--- I original had this script in SSRS dataset called item
INSERT INTO #temp
select distinct Item
, ShipTo
, Class1
, Class2
, Class3
, Class4
, SysFcst#1
, SysFcst#2
, SysFcst#3
, SysFcst#4
, AdjFcst#1
, AdjFcst#2
, AdjFcst#3
, AdjFcst#4
from dbo.January
where Item IN ('A101','A102','A105','A202','A205','B901-12'
,'B903-25','B905-50','B920','B940','C900','C901','D101','D103','D104','D106','D108','IN-STORE-PAINT-PALLET','PAINT-CAN-1-LITRE','PAINT-CAN-2-LITRE','PAINT-CAN-5-LITRE'
,'PAINT-CAN-LID-1-LITRE','PAINT-CAN-LID-2-LITRE','PAINT-CAN-LID-5-LITRE','RNG:PAINT DIVISION - EXTERIOR','RNG: PAINT DIVISION - INTERIOR','WC5-LINE')
select distinct Item from #temp
(appologies for formatting if it looks wrong), I'll address the Function in a seperate post.
(Edits Missing the IF and comments)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 2:39 am
Ninja's_RGR'us (10/6/2011)
Well it compiles on my system but obviously doesn't run without the tables.Can you run the sp alone in ssms?
If yes, you can try dumping the dataset and redoing it.
Ninja it would run is SSMS, this article on the MS forums explains the work round http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/d46a859f-8635-4ff7-b006-e52ce58252a6
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 2:54 am
@ Jason
When i try running the script i get the following error which i cant seem to fix
Msg 102, Level 15, State 1, Procedure Waterfall_version2, Line 22
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Procedure Waterfall_version2, Line 40
Incorrect syntax near '<'.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 7, 2011 at 2:59 am
Jnrstevej (10/7/2011)
@ JasonWhen i try running the script i get the following error which i cant seem to fix
Msg 102, Level 15, State 1, Procedure Waterfall_version2, Line 22
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Procedure Waterfall_version2, Line 40
Incorrect syntax near '<'.
sorry I should have explained, you need to replace the <datatype> with the appropriate data type you need for those columns, as that is the unknown for, Eg
select
Cast (null AS varchar(50)) Item
,Cast (null AS varchar(50)) ShipTo
,Cast (null AS varchar(50)) Class1
,Cast (null AS varchar(50)) Class2
,Cast (null AS varchar(50)) Class3
,Cast (null AS varchar(50)) Class4
,Cast (null AS Decimal(36,10)) SysFcst#1
,Cast (null AS Decimal(36,10)) SysFcst#2
,Cast (null AS Decimal(36,10)) SysFcst#3
,Cast (null AS Decimal(36,10)) SysFcst#4
,Cast (null AS Decimal(36,10)) AdjFcst#1
,Cast (null AS Decimal(36,10)) AdjFcst#2
,Cast (null AS Decimal(36,10)) AdjFcst#3
,Cast (null AS Decimal(36,10)) AdjFcst#4
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 3:02 am
A Question on the multi selected, is the intention to have the @Item variable store the results of the selection by the user?
Ninja is correct about an Indexted Temp table being another option, however if you intend to use a similar method in other places the benefits of a re-usable Table function out weigh the performance gain of the temp table, IMHO.
As far as I'm aware SQL server will determine the type of function that is created, in this case its an In-line table function that is created.
If we create the one in the link i provided on the first page, you would have a UF_CSVToTable function.
You would call this
INSERT INTO #temp
select distinct Item
, ShipTo
, Class1
, Class2
, Class3
, Class4
, SysFcst#1
, SysFcst#2
, SysFcst#3
, SysFcst#4
, AdjFcst#1
, AdjFcst#2
, AdjFcst#3
, AdjFcst#4
from dbo.January
JOIN dbo.UF_CSVToTable(@Item)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 8:46 am
I keep getting errors with this section of the code:
'Msg 195, Level 15, State 10, Procedure Waterfall_version2, Line 40
'varchar' is not a recognized built-in function name'
CREATE TABLE #temp (
Item As (varchar(50)
, ShipTo As (varchar(50)
, Class1 As (varchar(50)
, Class2 As (varchar(50)
, Class3 As (varchar(50)
, Class4 As (varchar(50)
, SysFcst#1 As int
, SysFcst#2 As int
, SysFcst#3 As int
, SysFcst#4 As int
, AdjFcst#1 As int
, AdjFcst#2 As int
, AdjFcst#3 As int
, AdjFcst#4 As int
)
@Item variable is not meant to store the results but simply allow the user to select multiple values. For example, if they select A101 i want to store all the corresponding data related to that item. However, if i select A101 and A102 i want in SSRS to select show the corresponding data for the two items.
-- What I'm trying to achieve within SSRS
-- The user only select A101
A101 4 5 6 7
A101 3 5 7 8
-- The user only select A102
A102 3 4 6 5
A102 6 4 8 9
-- The user select A101 and A102
A101 4 5 6 7
A101 3 5 7 8
A102 3 4 6 5
A102 6 4 8 9
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 7, 2011 at 9:01 am
This should work
CREATE TABLE #temp (
Item As varchar(50)
, ShipTo As varchar(50)
, Class1 As varchar(50)
, Class2 As varchar(50)
, Class3 As varchar(50)
, Class4 As varchar(50)
, SysFcst#1 As int
, SysFcst#2 As int
, SysFcst#3 As int
, SysFcst#4 As int
, AdjFcst#1 As int
, AdjFcst#2 As int
, AdjFcst#3 As int
, AdjFcst#4 As int
)
You had one too many brackets in the statement it should work now.
as for the RS report, you need to set up the Item list to be a multiple select, this will then pass data into the Variable as 'A101,A102'
At this point you need to split the Vairalbe that contains this data into a its consituent parts other wise the query will compile like
Select
Item
,Shipto
From
aTable
Where
Item in ('A101,A102')
Where you want something like
Select
Item
,Shipto
From
aTable
Where
Item in ('A101','A102')
This is where the text spliter comes into play.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 9:28 am
Unfortunately I'm still getting the same error message.
Msg 195, Level 15, State 10, Procedure Waterfall_version2, Line 40
'varchar' is not a recognized built-in function name.
Create PROCEDURE [dbo].[Waterfall_version2]
-- 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
/*
This fools SSRS into recognising the data set thus it shouldnt Error.
If you're only returning 1 column limit the set to that
*/
if 1=2
begin
select
Cast (null AS varchar(50)) Item
,Cast (null AS varchar(50)) ShipTo
,Cast (null AS varchar(50)) Class1
,Cast (null AS varchar(50)) Class2
,Cast (null AS varchar(50)) Class3
,Cast (null AS varchar(50)) Class4
,Cast (null AS Decimal(36,10)) SysFcst#1
,Cast (null AS Decimal(36,10)) SysFcst#2
,Cast (null AS Decimal(36,10)) SysFcst#3
,Cast (null AS Decimal(36,10)) SysFcst#4
,Cast (null AS Decimal(36,10)) AdjFcst#1
,Cast (null AS Decimal(36,10)) AdjFcst#2
,Cast (null AS Decimal(36,10)) AdjFcst#3
,Cast (null AS Decimal(36,10)) AdjFcst#4
end
--------Still getting the same error message with this part
CREATE TABLE #temp (
Item As varchar(50)
, ShipTo As varchar(50)
, Class1 As varchar(50)
, Class2 As varchar(50)
, Class3 As varchar(50)
, Class4 As varchar(50)
, SysFcst#1 As int
, SysFcst#2 As int
, SysFcst#3 As int
, SysFcst#4 As int
, AdjFcst#1 As int
, AdjFcst#2 As int
, AdjFcst#3 As int
, AdjFcst#4 As int
)
-------------------------------------------
-- 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'
TRUNCATE TABLE [dbo].[HorizontalTable1]
-- STAGE 2
Insert into [dbo].[HorizontalTable1]
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
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
--STAGE 4
----------------------------------
/*Testing*/
--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 (@Item IS NULL OR V.Item = @Item)
AND (@Company IS NULL OR V.Company = @Company)
AND (@Division IS NULL OR V.Division = @Division)
AND (@Corporation IS NULL OR V.Corporation = @Corporation)
AND (@SoldTo IS NULL OR V.SoldTo = @SoldTo)
AND (@Department IS NULL OR V.Department = @Department)
AND (@ShipTo IS NULL OR 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 (@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)
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 10, 2011 at 3:33 am
Any luck?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 10, 2011 at 6:55 am
Sorry its my fault I type things as I'm thinking, and a common fault of mine is during table creation scripts where I think the word AS and then type it subliminally.
CREATE TABLE #temp (
Item varchar(50)
, ShipTo varchar(50)
, Class1 varchar(50)
, Class2 varchar(50)
, Class3 varchar(50)
, Class4 varchar(50)
, SysFcst#1 int
, SysFcst#2 int
, SysFcst#3 int
, SysFcst#4 int
, AdjFcst#1 int
, AdjFcst#2 int
, AdjFcst#3 int
, AdjFcst#4 int
)
This is the create table script.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 17, 2011 at 4:30 am
Thanks in the process of testing this now
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply