I have joined a Fact Table (Policy) to a Bridge table (Group Policies). Since I have records in the Fact Table that are not in the Bridge Table, I have done a Left Outer Join Unique. I also have a Dimension Table (Group Dim) Left Outer Join Unique to the Bridge Table. My Group Dim fields are all set Missing = ON. I am also inner joining to a Month Dim (Snapshot Month Key) once and a Data Dim twice (Pol Eff Date and Pol Exp Date).

I am now trying to view in the Info Assist canvas Active Policies and their Premium that are not associated to a Group.

I initially set my report as follows:
Row By = Policy Number and Group Account (unselect "Hide Missing" Display Option on each)
Measure = Policy Premium (unselect "Hide Missing" Display Option)
Filter = Where Snapshot Month Equal and a Grouped Where "AND" Statement for Pol Eff Date and Pol Exp Date

When I run this, I get all records from the Fact with the Group Account displaying values and as missing ("-") accordingly.

Here is where the problem occurs:
If I apply the filter "Group Account Not Missing" the Run correctly returns only records with a Group Account.
However, when I change the filter to "Group Account Missing" and Run, the report returns with no records.

What am I doing wrong that is preventing the missing Group Account records from being returned using the Missing Filter?

Thank you for your help.

This message has been edited. Last edited by: FP Mod Chuck,

Can you test your code by running it with SQL Trace and post the results from both with and without the filter?

SQL Trace result for Missing

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1,
ipfowner.dbo.MTH_DIM T2,
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5,
IPFOWNER.dbo.GRP_DIM T6,
ipfowner.dbo.DT_DIM T16,
ipfowner.dbo.DT_DIM T17
WHERE
(T2."MTH_KEY" = T1."MTH_KEY") AND
(T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID") AND
(T6."GRP_KEY" = T5."GRP_KEY") AND
(T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") AND
(T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") AND
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T6."GRP_CD" IS NULL) AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...



SQL Trace result for Not Missing

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1,
ipfowner.dbo.MTH_DIM T2,
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5,
IPFOWNER.dbo.GRP_DIM T6,
ipfowner.dbo.DT_DIM T16,
ipfowner.dbo.DT_DIM T17
WHERE
(T2."MTH_KEY" = T1."MTH_KEY") AND
(T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID") AND
(T6."GRP_KEY" = T5."GRP_KEY") AND
(T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") AND
(T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") AND
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T6."GRP_CD" IS NOT NULL) AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...



I also tried to Filter Group Account = _FOC_NULL or _FOC_NONE. Here is the SQL Trace for that

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
( ( ( ( ( ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1
INNER JOIN
ipfowner.dbo.MTH_DIM T2
ON (T2."MTH_KEY" = T1."MTH_KEY") )
LEFT OUTER JOIN
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5
ON T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID" )
LEFT OUTER JOIN
IPFOWNER.dbo.GRP_DIM T6
ON T6."GRP_KEY" = T5."GRP_KEY" )
INNER JOIN
ipfowner.dbo.DT_DIM T16
ON (T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") )
INNER JOIN
ipfowner.dbo.DT_DIM T17
ON (T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") )
WHERE
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...

Thanks again

Could YR_MTH be both equal to 201905 AND >= 197501?

The >=197501 filter is something I added to the Month Dim to eliminate all dates prior to the inception of our company. It is being applied to other reports/charts and is not creating any issue.

Looking at your first two SQL Traces, they are using an implicit join between tables, which acts as an INNER JOIN and only brings back all matching records. So there will be no missing values. Try setting your join to be a LEFT_OUTER MULTIPLE or SET SHORTPATH = SQL.

Read more in the docs: Handling a Missing Segment Instance

There was also a really good webinar on joins that you can watch here: FOCUS: JOIN in Depth and FOCUS/XFOCUS Database Internals

Thank you Hallway.

That information was extremely helpful. I had the Left outer join set, but I switched the relation from Unique to Multiple. However, that still did not correct the issue. The implicit join was still defaulting when I added the Not Missing or Missing filter.

I ended up also having to add SET ALL = PASS manually by editing the report with text editor.

Thank you for your help

That's great! I'm so glad that you found a resolution.

Don't forget to prepend '[Solved]' to the title of the post