1. From Analysis, click Information Library
  2. Click Add a new Ad-hoc Query
  3. Select the record type of Membership
  4. Highlight Membership in the list of Source views and click OK. User-added image
A new ad-hoc query window will appear. On the Select fields and output tab, use the instructions below to add fields to the "Include records where" box to find the membership transactions you are looking for:
  1. First, if you would like to find membership transactions that occurred within a certain date: Under Browse for fields in, highlight Membership Transaction. In the middle column "Select Membership Transaction fields" drag Transaction date to "Include records where." Your field will look like this: Membership Transaction\Transaction date (date only) between 1/1/2013 and 12/31/13 
  2. Next, with Membership Transaction still highlighted on the left, from the middle column "Select Membership Transaction fields" drag Action to "Include records where." Set the action to be one of Join, Renew, Upgrade, Downgrade, Rejoin. Here is an example of how the field will look under "Include records where" Membership Transaction\Action is one of Join, Renew, Upgrade Downgrade, Rejoin User-added image
  3. Next, if you'd like to only display the primary member name, under Browse for fields in, highlight Members. In the middle column "Select Members fields" drag Primary member to "Include records where." Select Equal to Yes. Your field will look like this: Members\Primary Member is equal to YesUser-added image
  4. (Optional) If you would like to view only transactions that were processed in a specific sales method (for example, online sales or daily sales), you can add this field to your criteria:
    1. Under Browse for fields in, expand Membership Transaction, expand Revenue Application, Expand Revenue Details, highlight Sales Order.
    2. In the middle column "Select Sales Order fields" drag Sales method type to "Include records where."
    3. In the apply criteria window, select Equal to or One of and choose the sales methods you'd like to include. User-added image
  5. (Optional) If you would like to exclude fully refunded transactions, you can add this field to exclude them from your results: 
    1. Under Browse for fields in, expand Membership Transaction, expand Revenue Application, Expand Revenue Details, highlight Sales Order.
    2. In the middle column "Select Sales Order fields" drag Refund status text to "Include records where."
    3. In the apply criteria window, select Not Equal To Fully Refunded. Make sure the box is checked to Include Blanks.
    4. Your field will look like this: Membership Transaction\Revenue Application\Revenue Details\Sales Order\Refund Status text is not equal to Fully Refunded or is blank. User-added image
Use the following instructions for your output on the Select filter and output fields tab:
  1. To display the Membership ID, under Browse for fields in, highlight Memberships at the top. In the middle column, "Select Memberships fields" drag Membership ID to "Results fields to display" Your field should appear like this: Membership ID
  2. To display other membership information such as Membership Level, under Browse for fields in, highlight Memberships at the top. In the middle column, drag any other applicable membership information to "Results fields to display."
  3. To display the Member's name, under Browse for fields in, expand Members and under that highlight Constituent. In the middle column, drag Name to the "Results fields to display." Your field should appear like this: Members\Constituent\Name
  4. To display the transaction date, under Browse for fields in, highlight  Membership Transaction and in the middle column, drag Transaction date (date only) to the "Results fields to display." Your field should appear like this: Membership Transaction\Transaction date (date only)
  5. To display what type of transaction it was, with Membership Transaction still highlighted, in the middle column, drag Action to "Results fields to display."
  6. Use this field to return the amount 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. Under Browse for fields in, expand Membership Transaction and highlight Revenue Application. In the middle column, drag Amount to Results fields to display. Your field should appear like this: Membership Transaction\Revenue Application\Amount. Here is an example of how your Results Fields to Display box will appear after completing steps 1-6: User-added image
  7. (Optional) Use these field, if you would like to include refunded amounts in your query. This can be helpful if you want to subtract the amount refunded from the amount paid:
    1. Under Browse for fields in, expand Membership Transaction, expand Revenue Application, and Refunded Item.
    2. In the middle column "Select Refunded item fields" drag Net amount to Results fields to display. User-added image
  8. (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. Your field will look like this:  Membership Transaction\Revenue Application\Revenue Details\Sales Order\Sales Order Item\Sales Order Item Membership Item Promotion\Amount.
    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