Parameter dependence?

• SQL_path

  SSCrazy

  Points: 2810

  More actions

  May 25, 2012 at 8:17 am

  #269535

  Hi,

  I have multiselect prompts in my report as @color, @size, @shape, @material.

  based on @start & @end > @color populates

  I am looking for :

  @size should have dropdown values based on @color choices

  @shape should have dropdown values based on @size choices

  @material should have dropdown values based on @shape choices

  The link between them is shipped_on. It is some hybrid thing. Any ideas on query please? Thanks.

  --Table structure

  create table #ITEM

  (

  Item_id int ,item_desc varchar(50) )

  INSERT INTO #ITEM (Item_id, Item_desc)

  SELECT 1,'DateOrdered'

  UNION ALL

  SELECT 2,'Color'

  UNION ALL

  SELECT 3,'Size'

  UNION ALL

  SELECT 4,'Shape'

  UNION ALL

  SELECT 5,'Material'

  select * from #ITEM

  drop table #ITEM

  create table #Orders

  (

  Order_id int ,item_id int

  ,order_detail varchar(40)

  ,shipped_on datetime)

  INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

  SELECT 11,1,'1/1/2012','5/1/2012 1:37:00' --dates are in mmddyy

  UNION ALL

  SELECT 22,2,'black','5/1/2012 1:37:00'

  UNION ALL

  SELECT 33,3,'50','5/1/2012 1:37:00'

  UNION ALL

  SELECT 44,4,'square','5/1/2012 1:37:00'

  UNION ALL

  SELECT 55,5,'iron','5/1/2012 1:37:00'

  INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

  SELECT 61,1,'2-3-2012','5/4/2012 3:07:20' --dates are in mmddyy , orderid is unique pk and Item id is unique pk

  UNION ALL

  SELECT 62,2,'green','5/4/2012 3:07:20'

  UNION ALL

  SELECT 63,3,'10','5/4/2012 3:07:20'

  UNION ALL

  SELECT 64,4,'round','5/4/2012 3:07:20'

  UNION ALL

  SELECT 65,5,'plastic','5/4/2012 3:07:20'

  INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

  SELECT 81,1,null,'9/4/2012 3:17:20' --dates are in mmddyy , orderid is unique pk and Item id is unique pk

  UNION ALL

  SELECT 82,2,'yellow','9/4/2012 3:17:20'

  UNION ALL

  SELECT 83,3,'20','9/4/2012 3:17:20'

  UNION ALL

  SELECT 84,4,'square','9/4/2012 3:17:20'

  UNION ALL

  SELECT 85,5,'nylon','9/4/2012 3:17:20'

  select * from #Orders

  drop table #Orders

• gsc_dba

  SSCertifiable

  Points: 5407

  More actions

  June 1, 2012 at 8:28 am

  #1495898

  Is the "Black iron square (size) 50" what you are after in your example?

  gsc_dba