I have a SSRS report with a parameter with 3 options
- Customers = All
- Customers = A (more than 1 customer)
- Customers = All – A
Based on selection I want the SQL to be
Case 2 Select * from customers where customer in (A) Case 3 Select * from customers where customer not in (A) Else Select * from customers
And my select statements are currently:
select t.Docket, t.TranType, Convert(Date,t.Despatchdatetime) as DespatchDate, t.Depot, t.Plant, t.Product, p.ProductName, t.Gross, t.Net, t.Tare, t.Vehicle, t.Rego, p.ProductName + ' ' + t.Docket as TEXT, T.Vehicle + ' ' + T.Rego as [Field Assignment], t.Customer + ' ' + t.CustomerName as Customer from trans t, product p where t.depot in (@Depot) and t.plant in (@Plant) and t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= (@DespatchTo) and p.Product = t.product and t.cancelleddocket = '0' and t.trantype <> 'DIV' ** and t.customer [Depending on parameter selection] Order by t.plant, t.despatchdatetime,t.docket
But I can’t seem to get the statement right:
SELECT CASE WHEN Parameters!Customers.Value='Exclude' then ( select t.Docket, t.TranType, Convert(Date,t.Despatchdatetime) as DespatchDate, t.Depot, t.Plant, t.Product, p.ProductName, t.Gross, t.Net, t.Tare, t.Vehicle, t.Rego, p.ProductName + ' ' + t.Docket as TEXT, T.Vehicle + ' ' + T.Rego as [Field Assignment], t.Customer + ' ' + t.CustomerName as Customer from trans t, product p where t.depot in (@Depot) and t.plant in (@Plant) and t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= (@DespatchTo) and p.Product = t.product and t.cancelleddocket = '0' and t.trantype <> 'DIV' and t.customer not in ('123','456') Order by t.plant, t.despatchdatetime,t.docket ) WHEN Parameters!Customers.Value='ABL' then (select t.Docket, t.TranType, Convert(Date,t.Despatchdatetime) as DespatchDate, t.Depot, t.Plant, t.Product, p.ProductName, t.Gross, t.Net, t.Tare, t.Vehicle, t.Rego, p.ProductName + ' ' + t.Docket as TEXT, T.Vehicle + ' ' + T.Rego as [Field Assignment], t.Customer + ' ' + t.CustomerName as Customer from trans t, product p where t.depot in (@Depot) and t.plant in (@Plant) and t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= (@DespatchTo) and p.Product = t.product and t.cancelleddocket = '0' and t.trantype <> 'DIV' and t.customer in ('123','456') Order by t.plant, t.despatchdatetime,t.docket ) ELSE (select t.Docket, t.TranType, Convert(Date,t.Despatchdatetime) as DespatchDate, t.Depot, t.Plant, t.Product, p.ProductName, t.Gross, t.Net, t.Tare, t.Vehicle, t.Rego, p.ProductName + ' ' + t.Docket as TEXT, T.Vehicle + ' ' + T.Rego as [Field Assignment], t.Customer + ' ' + t.CustomerName as Customer from trans t, product p where t.depot in (@Depot) and t.plant in (@Plant) and t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= (@DespatchTo) and p.Product = t.product and t.cancelleddocket = '0' and t.trantype <> 'DIV' Order by t.plant, t.despatchdatetime,t.docket ) END
Advertisement
Answer
select t.Docket, t.TranType, Convert(Date,t.Despatchdatetime) as DespatchDate, t.Depot, t.Plant, t.Product, p.ProductName, t.Gross, t.Net, t.Tare, t.Vehicle, t.Rego, p.ProductName + ' ' + t.Docket as TEXT, T.Vehicle + ' ' + T.Rego as [Field Assignment], t.Customer + ' ' + t.CustomerName as Customer from trans t join product p ON p.Product = t.product where t.depot in (@Depot) and t.plant in (@Plant) and t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= (@DespatchTo) and t.cancelleddocket = '0' and t.trantype <> 'DIV' and (( @Customers = 'Exclude' and t.customer not in ('123','456') ) OR(@Customers='ABL' AND t.customer in ('123','456') ) OR @Customers='ALL' ) Order by t.plant, t.despatchdatetime,t.docket )