Start your query:
  1. Navigate to Analysis, then click Information Library
  2. Click Add a new Ad-hoc Query
  3. Select the source view of Membership and click OK
User-added image

Add fields to Include records where:
  1. To see membership transactions that occurred within a certain date: In the left column select Membership Transaction. From the middle column drag Transaction date to Include records where.
  2. In the left column select Membership Transaction. From the middle column drag Action into Include records where. Set the action to be One of Join, Renew, Upgrade, Downgrade, Rejoin as you see below. 
    User-added image
  3. To only display the primary member name: In the left column, select Members. From the middle column, drag Primary member to Include records where. Select Equal to Yes as you see below:
    User-added image
  4. (Optional) To view only transactions that were processed in a specific sales method such as Daily Sales or Online Sales: In the left column, expand Membership Transaction, expand Revenue Application, expand Revenue Details, and select Sales Order. From the middle column, drag Sales method type into Include records where. Set Equal to or One of and choose the sales methods you'd like to include. User-added image
  5. (Optional) To exclude fully refunded transactions: In the left column, expand Membership Transaction, expand Revenue Application, Expand Revenue Details, and select Sales Order. From the middle column, drag Refund status text into Include records where. Set Not Equal to Fully Refunded. Make sure to Include Blanks.
    User-added image

Add fields to Results fields to display:
  1. To display the Membership ID: In the left column, select Memberships. From the middle column, drag Membership ID to Results fields to display.
  2. To display Membership Level: In the left column, select Memberships. From the middle column, drag Membership level to Results fields to display.
  3. To display the Member's name: In the left column, expand Members and select Constituent. From the middle column, drag Name into Results fields to display.
  4. To display the Transaction Date: In the left column select Membership Transaction. From the middle column, drag Transaction date (date only) into Results fields to display.
  5. To display the Transaction Type: In the left column, select Membership Transaction. From the middle column, drag Action into Results fields to display.
  6. To see the Cost of the membership. This field excludes any other item (such as an additional donation or a ticket purchase) that may be on the same transaction.: In the left column, expand Membership Transaction and select Revenue Application. From the middle column, drag Amount to Results fields to display.
    User-added image
  7. (Optional) To include refunded amounts in your query. This is helpful if you want to subtract the amount refunded from the amount paid: In the left column, expand Membership Transaction, expand Revenue Application, and select Refunded Item. From the middle column, drag Net amount into Results fields to display. 
  8. User-added image
  9. (Optional, but recommended) If you offer Membership Promotions, you may want to include the promotion amount in your query. This is recommended because the Membership Transaction\Revenue Application\Amount field only considers promotional amounts for back office transactions, not for sales order transactions. You can add the following field to subtract the promotional amount used for a Sales Order Membership Transaction from the Revenue Application Amount Field: 
    1. Under Browse for fields in, expand Membership Transaction, expand Revenue Application, expand Revenue Details, expand Sales Order, expand Sales Order Item, highlight Sales Order Item Promotion.
    2. From the middle column, drag Amount to Results fields to display.
    3. If you offer multiple promotions such as a discount and applied tickets, you may also want to apply a SUM function to this field. Highlight the field and click the Sigma Sign, then check the SUM box.
      User-added image

Save your results:
  1. Click Set save options tab
  2. Name your query
  3. Save and Close