CODEWITHSUNDEEP
crystal-reportscrystal-reports-2008
user2169575
user2169575

Reputation: 3

Formula to show multiple values

Trying to find a formula that would allow me to show certain values.

Example:

I want to see Aspirin and Warfarin and (one or more of the next 3 values) Clopidogrel or Prasugrel or Ticagrelor.

Patient1 takes:

Aspirin Warfarin Clopidgrel

Patient 2 takes: Aspirin Warfarin

Right now I am seeing both patient 1 and patient 2 and I only want to see patients like patient 1 that shows the 3 different medications.

Please help and thank you in advance. I am using crystal reports 2008.

Upvotes: 0

Views: 1598

Answers (1)

user359040
user359040

Reputation:

I suggest adding something like the following lines to the SQL select clause:

count(distinct case when medication in ('Aspirin', 'Warfarin') 
                    then medication end) 
     over (partition by patient) as mandatory_meds,
count(distinct case when medication in ('Clopidogrel', 'Prasugrel', 'Ticagrelor')
                    then medication end) 
     over (partition by patient) optional_meds,

- then adding the following conditions to the SQL where clause:

and mandatory_meds = 2 and optional_meds >= 1


Alternatively, you could achieve a similar result in Crystal by:

  • group the report by patient
  • create a crystal formula called mandatory_meds, with a formula like:
    if {myTable.medication} = "Aspirin" or {myTable.medication} = "Warfarin"
    then {myTable.medication}
  • create a crystal formula called optional_meds, with a formula like:
    if {myTable.medication} = "Clopidogrel" or {myTable.medication} = "Prasugrel"
    or {myTable.medication} = "Ticagrelor" then {myTable.medication}
  • add a condition to the Group selection formula like:
    DistinctCount({@mandatory_meds})=2 and DistinctCount({@optional_meds})>=1

Upvotes: 1

Related Questions