Built-in CRM reports like the Donor Detail and LYBUNT/SYBUNT reports are great tools that contain predefined settings and data points for your constituent and donation reporting. However, sometimes you'd like to get more information than what is available in the report or further filter on information that's not currently available. You can leverage the Advanced Query Wizard to further enhance these report capabilities.
In the Donor Detail report, there is a setting to Save Results for Advanced Queries. If this box is checked and the Donor Detail report is run, the results are stored in a temporary table in the database. This makes those data fields available to a constituent-based Advanced Query.
For an overview of the Advanced Query Wizard and its controls, see Advanced Query Wizard Overview.
To create a query from the last-run Donor Detail report results...
- Run a Donor Detail report and make sure Save Results for Advanced Queries is checked.
- From the main menu, choose Reports > Queries > Advanced Query Wizard. The Advanced Query Wizard launches.
- Click the Create New Query button at the top of the Query Wizard window. This opens a new browser tab for your new query.
- From the Query Type frame to the left of the window, choose Constituent.
- Select the appropriate Purpose of this Query, depending on what you want to do.
- Click Next.
- Among other output fields you may include in your query, those in the Last Donor Detail Report table are specifically from that table of data from the last time the Donor Detail Report was run. Those can be used in your output fields or filters.
NOTE: Be careful using the Criteria field in your output fields, as it contains all the filter settings from the last Donor Detail Report. If you include it in your output fields, the data returned is a very long string:
{end_date=2017-06-30, save_results_for_aq=true, report_mode=0, is_appeals_exclude=false, include_co_donor=true, force_spouses=true, total_received_max=null, is_funds_exclude=false, sort_direction=sort_ascending, batches=null, exclude_gik=false, consolidate_spouses=true, campaigns=null, is_groups_exclude=false, batch_templates=null, ddrExcludeDeceased=false, total_combined_max=null, funds=null, include_soft_credit=true, start_date=2017-06-01, total_combined_min=null, total_received_min=null, include_recurring_payments=true, include_donations=true, groups=null, total_pledged_min=null, include_match_payments=true, appeals=null, is_batch_templates_exclude=false, ddrExcludeDoNotContact=false, sort_type=sort_on_name, include_pledges=false, include_pledge_payments=true, is_campaigns_exclude=false, total_pledged_max=null, is_batches_exclude=false} - Filter on Last Donor Detail Report User ID is not blank. This will restrict the query to just those records in the last Donor Detail Report that was run.
- Add whatever sort fields you may need for your query.
- Click Next to get to Step 3 of the Query Wizard and see your results.