May 30, 2012 at 8:54 am
Hi,
I have multiselect prompts in my report as @color, @size, @shape , @MAterial
based on @start & @end ;
@color populates then
> @size populates based on @color
> @Shape populates based on @Color
> @MAterial populates based on @Shape
Now everthing works fine except when there are null values..
For instance when I select range containign '1/6/2012' from below table, my report should show @ material dropdown values as 'Wood' despite prior selections being null. any ideas please?
Thanks !!
create table #ITEM
(
Dates datetime, color varchar(50), size varchar(50), shape varchar(50), material varchar(50) )
INSERT INTO #ITEM
(Dates, color , size , shape , material)
SELECT '1/1/2012','black', '50', 'sqaure', 'IROn'
UNION ALL
SELECT '1/1/2012','black', '100', 'round', 'plastic'
UNION ALL
SELECT '1/2/2012','green', '100', 'rectangular', 'plastic'
UNION ALL
SELECT '1/4/2012','white', '100', 'rectangular', 'plastic'
UNION ALL
SELECT '1/5/2012','white', null, 'oval', 'wood'
UNION ALL
SELECT '1/6/2012',green, null, null, 'wood'
select * from #ITEM
drop table #ITEM
--COLOR DATASET values come from
Select color from table where dates between @start and @end
--@size DATASET values come from
Declare @tab table
(size varchar(50), color varchar(50) )
INsert into @tab
Select size, color from #Item
select size where color=@color
--@Shape DATASET values come from
Declare @tab table
(Shape varchar(50), Size varchar(50) )
INsert into @tab
Select Shape ,size from #Item
select Shape where Size=@Size
--@MAterial DATASET values come from
Declare @tab table
(Shape varchar(50), material varchar(50) )
INsert into @tab
Select Shape ,material from #Item
select Material where Shape=@Shape
May 30, 2012 at 10:02 am
How about
declare @Shape varchar(20)
set @Shape = 'oval'
select Material from #ITEM where ISNULL(Shape,@Shape)=@Shape
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 30, 2012 at 2:48 pm
Something like this?
create table #item
(dates datetime, color varchar(50), size varchar(50), shape varchar(50), material varchar(50))
insert into #item (dates, color , size , shape , material)
select '1/1/2012','black', '50', 'sqaure', 'iron'
union all
select '1/1/2012','black', '100', 'round', 'plastic'
union all
select '1/2/2012','green', '100', 'rectangular', 'plastic'
union all
select '1/4/2012','white', '100', 'rectangular', 'plastic'
union all
select '1/5/2012','white', null, 'oval', 'wood'
union all
select '1/6/2012','green', null, null, 'wood'
select * from #item
declare @start as datetime
set @start = '1/6/2012'
declare @end as datetime
set @end = '1/6/2012'
--color dataset values come from
declare @color as varchar(50)
declare @size as varchar(50)
declare @shape as varchar(50)
select @color = color from #item where dates between @start and @end
select @size = size from #item where dates between @start and @end
select @shape = shape from #item where dates between @start and @end
select @start as start, @end as [end], @color as color, @size as size, @shape as shape
--@size dataset values come from
declare @tab1 table(size varchar(50), color varchar(50))
insert into @tab1
select size, color from #item
select * from @tab1 where isnull(color,'')=isnull(@color,'')
--@shape dataset values come from
declare @tab2 table(shape varchar(50) default(null), size varchar(50) default(null))
insert into @tab2
select shape,size from #item
select * from @tab2where isnull(size,'')=isnull(@size,'')
--@material dataset values come from
declare @tab3 table (shape varchar(50), material varchar(50))
insert into @tab3
select shape ,material from #item
select * from @tab3 where isnull(shape,'')=isnull(@shape,'')
drop table #item
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply