Redo Reports for BIRT 2.2

General Notes
--------------------

• Please redo the below listed reports with the modifications noted for each. In all cases, take advantage of structured input fields for parameters as much as possible (combo list/select, date select if available, etc.).
• Make sure parameter labels are user friendly text - not variable names, etc.. Show CURRENT value of user entered params in header section of each report.
• I've committed updates to svn for several of these files. Use corrected query and datasource info from my latest svn commits as a starting point.
• Set your datasource to use following standards (and set these values in all reports): DBName = civicrm, DBUser = civicrm, DBPassword = civicrm

Reports to Redo
----------------------
1. ContactReport - start w/ my revised 3.2 compatible version (in svn) which fixes the query and provides combobox parameters for Individual and Group.

2. ContributionByYear - Change title to "Contact Contribution Totals By Date Range"

• Modify parameters to take 2 parameters:

• From Date
• To Date

• Display current param values in the header below report title
• Modify query to sum the contributions for each contact across the date range and sort by contribution total DESC.
• Modify output columns:

• Contributor Name
• # of Contributions
• Avg Contribution
• Total Contributed

SELECT contact.display_name, SUM(contrib.total_amount) AS contrib_total, COUNT(contrib.id) as contrib_count, AVG(contrib.total_amount) AS contrib_avg
FROM civicrm_contribution contrib,
civicrm_contact contact
WHERE contrib.contact_id = contact.id AND
contrib.is_test = 0 AND
contrib.receive_date >= ? AND
contrib.receive_date < = ?
GROUP BY contact.id
ORDER BY contrib_total DESC

NOTE: We'll need to figure out a way to pass the 2nd param to the query with 23:59 timestamp appended.

3. ContributionTotalsByHousehold
If we can figure out the query to do a similar report to #2 above - except that we aggregate and group contributions on Household contacts (i.e. list of Households where Individuals who are household members OR household heads have made contributions + the total amount, count and avg of contribs associated w/ each household).

4. statusBasedParticipants

• Modify parameters - add Event Title select (so you select Event and Participant Status from drop-down lists)
• Query sort is by sort_name ASC
• Display both selected Event Title and Status params in header of report

5. contributionGraphReport

• Move this into Contribute directory
• Change report title: "Contributions by Type"
• Change grouping in main report to group by contrib type instead of contact type. Add sub-footers w/ totals for each type if you can figure it out. (This change makes sense because the report detail will then map to the chart).
• Move the chart above the detailed report in the layout (since it is a summary of the data).
• Add From and To Date params as in other 2 contribution reports.

6. (Membership reports TBD)