I am having a problem with a Crystal 8.0 report that is using a left outer join (LOJ) with the Lotus Domino ODBC driver. The LOJ is occurring in a subreport if that is of any importance.
After reading articles in the Crystal Knowledgebase, I have added an IsNull check for the data field in from the right table that I am using in my selection criteria.
Parameter values from the main report are being used in the select criteria of the subreport and are included in the linkage to the subreport from the main report.
The left table in the LOJ is an Object Reference table and the right table in the LOJ is a Resource Tracking table. Both tables are joined on two ID fields (an Object ID and a Response ID), and data selection is partially based upon two input parameters that define a start date and an end date. The format of the input parameters are YYYYMMDD since the Creation Date field in the Resource Tracking table is a text field that defines a sortable date.
The select criteria is coded as follows:
{vwRptCoreObjectReference.rcnObjectId} = {?Pm-?ObjectID} and
(IsNull({vwRptCoreResourceTracking.rcnCreateDate}) OR
({vwRptCoreResourceTracking.rcnCreateDate} >= {?Pm-?StartDate} AND
{vwRptCoreResourceTracking.rcnCreateDate} <= {?Pm-?EndDate})) and
{vwRptCoreObjectReference.rcnResponseId} <> "rcnQuestion Text"
The report works when ANY data from the right table is selected for the requested date range. If a row from the left table contains no rows in the right table for the selected date range, an "empty" row is printed on the report. When NO data from the right table is selected for the defined date range, no "empty" rows for the left table are printed on the report (i.e., the report contains no information other than headings).
In the report definition itself, I've added IsNull checks for all report fields that reference data fields in the right table.
I've tried reordering the components of the select criteria, but nothing seems to help.
Any suggestions?
After reading articles in the Crystal Knowledgebase, I have added an IsNull check for the data field in from the right table that I am using in my selection criteria.
Parameter values from the main report are being used in the select criteria of the subreport and are included in the linkage to the subreport from the main report.
The left table in the LOJ is an Object Reference table and the right table in the LOJ is a Resource Tracking table. Both tables are joined on two ID fields (an Object ID and a Response ID), and data selection is partially based upon two input parameters that define a start date and an end date. The format of the input parameters are YYYYMMDD since the Creation Date field in the Resource Tracking table is a text field that defines a sortable date.
The select criteria is coded as follows:
{vwRptCoreObjectReference.rcnObjectId} = {?Pm-?ObjectID} and
(IsNull({vwRptCoreResourceTracking.rcnCreateDate}) OR
({vwRptCoreResourceTracking.rcnCreateDate} >= {?Pm-?StartDate} AND
{vwRptCoreResourceTracking.rcnCreateDate} <= {?Pm-?EndDate})) and
{vwRptCoreObjectReference.rcnResponseId} <> "rcnQuestion Text"
The report works when ANY data from the right table is selected for the requested date range. If a row from the left table contains no rows in the right table for the selected date range, an "empty" row is printed on the report. When NO data from the right table is selected for the defined date range, no "empty" rows for the left table are printed on the report (i.e., the report contains no information other than headings).
In the report definition itself, I've added IsNull checks for all report fields that reference data fields in the right table.
I've tried reordering the components of the select criteria, but nothing seems to help.
Any suggestions?