Aristotle Support

Focus On: Custom Report Generator

Article ID: 792
Last updated: 07 Mar, 2010

Focus On: Custom Report Generator  

 

Welcome to our Focus On series. In this series of e-mail tutorials, we will show you a few overlooked features in our system. We encourage you to print this document to follow along through the lessons.

 

Tutorial: Custom Report Generator Features

 

Most of us use the Custom Report Generator everyday. From generate specific mailing lists to donor reports; the custom report generator has many functions and uses. However, there are some commonly overlooked features that we’re going to go over in this Focus On.

 

In this tutorial, you will learn:

Lesson 1: Understanding Queries

Lesson 2: Aggregates in the Query

Lesson 3: The Radio Button in the Query Details Section

Lesson 4: Using the Multiple Report Export and Having No Export Method

Lesson 5: Saving Custom Reports

 

In order to use the Custom Report Generator Tutorial, you must have the access permission level of Export.

 

Lesson 1: Understanding Queries

 

What is a query?

 

A query is like directions. You are giving the database specific directions for it to use when pulling records that you want to see in your export (whether it be a report or a mail merge). It can be simple or complex and it can stand alone or have multiple pieces or sub-queries.

 

Each piece of your query is made up of one or more criteria. In your database, the criteria are made up of five parts: Join, Table, Field, Relationship, and Value.

 

When you select a Table from the drop-down menu, the options available under the Field menu become more specific.  In general, as you move from right to left in your query menus, the options available will become more and more specific.

 

This is a simple query:

 

This is a complex query:

 

So if that’s a query, what is a sub-query?

 

Sub-Queriesare powerful tools for doing complex targeting. A sub-query allows you to select more than one field at a time from the same table, thus simultaneously placing multiple conditions on a single set of search results.  

 

Ok, did I lose you with “conditions?”  The conditions that I’m talking about are those directions again. You are giving the database direction for pulling this set of people out of you query.

 

 

In this query, we added a sub-query by using the And Transaction Date is equal to or greater than 01/01/2005. In this case we are asking the database to pull every record that has a total contribution of $500 or more AND those contributions are dated equal to or greater than 01/2005. Our sub-query allows us to exclude any contributions that do not have that date or greater. If Bob gave us $500 on 12/31/2004 and a $3 contribution on 01/01/2005, he would not show up in our report because his total contributions were not made after 01/01/2005.

 

A sub-query can be anything, it does not matter which order you put it in. In this case, we put the transaction date as the sub-query but, just as easily, we could have put the transaction date first and made Total Contribution the sub-query. Don’t get hung up on order as much as recognizing what the sub-query does.

 

Counts and Sub-Queries

Running Count will give you a running total of record counts as your query is calculated and returned by each statement of your query.  Because sub-queries are executed as a single unit, the entire sub-query will only return a single count number in this running total.

 

Why wouldn’t I add it as a second piece?

 

Multiple pieces are different from sub-queries. With a sub-query you are putting a condition on the directions you are giving the database for that set of data. If we separate the sub-query out of the query, we are giving it different directions. Let’s look at the example above separated into multiple pieces:

 

 

Piece 1 is asking us for total contributions over $500.

 

Piece 2 is asking for transaction dates that are greater than 01/01/2005.

 

At first glance, this appears to be very similar to the sub-query, however our results are different. If Bob gave us $500 on 12/31/2004 but had a $3 contribution dated 01/01/2005, he will show up on our report but our second piece was not a condition of our first piece.

 

Lesson 2: Aggregates in the Query

 

What is an Aggregate?

 

Ok, in order to understand aggregates, we need to understand what is NOT an aggregate. If we use the Table: All Contributions and the Field: Amount, in Relationship we select is greater than or equal to and in Value we type: 100. This query will select individuals who have given $100 or more at a single time. Since Amount refers to a single amount, it is a non-aggregate.

 

 

However, if we use the same query but instead of Field: Amount, we use Field: Total Amount,  our query will select individuals who have given a total amount of $100 or more, no matter how many contributions they made to reach that sum. Since Total Amount refers to more than a single contribution, it is an aggregate. Other aggregates are Average, Largest, Smallest and Count.

 

Aggregates will always be marked with an asterisk. *

 

 

How can I use Aggregates in my queries?  

 

