SSRS IIF parameter equal to NULL run report as normal

Question

I'm writing a report in SSRS which requires a search parameter to filter the report. The parameter is setup to default allow nulls which should let the report run as normal.

The report run's, however nothing get's returned until I ad something into into the search parameter.

Is it possible to use an IIF expression to say that if the parameter is null run the report as normal?

Here is the query I'm using to generate the dataset within SSRS.

CREATE TABLE #StartDateTable(

  stSecurityType varchar(10) NOT NULL,
  stSecuritySymbol varchar(50) NOT NULL,
  stPrice float NOT NULL,
  stSecurityID int NOT NULL,
  stPriceDate date NOT NULL
)

INSERT INTO #StartDateTable (stSecurityType, stSecuritySymbol, stPrice, stSecurityID, stPriceDate )
    SELECT DISTINCT 
       Instruments.SecurityType, Instruments.SecuritySymbol, 
       InstrumentPrice.Price, InstrumentPrice.SecurityID, InstrumentPrice.PriceDate
    FROM 
       InstrumentPrice
    JOIN 
       Instruments ON Instruments.ID = InstrumentPrice.SecurityID
    WHERE 
       InstrumentPrice.PriceDate = @StartDate;

CREATE TABLE #EndDateTable
(
      etSecurityType varchar(10) NOT NULL,
      etSecuritySymbol varchar(50) NOT NULL,
      etPrice float NOT NULL,
      etSecurityID int NOT NULL,
      etPriceDate date NOT NULL
)

INSERT INTO #EndDateTable (etSecurityType, etSecuritySymbol, etPrice, etSecurityID, etPriceDate)
   SELECT DISTINCT 
      Instruments.SecurityType, Instruments.SecuritySymbol, 
      InstrumentPrice.Price, InstrumentPrice.SecurityID, 
      InstrumentPrice.PriceDate
   FROM 
      InstrumentPrice
   JOIN 
      Instruments ON Instruments.ID = InstrumentPrice.SecurityID
   WHERE 
      InstrumentPrice.PriceDate = @EndDate;

SELECT * 
FROM #StartDateTable
LEFT JOIN #EndDateTable ON #EndDateTable.etSecurityID = #StartDateTable.stSecurityID

I setup the search parameter as a filter on the dataset within SSRS with a LIKE as I want it to be a wildcard.

Tak Tak · Accepted Answer · 2014-09-09T14:26:29

There are 2 ways to do this. Either filter at Dataset with a where clause or below is using group filter.

From the group properties use between and use expression..

Example =IIF(Isnothing(Parameters!Param1.Value) = true, 0, Parameters!Param1.Value) and =IIF(Isnothing(Parameters!Param1.Value) = true, 999999, Parameters!Param1.Value)

enter image description here

SSRS IIF parameter equal to NULL run report as normal

1 Answers