Friday 7 November 2014

SSRS - Cascade Parameter using Dataset

--Add Dataset to SSRS Report Builder
--1.DataSet (Warehouse)
select * into #TempR from
(select '1' Warehouse
union all
select '2'
union all
select 'AB')P

select * from #TempR

drop table #TempR

--2.DataSet (Item and Price)
select * into #TempR from
(select '1' Warehouse, 'Widget'Item, '$1' Price
union all
select '1', 'Pens', '$2'
union all
select '1', 'Cards', '$5'
union all
select '2', 'Popcorn', '$2'
union all
select '2', 'Boxes', '$10'
union all
select 'AB', 'Ruler', '$10'
union all
select 'AB', 'Stapler', '$12')P

select * from #TempR
where Warehouse=@WareHouse


--Add WareHouse Parameter in Report

1.Create Parameter Name with 'WareHouse'.
2.Go to 'Available Value' in Parameter Property.
3.Go to Get Value from Query.
4.Select Dataset and Value



--Select svailable value from Dataset




--Add ItemandPrice Parameter in Report and select available value

Follow Same Steps.




--Take Textbox 
1.Right Click on Textbox
2.Goto Expression
3.Paste =Cstr(Parameters!Item.Value)+" Price :"+First(Fields!Price.Value, "ItemANDPrice")




Output:
Select Parameter



No comments:

Post a Comment