SSRS Date Parameter and second parameter

Question

I have an SSRS report with four parameters:

• StartDate
• EndDate
• Payment Ref Number
• Payment Status

They work well as stand-alone parameters. But I can't figure out, when using the StartDate and EndDate parameters and Payment Status to work together. When I have Start and End Dates, and select an option from the Payment Status, I only want to see the data for that status within the Start and End date range.

Here is a screenshot of my issue, it is displaying all the data and not taking the Payment status into consideration:

My where clause is as follows:

WHERE
(p.InsertDT >= @StartDate and p.InsertDT <= @EndDate)
or
p.PaymentReferenceNumber = @PaymentRefNumber
or
(ps.Description = @PaymentStatus OR @PaymentStatus = 'All')

Answer 1

You used the OR to allow some filters not to be filled, but in fact it makes any matching condition to allow for the row to appear in the results.

Let's consider a simplified, 2-filter example: condition1 or condition2:

• If your user only fills condition1,
  condition2 will be null which will never match anything (that's the magic of NULL),
  but it is ORed with condition1 so rows matching condition1 will pass:
  all in all, you get the expected result.
• But if your user fills both criteria,
  a row matching condition1 but not condition2 will still pass, due to the or.

What you want

You want something like:

• condition1 if it is filled by the user
• and condition2 if it is filled by the user
• and so on.

This is exactly what you did (correctly) with your:
(ps.Description = @PaymentStatus OR @PaymentStatus = 'All')
which means: "the field matches the selection or there was no selection" (All being the default value, sent for no specific value selection).

So, you could rewrite your query as:

WHERE
    (p.InsertDT >= @StartDate OR @StartDate IS NULL)
AND (p.InsertDT <= @EndDate OR @EndDate IS NULL)
AND (p.PaymentReferenceNumber = @PaymentRefNumber OR @PaymentRefNumber IS NULL)
AND (ps.Description = @PaymentStatus OR @PaymentStatus = 'All')

(just ensure PowerBI passes the interface's empty fields as NULL)

What you could do better

In order not to rely on the DB's optimizer to recognize that the IS NULL should be tested first in order to ignore the corresponding test,
it would be better to only pass the minimal query to the server.

Thus, if you have the opportunity to build you query dynamically, you should do something like that:

where = 'WHERE 0=0'; // This one generally gets well optimized…
params = {};
if(StartDate != NULL)
{
    where += 'AND p.InsertDT >= @StartDate';
    params['@StartDate'] = StartDate;
}
if(EndDate != NULL)
{
    where += 'AND p.InsertDT <= @EndDate';
    params['@EndDate'] = EndDate;
}
if(PaymentRefNumber != NULL)
{
    where += 'AND p.PaymentReferenceNumber = @PaymentRefNumber';
    params['@PaymentRefNumber'] = PaymentRefNumber;
}
if(PaymentStatus != 'All')
{
    where += 'AND ps.Description = @PaymentStatus';
    params['@PaymentStatus'] = PaymentStatus;
}
results = runQuery('…'+where+' ORDER BY …', params);