- 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,
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.
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).
- 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
- 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)