Why do I see no data when I use counties.country_id or provinces.prov_id in an "In List" filter?

Environment:

Analytics
Oracle B2C Service

Resolution:

Filtering on a data table that populates a menu will not show joined table results with no value for that menu field.

Our software has a table of countries which is a list of all countries that you have added to your site through the Configuration -> Internationalization tool.  This tool populates the countries table with rows based on additions to the tool.  Rows are only added with data and there are no empty, No Value, rows.

The countries table is joined to the organization addresses table through the org_addrs.country_id field.  When you create a new organization record you can select from the list of countries or chose to not enter a value for the org_addrs.country_id field. 

When creating a report on organizations, you can technically use either field, org_addrs.country_id or countries.country_id, in an In List report filter on the country value.

Using org_addrs.country_id in the filter, you will be able to select from all actual country values contained in your organizations records.  These values may contain No Value. Therefore if you check the No Value checkbox (or Select All) in the report filter you will get results with no organization address country value.

Using org_addrs.country_id in the filter, you will be able to select from all possible country values contained in the countries table.  There is not a No Value row in this table therefore the No Value option will not appear as a checkbox.  When you run the report only rows with a selected checkbox will appear and therefore no organizations without a organization address country value will be displayed in results.

Note that there is automatic behavior built in to the provinces.prov_id field that shows country/state/province.  This functionality can be used in a report field to show more than just the country but is not available in report filters.