Step #1: Create a selection to pull all patrons we want to see:
- Create your selection using the steps here.
- DO NOT add Results fields to display. These fields will be defined in our Export definition.
- Once created, from Set save options tab Name your query, and mark to Create a selection before Saving.
Step #2: Create your smart field for average spent on a membership and membership add-on:
- Navigate to Administration > Smart Field
- Click Add and select Constituent Revenue Application Amounts source view
- From General tab, Name your Smart Field
- From Parameters tab, change Value to return to: Average application amount
- Underneath Revenue types, select Membership
- Within Transaction type/Application, Underneath ORDER mark Membership and Membership Add On. Underneath PAYMENT select Membership and Membership Add On.
- Time frame: If you want to only focus on specific time range, define it here, then Save.
- From the Smart Field screen, click drop down next to your smart field and select Process smart field.
Step #3: Create your Export definition:
- Navigate to Administration > Export definitions
- Click Add and select record type of Memberships
- In the right column, select Memberships. In the left column, select Membership Transaction. From the middle, bring Action into the right column.
- In the export criteria screen, select Number to export (up to how many transactions you want to see per patron, for example 5). Next to Order by, change to Date added. Underneath Filter, mark Seleced membership transaction. In the left column drag Transaction date into Include records where. Set equal to your date range.
- Click OK
- From the middle column, bring Transaction date into the right column.
- In the left column, expand Membership transaction and select Revenue Application. From the middle column bring Amount into the right column.
- In the right column, select Memberships at the top. In the left column, expand Members, then expand Constituent. From the middle column, drag Name into Selected fields.
- In the Export criteria screen, decide if you want to see ALL Members, or just the Primary member.
- To see all members on the membership, set your Number to export to as many per membership ID you want to see, then click OK.
- To see only primary member on the membership, set your Number to export to 1. Underneath Filter mark Selected members. From the left column drag Primary Member into Include records where. Set Equal to Yes, for example:
- In the left column, underneath Constituents select Address (Primary). From the middle column, bring Address line 1, Address line 2, City, State and Zip into the right column.
- In the right column, select Name.
- In the left column, with Constituent still expanded, expand Smart Fields and select your Smart Field created in Step #2. From the middle column, bring Value into the right column. Below is the final export definition:
- Click Column Order tab to arrange where you want each column to appear in your final export
- Click Set save options tab to Name your export definition and mark to "Allow definition to be used by other areas of the application," then Save and close.
Step #4: Create your Export
- Navigate to Administration > Export
- Click Add
- Select Export type: Export definition
- Next to Selection, use the selection you created in Step #1
- Next to Export definition, use the Export definition created in Step #3
- Click drop down next to Export and select Start export
- Once complete, click Download output for excel file