ADR_Report_requirements_Elapsed_X_enhancement_2_8_13.docx

Federal Election Commission

Alternate Dispute Resolution (ADR) ADR Crystal Reports Enhancement Requirements Jennifer Boyt Revision Date: 2/11/2013 Version 1.0 Table of Contents

Contents

List of Tables (ADR) Crystal Report Enhancement Requirements

BACKGROUND

ADR requires Business Objects IX (Crystal) Reports, which access data from Law Manager Pro production database (fecas028\lmpro).

The ADR Elapsed Time Report needs to be updated due to the changing business processes that have occurred in ADR.

Report - Modify Existing ADR Time Elapsed Report Audience:

Commissioners, Staff Director, ADR Exists in:

Current report file is run from Law Manager Scope:

The ADR Time Elapsed Report currently searches on case closing events during a specific timeframe. The first page lists the date parameters input into LMPro, and the Average, Median, Maximum, Minimum date calculations from open to close sectioned by various disposition event calendar entries. It also notes the total number of each type of those dispositions and the total days open.

The main report separates cases by disposition type, then notes ADR Case #, Source Case #, Case Received Date (A), Date ADR Received (B), Memorandum on Final Vote (C), Case Closed Date (D), Difference (In Days) (D-B), Difference (In Days) (C-B), Difference (In Days) (D-C), and Difference (In Days) (D-A).

Details:

Current report file is run from data in Law Manager, processed by SQL Stored Procedures in the LMPro database, and run through a Business Objects XI Crystal Run-Time using Business Object XI Crystal application to create the report format. Two SQL Stored Procedures are utilized, one for the first page and one for the subsequent pages.

User Prompt Parameters:

NEW Parameter: Staff Dropdown or entry selection to select: “ALL” for all ADR Staff or individual ADR staff members by name (this will search for ALL cases in following universe, or just those cases in the universe in which a specific staff person is a player in the Players Tab)

Relationship:

Staff = 100206 NAME would be lookup, currently only Krista Roche or Lynn Fraser

CALENDAR YEAR REPORT:

From date default = current date;

Else from date = Date input by user

End date default = sys date Else End date = Date input by user

ELECTION CYCLE YEAR:

From date default: 2004;

Else from date = Date input by user

End date default: 2004;

Else end date = Date input by user

FISCAL YEAR:

From date default: 1990;

Else from date = Date input by user

End date default = 2000 Else End date = Date input by user

Report time frame chosen by user User selects by drop down list, with choices including:

Calendar Year, Fiscal Year or Election Cycle Default = Fiscal Year For each of 3 calendar types, 1 view

Search Logic (Program Parameters) Major:

Select Calendar.primary_relation_kind = 36 and Calendar.primary_relation_key = M

Create temporary table #ET (holding results of search, including case_id))

Format:

· Landscape on 8.5” x 11” paper

· Solid black line under the column headers

· Dates formatted in mm/dd/yyyy

· Duration calculations in whole numbers with decimals for page 1 (i.e., Average, Median, etc.)

· Duration calculations in whole numbers with no decimals for page 2 forward (i.e., Difference in Days D-C)

Requirements for Presentation and Final Report Content:

· Main Header Line 1 (Centered): “ADR Elapsed Time Report For Closed Cases (by XXX Year)” XXX = Calendar Year, Fiscal Year or Election Cycle

· Main Header Line 2 (Centered): “(in Days)”

· Main Header Line 3 (Centered): “From:[date parameter] To: [date parameter]”

· New: Main Header Line 4 (Centered): input parameters for Staff Pick

Current Search criteria: This will remain as it currently is (but limited by New Player Parameter)

111813 Settlement Agreement-Commission Action-Approve 111814 Settlement Agreement-Commission Action-Failed to Approve 111840 Memorandum to Dismiss-Commission Action-Failed to Approve 111831 Mediation Settlement Agreement-Commission Action-Failed to Approve 112258, --Memorandum to Assign Case-Commission Action-Failed to Approve Returned to Referring Office 111778, --Memorandum to Assign Case- Commission Action- Failed to Approve

111791, --Respondent's Response - ADR Rejected- Returned to Referring Office

NEW NUMBER TO BE DEVELOPED for “ADR REJECTED-TRANSFERRED TO OGC”

SETTLE_EVENT = 111839

