Skip to main content
Planview Customer Success Center

How to filter on a Report Designer provider that is linked with a left outer join to the main provider?

Question:
A secondary provider has been linked to a primary provider via "left outer join". A report uses both providers, and has a design filter to filter data based on the values coming from the secondary provider. Due to the filter on the secondary provider, report returns records that only exist in both providers, hence the "left outer join" turns into an "inner join" in practice.

How can we filter on a provider that is linked with a left outer join to the main provider, so that the outer join is preserved i.e. records from the primary provider would still be displayed even if there is no corresponding value in the secondary provider?


Answer:
Instead of applying the filter on the secondary provider, add a custom column to the data collection, which has a “Case” directive for covering NULL values, and filter on this custom field instead.

The formula for the custom field should be in the form of "Select Case [Field] is null or [Field] = 0 Then 0 Else [Field] End".