CR 8.5 on Oracle 8i via ODBC
I have a situation that's been milling in my head but cannot think of a solution.
1. The Arrestee table lists the individual that's been arrested
Arrest_id = 1, person_id = 123
Arrest_id = 2, person_id = 345
2. The arrest_charges table links the charges to the arrest_id
arrest_id = 1, Charge = A
arrest_id = 1, charge = B
arrest_id = 2, charge = C
arrest_id = 2, charge = D
arrest_id = 2, charge = E
3. There is a charge_code table linked by the charge code
Charge = A, Offense = 001, Description = "Speeding"
Charge = B, Offense = 002, Description = "Aggravated Assault"
Charge = C, Offense = 003, Description = "Forgery"
Charge = D, Offense = 004, Description = "Auto Theft"
Charge = E, Offense = 005, Description = "DUI"
4. The arrest_id links the arrest to the incidents table
arrest_id = 1, Incident = 04C01
arrest_id = 1, Incident = 04C02
arrest_id = 2, incident = 04C05
5. The offense table is link to the Incident table by the incident
Incident = 04C01, Offense = 001 (Charge A)
Incident = 04C02, Offense = 002 (Charge B)
Incident = 04C02, Offense = 003 (Charge C)
Incident = 04C05, Offense = 003 (Charge C)
Incident = 04C05, Offense = 004 (Charge D)
Report
Main Report
The main report uses Arrestee, Arrest_charges, and Charge_codes tables
GH1 - is grouped by Arrest_ID
Detail - Arrest Charges are listed here
GF1 - Incident Subreport inserted here linked by Arrest_ID. This subreport uses the Incident and Offense tables
This is where I need some assistance
I have to match up the Incident offenses to the arrest charges
For Arrest_ID = 1, I need to show that Offense 003 on incident 04C02 does not have a matching arrest charge.
Likewise, for Arrest_D = 2, I need to show that Arrest charge E (offense 005) is not listed on any incident
I know I will have to create a shared variable of the matching offense from the Charge_Code table for each arrest charge in the main report, probably in GF1A and move the subreport to GF1B
Evaluate the shared variable in the Subreport in GF1B. How do I check off each matching offense? Also need to create another shared variable of all offenses that do not match the arrest charges
Finally, retrieve the both shared variables in GF1C and print the proper message.
I think I have provided all of the necessary detail.
Your assistance will be greatly appreciated.
I have a situation that's been milling in my head but cannot think of a solution.
1. The Arrestee table lists the individual that's been arrested
Arrest_id = 1, person_id = 123
Arrest_id = 2, person_id = 345
2. The arrest_charges table links the charges to the arrest_id
arrest_id = 1, Charge = A
arrest_id = 1, charge = B
arrest_id = 2, charge = C
arrest_id = 2, charge = D
arrest_id = 2, charge = E
3. There is a charge_code table linked by the charge code
Charge = A, Offense = 001, Description = "Speeding"
Charge = B, Offense = 002, Description = "Aggravated Assault"
Charge = C, Offense = 003, Description = "Forgery"
Charge = D, Offense = 004, Description = "Auto Theft"
Charge = E, Offense = 005, Description = "DUI"
4. The arrest_id links the arrest to the incidents table
arrest_id = 1, Incident = 04C01
arrest_id = 1, Incident = 04C02
arrest_id = 2, incident = 04C05
5. The offense table is link to the Incident table by the incident
Incident = 04C01, Offense = 001 (Charge A)
Incident = 04C02, Offense = 002 (Charge B)
Incident = 04C02, Offense = 003 (Charge C)
Incident = 04C05, Offense = 003 (Charge C)
Incident = 04C05, Offense = 004 (Charge D)
Report
Main Report
The main report uses Arrestee, Arrest_charges, and Charge_codes tables
GH1 - is grouped by Arrest_ID
Detail - Arrest Charges are listed here
GF1 - Incident Subreport inserted here linked by Arrest_ID. This subreport uses the Incident and Offense tables
This is where I need some assistance
I have to match up the Incident offenses to the arrest charges
For Arrest_ID = 1, I need to show that Offense 003 on incident 04C02 does not have a matching arrest charge.
Likewise, for Arrest_D = 2, I need to show that Arrest charge E (offense 005) is not listed on any incident
I know I will have to create a shared variable of the matching offense from the Charge_Code table for each arrest charge in the main report, probably in GF1A and move the subreport to GF1B
Evaluate the shared variable in the Subreport in GF1B. How do I check off each matching offense? Also need to create another shared variable of all offenses that do not match the arrest charges
Finally, retrieve the both shared variables in GF1C and print the proper message.
I think I have provided all of the necessary detail.
Your assistance will be greatly appreciated.