Query Library for the Data Export/Reporting Tool (DERT)

The Data Export/Reporting Tool (DERT) allows you to build and export a query by choosing the fields you'd like to see, the criteria for what data to find, and the sort order for the data. Click here to learn how to use the Data Export/Reporting Tool.

This page contains a number of sample queries that you can download and then import into your Atlas database. Once you import a query from the library, you have the option to make changes/additions as well as save the query for one or more users.

Follow these steps to download and import these sample Queries:

To download a query, right-click on the bold query link and choose "Save Link As" (or "Save target as" in Internet Explorer), then name the query and choose a location on your computer to save it, such as your desktop.
To import the query into your DERT, go to Reports > Data Export/Reporting Tool in Atlas (or click on the Saved Queries Quick Actions menu) and click Import a Query. Select the file you saved in step 1 and the query will be loaded into the Data Export/Reporting Tool. You can then edit and save the query. once Saved, the query will be available from your Saved Queries Home Screen. Click here to learn more about Importing a Query.

Basic Profile Information Queries

All Members and Main Contacts: A list of all profiles with the Member checkbox checked, with their main contact name and some contact information.
All Members and Main Contacts: Detailed Export: A list of all profiles with the Member checkbox checked, with 20+ columns of contact and profile information.
Members by Profile Status: A list of members, sorted by Profile Status with level and dues info
Members with Membership Level Filter: A list of members with basic contact info, with a filter to limit to only members in a certain Membership Level.
Members by Zip Code: A list of members with all address and contact info, with a filter to limit to only members in certain Zip Codes.
Members with Affiliation Code Column: A list of all members with their level and Main Contact, with a column for an Affiliation Code of any type. This query requires you to enter an Affiliation Code Type ID in the filter. You can find Affiliation Code Type ID in Admin > Manage Codes > Profile Codes > Affiliations, in the grid. The query will display each member's Affiliation Code value for the Affiliation Type that you enter as the filter.
Members with Custom Field Column: A list of all members, with a column for the value in one Custom Field. This query requires you to enter a Custom Field ID, which you can see on the Custom Info tab of any profile. It will display each member's value for the Custom Field that you enter as the filter.

Billings

Dues Billings by Month: All active billing records for Dues revenue items, with an Anchor Month filter (set to January by default). The month can be edited on the Filters section in the DE/RT.
Non-Dues Billings: All active billing records that use any revenue item NOT flagged as a Dues Type, with profile status and membership status and level.

Contacts

Contacts by Type: All Contact records with subject, date, type and names, within a date range for a specific Contact Type that you enter as the filter.
Contacts by Contactee: All Contact records with subject, date, type and names, within a date range for a specific Contactee that you enter as a filter.
Contacts for Date Range: All Contact records for any profile, with Contact details and profile status/level/user, within a date range that you enter as a filter.
All Contacts for Profile: All Contact records from any date, including detailed description of contact, for a single profile that you enter as a filter.

Committees

Committee Mailing List: A list of all active Committee members for a specific Committee that you enter as a filter, with profile address and contact information
Committee Roster with Organization Info: All active Committee members for a specific Committee that you enter as a filter, with membership status, join date, etc. for the Committee member's organization
Former Committee Members: All inactive Committee members for a specific Committee that you enter as a filter, with basic contact information and dates they joined and left the Committee

Sales

Prospects by User: All profiles flagged as a Prospect with an Assigned To user entered by you as a filter. You can enter one or more users.
Open Sales Opportunities: All Sales Opportunities that are not "Closed" or "Lost" for any user, with amount and sorted by estimated close date
Sales Opportunities by User: All Sales Opportunities of any status for a specific user entered by you as a filter

Events

All Attendees for Event: all Attendees for each event item with basic contact information, for a single event that you enter as a filter (with Event ID)
Event Attendees by Event Item: all Attendees for one or more Event Items within an event, which you enter as a filter with both Event ID and Event Item names
Non-Members Signing up for Events: Creates a list of Event Signups who are not members. Uses the Event Start Date to determine how far back to look. Note: Dropped members will appear on this list, since they are no longer flagged as a Member.
Sign Ups not linked to a Profile: Creates a list of event Sign Ups (Registrations) that have not been connected to a Profile. Uses Event Date as the filter.

Invoices

All Open Invoices: All invoice line items from any date (past or future), with an amount due greater than $0.00.
Open Invoices by Date Due: All invoice line items with an amount due greater than $0.00, with a Date Due older than a date that you enter as a filter.
Dues Invoices by Date: All invoice line items with a Revenue Item marked as a "Dues" type, paid and unpaid, with an Apply Date in a date range you enter as a filter.
Invoices by Revenue Item: All invoice line items, paid and unpaid, for one or more specific Revenue Items that you select as a filter, with an Apply Date that you enter as a filter.

Payments

Payments by Date: All payments within a date range that you enter as a filter, with any payment adjustments and amount still due.
Payments by Revenue Item: All payments within a date range that you enter as a filter, that are applied to line items with specific Revenue Items that you select as a filter.
Credit Card Payments: All payments within a date range that you enter, that used a Payment Type flagged as "Credit Card Type" and processed in Atlas.

Listings

Active/Web Listings by Category: Displays a list of categories in the database and a count of listings that are flagged as Web Listing and Active Listing.
Members by Listing Record: Displays a list of members and all the Listing Categories assigned to their profile. Each Listing Record the member has will show as a new row for that Profile.

--------------------------------------------------------------------------------------------------------

This feature is included or available for purchase with the following Atlas packages.

Comments