(Memorandum to Dismiss-Commission Action-Approved) for which 111814 (Mediation Settlement Agreement-Commission Action-Failed to Approve) doesn't exist.

111837,-- Memorandum to Dismiss- Forwarded to Commission 111811 -- Negotiated Settlement Agreement-Forwarded to Commission

Report/Search criteria: This will remain as it currently is (but limited by New Player Parameter)

111813 Settlement Agreement-Commission Action-Approve 111814 Settlement Agreement-Commission Action-Failed to Approve 111840 Memorandum to Dismiss-Commission Action-Failed to Approve 111831 Mediation Settlement Agreement-Commission Action-Failed to Approve 112258, --Memorandum to Assign Case-Commission Action-Failed to Approve Returned to Referring Office 111778, --Memorandum to Assign Case- Commission Action- Failed to Approve

111791, --Respondent's Response - ADR Rejected- Returned to Referring Office

112628 : NEW NUMBER DEVELOPED for “ADR REJECTED-TRANSFERRED TO OGC”

Primary_relation_key to cases.object_key

Where settle_event = 111839 Memorandum to Dismiss-Commission Action-Approved, exclude case if there does NOT exist 111814 Settlement Agreement-Commission Action-Failed to Approve

111761, -- Received from RAD 111762, -- Received from Audit Division 111763, -- Received from Commission 111764, -- Received from Other 111760 -- Received from OGC

DELETE THIS COLUMN FROM REPORT

7 * Previously (C) column

111837,-- Memorandum to Dismiss- Forwarded to Commission 111811 -- Negotiated Settlement Agreement-Forwarded to Commission

8* Previously (D) column

111765 Memorandum to Assign Case-Forwarded to Commission

Calculation

Calculation

Page 1 Grouping criteria: This will remain as it currently is (but limited by New Player Parameter)

111830 Mediation Settlement Agreement-Commission Action-Approved 111813 Settlement Agreement-Commission Action-Approve 111814 Settlement Agreement-Commission Action-Failed to Approve 111831 Mediation Settlement Agreement-Commission Action-Failed to Approve

112258, --Memorandum to Assign Case-Commission Action-Failed to Approve Returned to Referring Office 111778, --Memorandum to Assign Case- Commission Action- Failed to Approve 111791, --Respondent's Response - ADR Rejected- Returned to Referring Office

112628 : NEW NUMBER DEVELOPED for “ADR REJECTED-TRANSFERRED TO OGC”

New Page 1 Summary Calculation Requirements: Add a Total row, to show average for total dispositions, median for total dispositions, maximum for total dispositions, minimum for total dispositions, Total Resolutions for all dispositions, Total of ALL cases for All dispositions, and Total Days for ALL Dispositions

Transferred categories, to come up with a Real average for all Disposition types; averaging min value for mm_assign_date and max value for settle_date

Transferred categories, to come up with a Real Median for all Disposition types; median min value for mm_assign_date and max value for settle_date

Transferred categories, to come up with a Maximum for all Disposition types; median min value for mm_assign_date and max value for settle_date

Transferred categories, to come up with a Minimum for all Disposition types; median min value for mm_assign_date and max value for settle_date

New Page 1 Table with Calculation Requirements: Add a Table that looks like this:

Calculate using D-A calculation from Page 2 of report, determine how many were completed in MORE than 155 days

Calculate using above Column 7 Total # of Cases; find percentage of that number using <155 Total Cases

Calculate using D-A calculation from Page 2 of report, determine how many were completed in LESS than 155 days

Calculate using above Column 7 Total # of Cases; find percentage of that number using >155 Total Cases

New Page 1 Table with Calculation Requirements: Add a Table that looks like this:

Calculate using B-A calculation from Page 2 of report, determine how many were circulated in MORE than 140 days

Calculate using above Column 7 Total # of Cases; find percentage of that number using <140 Total Cases

Calculate using B-A calculation from Page 2 of report, determine how many were circulated in LESS than 140 days

Calculate using above Column 7 Total # of Cases; find percentage of that number using >140 Total Cases

ADR Outstanding Balance Report

1. Audience:

Commissioners, Staff Director, ADR Exists in:

Current report file is run from Law Manager.

Purpose Displays the players involved in an ADR settlement agreement along with the civil penalty assessed, the civil penalty paid, the date received, the balance due. It also displays any non-monetary terms, their date due and date completed.

