sql - SSRS 报告生成而不选择所有参数

Question

我正在设置一个没有必填字段的 SSRS 报告，需要多个值。

根据网站 https://www.mssqltips.com/sqlservertip/3502/allow-null-value-in-multi-value-report-parameter-in-sql-server-reporting-services/

, 客户端仍然需要输出而不提供任何输入，即如果有 10 个参数，我们需要基于选择 3 或 4 或任意数量的其他参数的输出。

我将按照
https://www.tutorialgateway.org/sql-query-to-select-all-if-parameter-is-empty-or-null/完成的查询 检查这一行

WHERE Occupation = IIF(@Occupation IS NULL, Occupation, @Occupation)

这也在我的代码中使用。

SELECT [Order].[PartNumber],
        [Order].[BuildCode],[Order].[Product],[Order].[AssemblyUnitName],[Order].[ProductionStatus]  ,
            SUM([Order].[QTY]) AS [QTY]  FROM  (SELECT [Order].[PartNumber],[Order].[AssemblyUnitName],[Order].[BuildCode]    ,
    CASE WHEN 
        [Order].[BuildCode] LIKE '%-230B-%' THEN     
    CASE WHEN 
        [Order].[BuildCode] LIKE '%-HIGH-%' THEN '230B-HIGH' WHEN [Order].[BuildCode] LIKE '%-LOW-%' THEN '230B-LOW'        
            ELSE '230B-SPORT'     END    
            ELSE     
    CASE WHEN 
        [Order].[BuildCode] LIKE '%-0TAL-%' THEN 'NCAP-0T' WHEN [Order].[BuildCode] LIKE '%-AURA-%' THEN 'NCAP-AURA'    WHEN [Order].[BuildCode] LIKE '%-DUNE-%' THEN 'NCAP-DUNE'        WHEN [Order].[BuildCode] LIKE '%-24TK-%' THEN 'NCAP-24TK'        WHEN [Order].[BuildCode] LIKE '%-COST-%' THEN 'NCAP-COSTA'        ELSE 'NCAP-2T'     
            END    END   
    +CASE WHEN 
        [Order].[AssemblyUnitName] ='230B FS RH MAIN' THEN '-RH'        
            WHEN [Order].[AssemblyUNitName] ='230B FS LH MAIN' THEN '-LH'        
            WHEN [Order].[AssemblyUNitName] ='230B RSB 40 MAIN' THEN '-RSB40'        
            WHEN [Order].[AssemblyUNitName] ='230B RSB 60 MAIN' THEN '-RSB60'        
            WHEN [Order].[AssemblyUNitName] ='230B RSB 100 MAIN' THEN '-RSB100'        
            WHEN [Order].[AssemblyUNitName] ='230B RSC 100 MAIN' THEN '-RSC100'   
            END AS [Product]  ,
    CASE WHEN 
        [Order].[ProcessStatus]>=50 AND [Order].[ProductionStatus]>=100 THEN 'FG'        
            WHEN MAX([Operation2].[Status]) IS NULL THEN 'FG'        
            WHEN [Order].[ProcessStatus]>=20 OR [Order].[ProductionStatus]>=20 THEN 'WIP' END AS [ProductionStatus]  ,
            1.0 AS [QTY]  ,
        SUBSTRING('>= CAST(CAST({FN NOW()} AS DATETIME)-60.2430555555555556',0,0) AS 'UNWANTED'  
            FROM [Order] WITH (NOLOCK)    LEFT JOIN [Operation] AS [Operation2] WITH (NOLOCK) ON  [Order].[Id]=[Operation2].[OrderId] AND [Operation2].[OperationTemplateReference]<>1039 AND [Operation2].[Status]<>'F'   
             WHERE 

[Order].[PartNumber] in (IIF(@PartNumber IS NULL, [Order].[PartNumber], @PartNumber)  )
and [Order].[BuildCode] in( IIF(@BuildCode IS NULL, [Order].[BuildCode], @BuildCode) )
and [Order].[AssemblyUnitName] in (IIF(@AssemblyUnitName IS NULL, [Order].[AssemblyUnitName], @AssemblyUnitName) )

GROUP BY 
                [Order].[Id],[Order].[OrderNumber],
                [Order].[PartNumber],[Order].[BuildCode], 
                                [Order].[AssemblyUnitName],
                [Order].[IsScrap],[Order].[ProcessStatus],
                [Order].[ProductionStatus]) AS [Order]  GROUP 
                                BY [Order].[PartNumber],
                [Order].[BuildCode],[Order].[Product],
                [Order].[AssemblyUnitName],
                [Order].[ProductionStatus]  ORDER BY [Order]. 
                         [BuildCode]  OPTION (MAXDOP 2, MAX_GRANT_PERCENT=10)

预期输出：不选择参数的 SSRS 报告生成。如果查询的where条件有任何错误，请集思广益。

电流输出：

标签： sqlsql-serverreporting-services

Answer 1

谢谢@Alan Schofield，我换了

[Order].[PartNumber] in (IIF(@PartNumber IS NULL, [Order].[PartNumber], @PartNumber)  )
and [Order].[BuildCode] in( IIF(@BuildCode IS NULL, [Order].[BuildCode], @BuildCode) )
and [Order].[AssemblyUnitName] in (IIF(@AssemblyUnitName IS NULL, [Order].[AssemblyUnitName], @AssemblyUnitName) )

对此。

(@PartNumber IS NULL OR [Order].[PartNumber] IN (@PartNumber))
and (@BuildCode IS NULL OR [Order].[BuildCode] IN(@BuildCode) )
and (@AssemblyUnitName IS NULL OR [Order].[AssemblyUnitName] IN (@AssemblyUnitName) )

包括来自上述两个站点的信息，我们将得到报告。