Question Re: Multi-table Formula in Technology Q&A

Multi-table Formula

former_member1343181 — Wed, 02 Aug 2023 14:26:51 GMT

Good Morning,

This report uses five tables. One table is the Customer table and the others are transactions for each department. The tables are linked on the Customer ID field.

There is also a Parameter that allows the user to select a Department. The departments are Sales, Service, Parts, or Rentals.

I then created a straight forward formula that selects the Sub-total amount from the appropriate table based on the value of the parameter.

...and a record selection formula, also based on the value of the parameter, which excludes records from the other tables by adding 10,000 days to the current date and specifying that only invoice dates greater than that should be included (which of course there are none).

The problem is that the record selection formula produces erroneous results, including values from other departments as if the IF statement did not exist. For example; if I remove the other conditions from the record selection formula, the results appear to be accurate.

All of the grouping and group sorting for this report seem fine. Can someone please tell me why my record selection formula is failing?

Thanks - Tom

Re: Multi-table Formula

DellSC — Wed, 02 Aug 2023 16:05:07 GMT

You should avoid using If statements in the Select Expert - most of the time they can't be pushed to the database for processing so Crystal will pull all of the data into memory and filter it there, causing significant slowness in the report. Also, you're not linking together your filter criteria with and/or, which is probably what's causing your issues.

Try something like this:

(
  (
    {?Department} = 'Sales' and  
	Date({EDI_RPT_VHMLS.DateInvoice}) >= {?Start Date} and
	Date({EDI_RPT_VHMLS.DateInvoice}) <= {?End Date}
  } OR
  Date(EDI_RPT_VHMLS.DateInvoice})) > CurrentDate + 10000
)
AND
(
   (
      {?Department} = 'Service' and
	  Date({SVSLS.DateInvoice}) >= {?Start Date} and
	  Date({SVSLS.DateInvoice}) <= {?End Date}
   ) OR
   Date({SVSLS.DateInvoice}) >= CurrentDate + 10000
)
AND
(
   (
      {?Department} = 'Parts' and
	  Date({PTSLS.DateInvoice}) >= {?Start Date} and
	  Date({PTSLS.DateInvoice}) <= {?End Date}
   ) OR
   Date({PTSLS.DateInvoice}) >= CurrentDate + 10000
)
AND
(
   (
      {?Department} = 'Rentals' and
	  Date({LRBIL.DateInvoice}) >= {?Start Date} and
	  Date({LRBIL.DateInvoice}) <= {?End Date}
   ) OR
   Date({LRBIL.DateInvoice}) >= CurrentDate + 10000
)

-Dell