How can I write a WHERE clause to cater for optional paramerers on an SSRS report?

Question

I am trying to get a basic SSRS report to be filterable by any optional parameters. The 2 parameters to filter should be either between 2 dates or by a SalesID. I can get each parameter to filter individually but when I add them together in a statement and try to run the report it says "parameter cannot be blank".The advices I read online says me to check the "allow blank values" in the parameter properties but this is not available for Date/Time.

I read them online and it asks me to check the "allow blank values" in the parameter properties but this is not available for Date/Time. I've also tried adding numerous variations of the WHERE clause using "OR IS NULL" but that doesn't work either.I also read that I might need to add an =IIF clause somewhere but I have never done this before.

Select SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE (SalesDate BETWEEN @StartDate AND @EndDate AND SalesID IS NULL) OR 
      (SalesID = @SalesID AND @StartDate IS NULL and @EndDate IS NULL) 

The above code runs if I select a Start Date and End Date but it only pulls through the SalesDate data and the other fields are blank.

I want the report to be filterable by either a Start and End Date or SalesID, not both. At present the above returns an error that-StartDate cannot be blank. As I mentioned above, I cannot check the "allow blank values". Thanks in advance.

Answer 1

Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.

Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):

Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:

SELECT  SalesID, SalesDate, SaleValue
FROM    SalesDb
WHERE   SalesId = ISNULL(@SalesId, SalesId)
AND     SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)

Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:

1. Equal to the @SalesId parameter if it isn't null, or
2. Equal to the rows SalesId value if @SalesId IS null

This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.

Here's the test harness I used, which may be useful to you or others.

CREATE TABLE SalesDb
(
    SalesID INT NOT NULL,
    SalesDate DATETIME NOT NULL,
    SaleValue DECIMAL(10, 2),
)
GO

INSERT
INTO    SalesDb
        (
            SalesID, SalesDate, SaleValue
        )
VALUES  (15, '2019-01-01', 12),
        (16, '2019-01-02', 34),
        (16, '2019-01-03', 56),
        (16, '2019-01-04', 78)

DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL

SELECT  SalesID, SalesDate, SaleValue
FROM    SalesDb
WHERE   SalesId = ISNULL(@SalesId, SalesId)
AND     SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)