October 3, 2011 at 4:43 am
Hi
In the report i am creating i need help to achieve the following
1. Allowing the user to select multiple values from a drop down menu in SSRS. For example, the user can select more than one items A101 and A102, etc. The problem I that having is that if I select both of them no data values will appear in the table. However, if I was to select let’s say A101 data would appear.
2. I want to write a script/code in my SP that will look at the current month and only should 12 months (or whatever month I specify) of data including the current month. For example, it’s now September 2011 I would expect to see data from Nov, Dec, January 12, Feb12, March, April, May, June, July, Aug, Sep12
I've tried resolve this by creating a temp table but SSRS does not recognise it as i get a message saying it doesnt not exist.
Can you help me to achieve the above??
I've enclosed a copy of the database schema and the SSRS report. Currently using SQL Server 2008 R2 and SQL Server Business Intelligence Development.
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 3, 2011 at 4:54 am
Are you physically defining your temp table with a create or being lazy and doing a SELECT....INTO?
There are issues with SSRS (and SSIS) where by if you return data from an SP that uses a Temp table. you sometimes have to 'fool' it.
This can be done by using the script
IF 1=2
BEGIN
SELECT
convert(<data type>, NULL) column_Name
:::::::::::
END
as the first first statement in your Stored proc.
That should solve the problem of the SSRS saying a column doesnt exist.
PS : THis is a cludge and its the only way I've found of doing this.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2011 at 5:07 am
Ok I'm trying that now
Another questions I've entered the code below in SSRS query windows
select distinct Item
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')
I want the user to be able to select two or more items A101 and A102 and the data to be shown in the grid. However, when i select A101 it works perfectly but include A102 it appears blank.
Can anyone tell me the reason why???
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 3, 2011 at 5:30 am
I take it you're allowing muliple selects in the SSRS, one thing to remember is that SSRS will send multiple items as a CSV string. eg 'A102,A103'.
So what you need to do is write a string parser for to seperate them and place them in a table. A Table value function works very well, heres an example http://www.codeproject.com/KB/database/splitparameterstring.aspx
The only change I would suggest to the artile is that you alter the 'How to Use' code so that its either an Inner or Outer Join depending on how you want the data.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2011 at 8:26 am
Thanks for your reply Jason-299789 I'm going through this article now and will get back to you in a few hours with my results
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 3, 2011 at 8:28 am
Actually performance wise you're better off using a temp table with Clustered PK which drastically helps on the plan generation.
October 6, 2011 at 7:50 am
@ Jason and Ninja thanks for your replies
@ Jason I've read through the article and i must admit i am struggling a little to understand how to relate function concept to my problem for a few days. So that i understand this correctly can you clarify a few points please.
1. There are three types of functions(scalar, inline table value and multi-statement table value) and the one that i should use is inline table value?
2. Create a function to place the values such as A101, A102 etc into a new table, then inner join it to the original table that i've taken the values from. In my SP i can refer to the newly created function and in theory should allow me to select multiple values in SSRS???
@ Ninja originally i have tried to create a temp table in a SP. However, it doesn't matter what i try SSRS does not read temp tables i continuously get an error.:(
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 6, 2011 at 7:53 am
@ Ninja originally i have tried to create a temp table in a SP. However, it doesn't matter what i try SSRS does not read temp tables i continuously get an error.
Here's the workaround. Run the report, then hit refresh to get the new list of columns / parameters.
It's the same error in a sp or query & the same workaround.
October 6, 2011 at 8:55 am
Hi Ninja
I've done exactly as what you said and this is the error that i am getting
An error occurred during local report processing.
An error has occurred during report processing
Query execution failed for dataset item
invalid object name '#Temp'
Can you show me a clear example from the database that i have upload and the project of how to get it working using the temp table???
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 6, 2011 at 8:58 am
Might be simpler for you to post your query. It sounds like a simple syntax error.
Can you run that query in ssms all by itself?
October 6, 2011 at 9:10 am
This this the SP i'm using at the present moment
USE [DSReports]
GO
/****** Object: StoredProcedure [dbo].[Waterfall_New] Script Date: 10/06/2011 15:42:53 ******/
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'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HorizontalTable1]') AND type in (N'U'))
DROP TABLE [dbo].[HorizontalTable1]
-- 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 *
-- 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
select distinct Item
, ShipTo
, Class1
, Class2
, Class3
, Class4
, SysFcst#1
, SysFcst#2
, SysFcst#3
, SysFcst#4
, AdjFcst#1
, AdjFcst#2
, AdjFcst#3
, AdjFcst#4
into #temp
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
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 6, 2011 at 9:11 am
.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 6, 2011 at 9:30 am
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.
October 7, 2011 at 1:59 am
The sp can run within ssms on my system would you like a copy of the DB?
Do you mean the dataset within SSRS??
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 7, 2011 at 2:07 am
Jnrstevej (10/7/2011)
The sp can run within ssms on my system would you like a copy of the DB?Do you mean the dataset within SSRS??
No & yes.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply