Back
Adding Tables to a Report
The most basic function of the reporting tool is to add tables to a report. These tables correspond directly to tables in DNA’s database.
To add a table:
1.Select the name of the database table to add from the ‘Table’ drop-down
2.From the ‘Column’ drop-down, select a column to which any operators will be applied
3.Click on the blue ‘Add’ button
The choice of column in step 2 is significant if an operator is going to be applied to this table, or if the results are going to be grouped or ordered.
Operators
Once tables have been added to a report, the results can be refined by adding operators. These are filters that can be attributed to one of the table’s columns to filter out results.
To add an operator to a table:
1.Make sure that the selected column is the column to which you want to apply the operator
2.Select an operator from the ‘Operator’ drop-down list
3.Add a value to apply the operator against
4.Click on ‘Save Search’
For example, if you wanted to select only active memberships:
1.Add the ‘Membership’ table to the report
2.Select ’Membership Type’ as the Column
3.For the Operator, select ‘=’
4.For the Value, select ‘Active’
For more detailed information on each of the available operators, please refer to the ADNA manual.
Multiple Operators on one Table / Column
It is possible to impose multiple rules on a single table, either upon the same column or two different columns. If you would like to add multiple conditions to a table, include the table in the report more than once, choosing each different rule every time the table is added.
Wildcards and LIKE
The LIKE operator is similar to the ‘=’ operator, with two main differences. The first is that ‘=’ is case sensitive, while LIKE is not.
The second is that LIKE allows you to check text using wildcards. A wildcard is a special character that will find other characters based on specific rules. The most commonly used wildcard is the ‘%’ character, which means ‘anything’. This wildcard works with the LIKE operator to help search for patterns in text.
For example, if you wanted to return all members with the names ‘Jesse’, ‘Jessie’, or ‘Jessica’:
1.Add the ‘Contacts’ table to the report
2.Select ‘First Name’ as the Column
3.Select ‘LIKE’ as the Operator
4.Enter ‘Jess%’ as the Value
This effectively means ‘show all members whose first name begins with the letters ‘Jess’’.
Dates and NOW()
One of the common types of data you will be using in your operators are dates. Checking dates is an effective way to search for data that is a specific age. There are a few items to keep in mind when dealing with dates.
1.DNA uses a yyyy-mm-dd format to store its dates. This means that August 18, 2016 is stored as 2016-08-18 in the system. In order for date operators to be effective, the inputted format must match the above.
2.The >=, <=, and = operators work with dates. For instance, if you want to check records from 2015 and beyond, you can set the rule “Date >= 2015-01-01”.
NOW() is a special value that can be used to check dates. When NOW() is inputted into the Value column, it automatically uses today’s date when the report is run. In order for the NOW() command to work, the ‘No “‘ option must be checked off in the row that contains the command. This lets the report tool know that NOW() should be treated as a command rather than as text.
Grouping and Ordering Report Results
Two additional ways of filtering report results is by grouping or ordering them.
Grouping is a method of eliminating duplicates in your report results; when grouping on a column, only the first result found for each value in that column will be displayed in the report. To group report results, simply check ‘Group’ next to the table and column that you want to group by.
Ordering results allows you to sort report results by a particular column. To order by a column, simply select ‘ASC’ or ‘DESC’ from the Order column, depending upon whether you want to sort results in that column in ascending or descending order.
Adjust Reports to Show a Limited Number of Columns
You may not always want to display every column in each of the tables returned by your report. To limit report output to a set number of columns:
1.Build your report using tables as desired
2.See the names of the tables displayed below the Custom SQL box
3.Click on a table name to see all of the columns in that table
4.Check which specific columns you would like to appear in the report
5.Click on ‘Save Search’
If a report contains tables that share one or more columns with the same name, it is necessary to specify which of these columns should be displayed in the final report. For instance, both the ‘Event Registrations’ and ‘Events’ tables contain a column named ‘status’. If both of these tables are added to a report, the data from the last table added will override the data from the first table added. To ensure you are always displaying the desired data, use this feature to specify which table the column should come from.
View and export report results
Once a report has been completed, click on the ‘View Results’ button to see a quick preview of the report’s results, or click on ‘Export CSV’ to download the full report in .csv (Excel) format.
Retrieving ID Numbers
Some reports make use of the ID numbers of contacts or events in order to retrieve their results. The simplest way to discover the ID of an event or contact is to view it in DNA, and take notice of the ID number that is displayed at the end of the URL for the page you are viewing. Below is an example of where the ID can be seen for an event.
Back