SSRS Report Generation without selecting all parameters

Question

I'm setting a SSRS Report with no Mandatory fields, Need Multiple values.

As per the site https://www.mssqltips.com/sqlservertip/3502/allow-null-value-in-multi-value-report-parameter-in-sql-server-reporting-services/

, still the client needs the output without providing any input, i.e, If there is 10 parameters, we need the output based upon selecting the 3 or 4 or any number of other parameters.

I will the query which is done as per
https://www.tutorialgateway.org/sql-query-to-select-all-if-parameter-is-empty-or-null/ check this Line

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

This is used in my code too.

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)

Expected Output: SSRS Report Generation without selecting the parameters. If there is any wrong in the where condition of the query, please brainstorm me.

Current Output:

What is your reasoning for using NOLOCK here? Are you "ok" with incorrect result sets based on dirty reads, or reading difference versions of the same row in a single scan? — Larnu
As for your query, personally I'm not a fan of SSRS's tactic of injecting delimited lists into SQL. If you, change your query to a Stored Procedure and use a string splitter to split the delimited list provided by SSRS. if you're on SSRS 2016+ then use STRING_SPLIT. — Larnu
I typically use something like WHERE (@PartNumber IS NULL OR [Order].[PartNumber] IN (@PartNumber)) and don't have any issues — Alan Schofield

Sarath Mohandas Sarath Mohandas · Accepted Answer · 2019-07-06T09:27:44

Thanks @Alan Schofield, I replaced

[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) )

to this.

(@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) )

Including the info from the above two sites, we will get reports.

SSRS Report Generation without selecting all parameters

1 Answers