Details:

For purposes of this report, possibly re-utilizing and modifying the ReportADRCivilPenalty Stored Procedure existing in LMPro db, the report should run thru InfoView, as the parameters are very different from the existing ADR Civil Penalty Report.

User Prompt Parameters:

NEW Parameter: Staff Dropdown or entry selection to select: “ALL” for all ADR Staff or individual ADR staff members by name (this will search for ALL cases in following universe, or just those cases in the universe in which a specific staff person is a player in the Players Tab)

Relationship:

Staff = 100206 NAME would be lookup, currently only Krista Roche or Lynn Fraser

Existing Parameters: For this version of the report, the date should default to the current date, as it is looking at current data re: is there a balance owed on a closed case.

Date:

From date default = current date;

Search Logic (Program Parameters) Major:

Select Calendar.primary_relation_kind = 36 and Calendar.primary_relation_key = M.object_key and Payment.primary_relation_kind = 412 and Settlement.primary_relation_kind = 36

Create temporary table #BO (holding results of search, including case_id))

Note Payment is a VIEW not a TABLE.

· Solid black line under the column headers

· Dates formatted in mm/dd/yyyy

· Duration calculations in whole numbers with decimals for page 1 (i.e., Average, Median, etc.)

· Duration calculations in whole numbers with no decimals for page 2 forward (i.e., Difference in Days D-C)

Requirements for Presentation and Final Report Content:

· Main Header Line 1 (Centered): “ADR Settlement Report By XXX” XXX = Month, Fiscal Year or Election Cycle

· Main Header Line 2 (Centered): “(For Closed Cases)”

· New: Main Header Line 3 (Centered): “(Outstanding Penalty Report)”

· NEW: Main Header Line 4 (Centered): “For Payments Outstanding On Date: [date parameter]”

· New: Main Header Line 4 (Centered): input parameters for Staff Pick

Current Search criteria: Limited by New Player Parameter and new universe.

The universe is selected based on the date parameters including the Payment.date_received and Payment.primary_relation_kind = 412

Use the ADR Civil Penalty Report for the bones of the SQL Stored Procedure (ReportADRCivilPenalty), just add this criteria for the staff, change the criteria for the date, and add Balance Owed criteria.

Balance Owed criteria: where Payment.balance <> 0

ADR Date Payment Received Report

1. Audience:

Commissioners, Staff Director, ADR Exists in:

Current report file is run from Law Manager Purpose Displays the players involved in an ADR settlement agreement along with the civil penalty assessed, the civil penalty paid, the date received, the balance due. It also displays any non-monetary terms, their date due and date completed. Note that the “Date Received” column represents when the Civil Penalty was paid. The “Date Received” is derived from the Payment History Tab in the ADR Settlement Notebook. This report is looking for what payments were received during a distinct time frame.

Details:

For purposes of this report, possibly re-utilizing and modifying the ReportADRCivilPenalty Stored Procedure existing in LMPro db, the report should run thru InfoView, as the parameters are very different from the existing ADR Civil Penalty Report.

User Prompt Parameters:

NEW Parameter: Staff Dropdown or entry selection to select: “ALL” for all ADR Staff or individual ADR staff members by name (this will search for ALL cases in following universe, or just those cases in the universe in which a specific staff person is a player in the Players Tab)

Relationship:

Staff = 100206 NAME would be lookup, currently only Krista Roche or Lynn Fraser

Existing Parameters:

CALENDAR YEAR REPORT:

From date default = current date;

Else from date = Date input by user

End date default = sys date Else End date = Date input by user

ELECTION CYCLE YEAR:

From date default: 2012;

Else from date = Date input by user

End date default: 2012;

Else end date = Date input by user

FISCAL YEAR:

From date default: 2012;

Else from date = Date input by user

End date default = 2012 Else End date = Date input by user

Report time frame chosen by user User selects by drop down list, with choices including:

Calendar Year, Fiscal Year or Election Cycle Default = Fiscal Year For each of 3 calendar types, 1 view

Search Logic (Program Parameters) Major:

Select Calendar.primary_relation_kind = 36 and Calendar.primary_relation_key = M.object_key and Payment.primary_relation_kind = 412 and Settlement.primary_relation_kind = 36

Select results where Payment.date_received <= start date and >= end date Note Payment is a VIEW not a TABLE.

