After digging through the various report and widget properties, there appears to be no property that will automatically display the subreport if there are no records.
The way I finally got a subreport to display was to create a query which returns all the records, and also returns “blank” rows for nonexistent records.
In this example, we have three tables: Orgs, People, and Positions. Positions is a table that has OrgID and PeopleID columns, and joins the other two tables. Positions has a column “HasRecord” which is a boolean that indicates that the person has a record. Positions contains not only the related records of interest, but other records as well; HasRecord indicates that this is a record we’re looking for.
This query will get you the list of all the orgs, with additional columns where there is a matching record in Positions where HasRecord is true.
SELECT * FROM ( SELECT OrgID FROM Orgs ) a LEFT JOIN ( SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE ) b ON a.OrgID=b.OrgID
That gets you your result set, and you can then add some columns with a regular join:
SELECT Name, Address, Title FROM ( SELECT * FROM ( SELECT OrgID FROM Orgs ) a LEFT JOIN ( SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE ) b ON a.OrgID=b.OrgID ) c INNER JOIN People p ON c.PeopleID=p.PeopleID
What didn’t work
One commenter suggested using a UNION query to add blank rows to the result. I tried doing this:
SELECT OrgID, Null FROM Orgs UNION SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE
This didn’t work because you’d end up with a blank line before records that exist
Another possibility was to do a LEFT JOIN between Orgs and Positions, and match not only on HasRecord=TRUE but even when it’s NULL.
SELECT * FROM Orgs LEFT JOIN Positions ON Orgs.OrgID=Positions.OrgID WHERE HasRecord=True OR HasRecord IS NULL
That doesn’t work because HasRecord, if it exists, is either True or False. So False is not included. This means if a related record exists in Positions, but is not HasRecord=True, then, an OrgID for that organization won’t show up in the results. If we include “OR HasRecord=False” to the statement, we end up selecting everything, including records we don’t want. It just doesn’t work.