Report with multiple optional paramters retrieving more data than expected in certain scenarios

[elsenorjose]

Hello all,

I am trying to modify a report that has multiple optional parameters (CR 2008) to make it work so that the users can filter the report by any of the parameters but I don't think I've written the record selection formula correctly. Basically, given the parameters below, a user should be able to enter a date range (that is mandatory) then enter a value for OR Service Name or leave it blank for ALL and be able to enter a name for any or all of the surgeon parameters to look for a specific surgeon or leave blank for ALL. A sample case would be where you don't know what role Dr. Smith played so you want to search for her in any of the surgeon parameters so you enter her name in all of them. The result should be every surgical case where Dr. Smith was one of the surgeons along with the associated OR Service Name(s). Another scenario would be to look for Dr. Smith where she was the 2nd Primary surgeon, or 3rd, or 4th, or any combination, e.g.; was she 2nd or 4th, so the user enters her name in one or more of those parameters and leaves all other parameters blank. The result should be only those cases where Dr. Smith played one of those roles along with any other surgeons on those cases and associated OR Service Name(s). The way I've written the select formula works for the first scenario, but in the second scenario, if I enter Dr. Smith's name in the 2nd Primary Surgeon parameter or 2nd and 4th, I retrieve ALL records for all OR Service Names and all Surgeons for the selected date range. How can I rewrite this to accommodate the 2nd scenario?

Thank you.

Code follows:

{OR_LOG.SURGERY_DATE} >= {?Date of Service (Start)} And {OR_LOG.SURGERY_DATE} <= {?Date of Service (End)}
And (Not HasValue({?OR Service Name}) Or {ZC_OR_SERVICE.NAME} = {?OR Service Name})
And 
((Not HasValue({?First Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.1stPrimary} = {?First Primary Surgeon Name})
      Or (Not HasValue({?Second Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.2ndPrimary} = {?Second Primary Surgeon Name})
      Or (Not HasValue({?Third Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.3rdPrimary} = {?Third Primary Surgeon Name})
      Or (Not HasValue({?Fourth Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.4thPrimary} = {?Fourth Primary Surgeon Name})
      Or (Not HasValue({?Fifth Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.5thPrimary} = {?Fifth Primary Surgeon Name})
      Or (Not HasValue({?Sixth Primary Surgeon Name}) Or {V_PRIMARIES_FOR_SCL.6thPrimary} = {?Sixth Primary Surgeon Name})
      Or (Not HasValue({?First Assisting Surgeon Name}) Or {V_ASSISTING_FOR_SCL.1stAssisting} = {?First Assisting Surgeon Name})
      Or (Not HasValue({?Second Assisting Surgeon Name}) Or {V_ASSISTING_FOR_SCL.2ndAssisting} = {?Second Assisting Surgeon Name})
)

 

[Madawc]

Two tips

a) Take a look at Database > Show SQL Query. See what the software has made of your code

b) Break up complex selection commands. Make them boolians, e.g.

{V_PRIMARIES_FOR_SCL.1stPrimary} = {?First Primary Surgeon Name})

Place @TestFirst etc. against unselected data and see if it says True or False.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP