Multipass SQL with null date fields that need to be joined

[bridgeconsultant]

MSTR 7.2.2

I have a report with a date attribute and four metrics. One of the metrics is dimensional metric that generated multipass SQL (really more like subqueries). When the passes are joined on the datefield, we lose records. Typically, in SQL, we do a coalesce. MSTR only does a coalese when using full outer join.

Counts:

Should get 49 records.
Currently get 38 records.
Full outer joing generates 60 records.

Any ideas would be appreciated?

If there is no MSTR setting, I'll probably put a coalesce in an ApplySimple in the Attribute definition.

Thanks

 

[kpescatore]

Have you tried Messing with the VLDB properties or setting one or more of the metrics to be outer joined under the menu Data -->Report Data Options --> Metric Join Type

 

[bridgeconsultant]

I have tried that. Still didn't work. Full outer join (outer join on all metrics in report data options) does generate a coalesce but it retrieves too many rows due to the outer joing (should be 49...retrieves 60)

 

[entaroadun]

Any luck with this? If not, then repost with details on your dimensions and metrics.

 

[bridgeconsultant]

I have had no luck with this using MSTR options (VLDB, etc.) I opted for DB solution in the views for all date fields that may have nulls (below):

   Coalesce(CAST(ScheduledCompletionDate AS VARCHAR(10)), '?'), 
   Coalesce(CAST(ActualCompletionDate AS VARCHAR(10)), '?'),