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

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

• sql
• sql-server
• reporting-services
• ssrs-2008-r2

• 2 (p.InsertDT >= @.StartDat OR @.StartDat IS NULL) AND ... – jarlh Commented Feb 7 at 22:41
• Will the user always be selecting a date range, or is it an optional parameter? – Zack Commented Feb 7 at 23:08
• It will be optional, but I think in time, they will want to select a date range and get a list of successful or failed payments within a month. At least I am trying to think downstream. – jr7138 Commented Feb 7 at 23:15
• You might want to brush up on your AND/OR logic, you have 3 conditions you are ORing together, OR means only one condition has to match... so for example, if it meets the date condition, it ignores the other two conditions. Potentially just changing those two ORs to ANDs might work, but I don't fully understand your logic. However you should now be able to work it out yourself. Note if you do still have any ORs, the OR condition must be in brackets else AND/OR precedence will also screw up your results. – Dale K Commented Feb 8 at 3:33
• 1 Read up on "kitchen sink" queries to understand the logic required in your WHERE clause. Then set aside a day to read and understand the contents of Erland Sommarskog's article, Dynamic Search Conditions in T‑SQL, to see why this sort of stuff can easily cause performance problems. – AlwaysLearning Commented 2 days ago

1 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);