Create temporary table #CP (holding results of search, including case_id))

· Solid black line under the column headers

· Dates formatted in mm/dd/yyyy

· Duration calculations in whole numbers with decimals for page 1 (i.e., Average, Median, etc.)

· Duration calculations in whole numbers with no decimals for page 2 forward (i.e., Difference in Days D-C)

Requirements for Presentation and Final Report Content:

· Main Header Line 1 (Centered): “ADR Settlement Report By XXX” XXX = Month, Fiscal Year or Election Cycle

· Main Header Line 2 (Centered): “(For Closed Cases)”

· New: Main Header Line 3 (Centered): “(Payment Received Date)”

· NEW: Main Header Line 4 (Centered): “For Payments Received From:[date parameter] To: [date parameter]”

· New: Main Header Line 4 (Centered): input parameters for Staff Pick

Current Search criteria: Limited by New Player Parameter and new universe.

The universe is selected based on the date parameters including the Payment.date_received and Payment.primary_relation_kind = 412

Note Payment is a VIEW not a TABLE.

Use the ADR Civil Penalty Report for the bones of the SQL Stored Procedure, ReportADRCivilPenalty), just add this criteria for the staff, change the criteria for the date, and add criteria for limiting the result universe on date received

1. ADR Civil Penalty Report (ADR Settlement Report) now to be called Date Penalty Assessed Report

1. Audience:

Commissioners, Staff Director, ADR Exists in:

Current report file is run from Law Manager Purpose Displays the players involved in an ADR settlement agreement along with the civil penalty assessed, the civil penalty paid, the date received, the balance due. It also displays any non-monetary terms, their date due and date completed. Note that the “Date Received” column represents when the Civil Penalty was paid. The “Date Received” is derived from the Payment History Tab in the ADR Settlement Notebook.

Details:

Current report file is run from data in Law Manager, processed by SQL Stored Procedures in the LMPro database, and run through a Business Objects XI Crystal Run-Time using Business Object XI Crystal application to create the report format.

User Prompt Parameters:

NEW Parameter: Staff Dropdown or entry selection to select: “ALL” for all ADR Staff or individual ADR staff members by name (this will search for ALL cases in following universe, or just those cases in the universe in which a specific staff person is a player in the Players Tab)

Relationship:

Staff = 100206 NAME would be lookup, currently only Krista Roche or Lynn Fraser

Existing Parameters:

CALENDAR YEAR REPORT:

From date default = current date;

Else from date = Date input by user

End date default = sys date Else End date = Date input by user

ELECTION CYCLE YEAR:

From date default: 2004;

Else from date = Date input by user

End date default: 2004;

Else end date = Date input by user

FISCAL YEAR:

From date default: 1990;

Else from date = Date input by user

End date default = 2000 Else End date = Date input by user

Report time frame chosen by user User selects by drop down list, with choices including:

Calendar Year, Fiscal Year or Election Cycle Default = Fiscal Year For each of 3 calendar types, 1 view

Search Logic (Program Parameters) Major:

Select Calendar.primary_relation_kind = 36 and Calendar.primary_relation_key = M.object_key and Payment.primary_relation_kind = 412 and Settlement.primary_relation_kind = 36

Create temporary table #CP (holding results of search, including case_id))

· Solid black line under the column headers

· Dates formatted in mm/dd/yyyy

· Duration calculations in whole numbers with decimals for page 1 (i.e., Average, Median, etc.)

· Duration calculations in whole numbers with no decimals for page 2 forward (i.e., Difference in Days D-C)

Requirements for Presentation and Final Report Content:

· Main Header Line 1 (Centered): “ADR Settlement Report By XXX” XXX = Month, Fiscal Year or Election Cycle

· Main Header Line 2 (Centered): “(For Closed Cases)”

· New: Main Header Line 3 (Centered): “(Penalty Assessment Date)”

· Main Header Line 4 (Centered): “For Cases Opened From:[date parameter] To: [date parameter]”

· New: Main Header Line 4 (Centered): input parameters for Staff Pick

Current Search criteria: These will remain as it currently is (but limited by New Player Parameter) The universe is selected based on the following Disposition events:

111813 Settlement Agreement - Commission Action - Approved 111830 Mediation Settlement Agreement - Commission Action - Approved 111839 Memorandum to Dismiss - CommissionAction - Approved

image1.jpeg