- Navigate to Analysis, then click on Information library
- Click Add a new ad-hoc query
- Select the source view of Revenue and click OK
- First, we will create a selection of all of the membership transactions you would like to acknowledge:
- To filter on all memberships: From the left column, highlight Application Details. Drag Application to Include Records where and set this to be equal to Membership.
- To filter on a specific action: From the left column expand Application Details and highlight Membership Transaction. Drag Action to Include Records where and set the field to be equal to or one of and select the action you are looking for.
- To filter on a specific membership level: Expand Application Details, expand Membership Transaction and highlight Membership. Drag Membership level to Include Records where.
- On the Set save options tab, name your query and check the boxes to Create a selection and Show this selection in query designer.
- Now we will use this selection to find which of these revenue records also contained a donation. Go back to analysis and add another Ad-hoc query. Select a source view of Revenue.
- In the left column, expand Application details and from the middle column, drag Application to Include Records where. Set this to be equal to Donation. Your field will appear like this: Application Details\Application is equal to Donation.
- In the left column, find the Selections folder. Find the selection we created in Steps 1-4. From the middle column drag "Exists in 'Selection Name'" to Include Records where. Set this to be equal to Yes. This will appear as: and Exists in "Membership Transactions" is equal to Yes.
- On the Set save options tab, name your query and check the box to Create a Selection.
Step 2: Build the smart field.
- Go to Administration > Smart Fields
- Click Add and select Constituent Revenue application amounts
- Name your smart field and click the Parameters tab
- Under Value to return, select Total application amount
- Under revenue types, select all revenue you’d like to see. If you just want donations and not sponsorships or any other type, click Donations only
- Under Transaction type/Application click Donation under Order (for sales orders) and Payment (for back office payments)
- Under Application selection, click the magnifying glass. We will need to create a revenue application for this.
- Click Add. In the box that comes up select Revenue Application
- Expand Revenue Details, then click the folder labeled Selections
Double-click the name of your query created in Step 1.
- In the criteria box that pops up, set the criteria to Yes. Click OK
- Click Revenue Application at the top again, then drag application to the Include records where: box. Set this criteria to Donation. The resulting query should look like this:
- Click the Set save options tab and give the query a name. Click Save and close
- Your smartfield is done and should look like this. Click Save.
Step 3: Run the Smartfield. Once you click save it’ll take you back to the smartfield page. Click the name of your smartfield and then click Start process in the top left corner.
Step 4: Create the new report query. Since this query will use the same parameters as the first one, we can actually copy the query in Step 1.
- Go back to Analysis > Information Library
- Click the dropdown arrows next to your query from Step 1.
- Click Copy. This will open a new query with the same criteria as your first query.
- Expand Constituent, then click the Smart fields folder
- Double-click the smartfield you created
- Drag Value into Include records where: and set the criteria to Greater than and then 0
In Results fields to display, I would add Amount under Revenue (the top node) to get the total amount paid. This includes the membership and donation
- Click Application Details, then drag Amount to results fields to display. This will give you the Membership payment amount
- Drag the smart field criteria from Include records where to Results fields to display. This will copy that field and give you the donation amount.
And the results look like this:
NOTE: You will note get total donation amount done for each transaction time frame without editing both the initial query and smart field. For example, if I wanted to see all transactions from 1/1/17-12/31/18 I would have to change the date criteria in the first query and then re-run the smart field for those values to change. To get a date range in the first query you’d edit it and add Application Details\Membership Transaction\Transaction date is between [your dates].