You can use aggregates alone or in sub-queries. For instance, if you’d like to find everyone who contributed more than that the total amount of $500, your query would look like this.

 

Join

And

Table

Monetary Contributions

Field

*Total Amount

Relationship

Is Greater Than

Value

500

 

 

You can also use aggregates in sub-queries. When an aggregate is used in combination with other statements in a sub-query, only the entries selected by those other statements will be considered in calculating the aggregate.  For example, if you enter the following query:

 

Join

And

Table

Monetary Contributions

Field

Transaction Date

Relationship

Is Equal to or Greater Than

Value

01/01/2004

Sub-Query Join

And

Field

Category

Relationship

Exactly Matches

Value

Fourth of July Event

Field

*Total Contributions

Relationship

Is Equal to or Greater Than

Value

100

 

 

The results of this query will all be records that have total contributions of $100, who also have a checkdate after 1/1/2004 and are in the Fourth of July Event category.

 

 

 

Lesson 3: Radio Buttons in the Query Details Section

 

The Radio Buttons in the Custom Report Generator

 

It’s a rare occasion that we don’t label something in the database. Usually we try to give you a definitive label so there are no questions about what a button does in the system. This is one of those occasions when the label would be much larger than the button.

 

You may have noticed these tiny, little buttons.

They appear in the Query Details area of the Custom Report Generator after you have built your query in the Custom Report Generator and clicked the Add to Query button.

 

They’re called radio buttons because about a million years ago in old car radios, you pushed one button, the dial moved. When you pushed another, the first choice was dropped and the dial moved again. (I only remember this because my dad’s truck only had AM radio and it was torture to drive with him).

 

When you click one of our radio buttons, that piece of the query will populate in the Query Builder (when you click another radio button, the first one will disappear and be replaced by the piece you just clicked). This can be helpful in two ways:

If you’ve made a mistake in the middle of your query, you can click on the radio button for that piece of the query, make you corrections and click Update Selected.

If you have a big piece of a query and you have to add a similar piece, you can click on the radio button, change what you need to and click Add to Query to add the new piece.

 

Hopefully this has helped you figure out a better way to correct your reports, or build more complex reports.

 

Lesson 4: Using the Multiple Report Export and Having No Export Method

 

Once you have your query, you need to pick an Export Option. There are no many options to choose from but there are two that you should remember.

 

Multiple Report Exportcan be found under Type: General Format: Multiple Reports.

 

Multiple Reports allows you to run several different reports without having to re-run your query each time. This is very helpful when you’re not sure which report you want to run and you want to try several different reports.

 

In VoterManager, this is an excellent tool to use to get Household Counts for your query.

 

Having No Export Format is another option. To run it without selecting an export method, leave the Type: General and the Format: None to find out your running counts. This is a great way to make sure your query is set up correctly before proceeding to a report or a mail merge.

 

You will find the running count in the Query Details section of the Custom Report Generator.

 

Lesson 5: Saving Custom Reports

 

You can save custom reports so that you can generate them at a later time. This means that if you have a complicated custom report or a report that you run on a day-to-day basis that you can save them and reproduce them at any time.

 

To save a report through the Custom Report Generator, simply click on Save Report when you have finished building your query and the system will ask you to name the new query.

 

You also have the ability to edit previously saved reports or lock certain portions of the report. To edit a Saved Report, go to the Management menu and select Saved Reports. Click on the Modify next to the saved report you wish to change. You will notice that there are two options unfamiliar options:  

  • There are fixed checkboxes along side of each section of the report. The fixed checkbox allows you to lock the selections for that report. For instance, if you only export a particular report as a phone list, you can check the fixed box and the only export method will be phone list. Fixing a report is very helpful when working with volunteers and junior staffers so that you can ensure that the reports will run as you have designed. 

  • You can choose more than one export method. If you want to export a particular report as a phone list and a detailed list, you can select both methods.

 

When you are finished, click Save Report. Now, you will have the option to load this saved report when you go to the Custom Report Generator.

 

 

 

This article was:   Helpful | Not helpful Report an issue


Article ID: 792
Last updated: 07 Mar, 2010
Revision: 1
Views: 396
Prev     Next
Focus On: Custom Exports       Letter Templates Tutorial