时间戳

首页 > 解决方案 > SSRS CASE 基于参数选择然后传递 Select 语句

sql - SSRS CASE 基于参数选择然后传递 Select 语句

问题描述

我有一个带有 3 个选项的参数的 SSRS 报告

  1. 客户 = 所有
  2. 客户 = A（超过 1 个客户）
  3. 客户 = 全部 - A

根据选择，我希望 SQL 是

Case 2
 Select * from customers where customer in (A)
Case 3
 Select * from customers where customer not in (A)
Else
 Select * from customers

我的参数是： [![从客户不在 (A) 中的客户中选择 *][1]][1] [1]: https://i.stack.imgur.com/mWZoU.png

我的选择语句目前是：

    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

但我似乎无法得到正确的陈述：

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

标签： sqlselectreporting-servicesparameter-passingcase

解决方案

  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 )

推荐阅读