Published on February 22, 2014
Query Basics Laurie Osman, Business Analyst The University of Texas System
What is a Query? • A Report? • Another Way to Export Data? • A Grouping of Records? A query in Raiser’s Edge is a grouping of similar records in the database. The query module enables us to group, sort, and output different types of records. 2
Query Types and Formats • A Query Type determines what kind of records you want to group and see in your output • A Query Format determines if the data will be a snapshot or will refresh over time as the database changes 3
The Criteria Tab • Define which group(s) of records you want to pull from the database • Each filter relates to a field in Raiser’s Edge – the key to finding filters is to know which field(s) on the record relate to the information you want to group on 4
Criteria Nodes (Fields) Frequently Used “Nodes” for Constituent Queries • • • • • • • Constituent Information: Biographical fields and record properties Solicit Codes: Codes on the bottom left of the Bio 1 tab. Examples: Do Not Contact For Visits, Do Not Solicit, Outstanding Pledge Addresses: Preferred Address fields (There is a sub node of Phones that is used to filter phone numbers and e-mail addresses) Actions: Fields on a constituent’s actions record (type, date, solicitor, status) Relationships: Assigned Solicitor, Education, Individual, Organization Relationship fields Gifts: Fields on a constituent’s gift records (date, amount, type) Attributes: Information on constituent’s attributes. When querying on attributes, there’s a sub node of Specific Attribute. From there, choose the attribute you want to filter on 5
Query Criteria Tip Query Favorites • For query fields/criteria that you use frequently, you can add them to your favorites by Right Clicking on the field and selecting “Add to Favorites” • The Favorites node appears at the very top of the criteria list and will include all the fields that you have added as favorites 6
Query Criteria Tip Query Fields Allows you to select any field that is currently in your query. Use this to save time when selecting your output fields or sort field(s). 7
Query Criteria Tip Summary Information Allows you to select a summary of certain fields (Gift, Honor/Memorial, Action, or Appeal) 8
Criteria Operators Operator Example Equals / Does not equal Preferred State equals Texas Greater than / Greater than or equal to Gift Amount greater than $1,999 Less than / Less than or equal to Gift Amount less than or equal to $100 One of / Not one of Current Membership one of CC, CCEC Between / Not Between Gift Date between 09/01/12 and 08/31/13 Blank / Not Blank Preferred Email Number not blank Begins with / Does not begin with Last Name begins with O Contains / Does not contain Note Description contains solicitation Ask This operator will ask for operator and value when the query is ran 9
Combining Operators and Parentheses Operator Definition Example AND Records must meet both criteria to be selected. The default combining operator in Query. Last Gift greater than 9/1/13 AND NetCommunity gift equals Yes OR Records can meet either criterion to be selected. Last Gift greater than 9/1/13 OR NetCommunity gift equals Yes Parentheses ( ) Needed when you have 3 or more rows and you are using both AND & OR Preferred State equals Texas AND Last Gift greater than 9/1/13 OR NetCommunity gift equals Yes Preferred State equals Texas AND (Last Gift greater than 9/1/13 OR NetCommunity gift equals Yes) 10
Output, Sort and Results Tabs • Output Tab: Select fields you want to view • Sort Tab: Select field(s) you want to use to sort your data • Results Tab: Preview Results Note: From the Results Tab you can open any record by double clicking on the row that corresponds to that record. 11
Query Tips and Tricks Duplicate Records • When viewing the results in a query, you could see duplicate records if you have one-to-many fields in the output or in your criteria • If you see duplicates in your query, you can remove them in your export file (excel/access for example) or you can run the query through the Export Module which will automatically de-duplicate your query 12
Query Tips and Tricks Creating a Copy of an Existing Query • If you see a query that has all or nearly all the criteria that you need in it, instead of creating a new query from scratch or changing that query (Never change a query that you didn’t create), go to File > Save As and rename the copy of the query for yourself 13
Query Tips and Tricks Query Options Tools Query Options • General Tab: An overview of the basic query information – Query Type, Format, Name, Category (folder), and Description • Record Processing Tab: Details on filtering or limiting – Select From Query: Create the current query from records included in a separate query – Suppress Duplicate Rows: Rows with the same data will be eliminated – Apply Output Limits: Constraints will only be applied when the query is used as an input query • Available only for Static Queries 14
Query Tips and Tricks Query Options Tools Query Options • Gift Processing Tab: Details on how soft credits and matching gifts will be handled – This is a very important tab if you are looking at household giving, including if a donor gave through an organization, etc. • Advanced Processing Tab: The options on this tab are generally not used very often – Used for SQL coding and multiple attributes 15
Query Tips and Tricks Exporting from a Query • To export your query results to a file, click on the export icon (looks like a CD) from the query tool bar • Specify a file type (excel, CSV, access, etc…) and file name (click on the ellipses to specify the name and where you want to save the file) Remember that the export tool from a query will output your results exact as they appear (including duplicates) if there are any 16
Ways to Utilize Queries Query Lists • Link one or many queries to a query list and then you can add or remove specific records 17
Ways to Utilize Queries Constituent Search • Search Using a Query – allows you to change the output • You could also use this if you only search for constituents in a certain criteria. 18
Ways to Utilize Queries Querying with Exports • You can export gifts using a particular gift query in a constituent export. You can use this to filter down gifts based not only on campaign, fund, or appeal, but on attributes, codes, etc. 19
Ways to Utilize Queries Canned Reports • Link queries to Canned Reports to filter which records you want to appear in the report • Under each canned report there is a summary of what the report can run and a link to view samples of the report 20
Ways to Utilize Queries Dashboard Panels • Much like canned reports, dashboard panels can be attached to a query to filter which records to shown • Some Dashboard panels can be the query results, which allows you to change the columns and sort however you want to! 21
Ways to Utilize Queries Clean Up Database • With the BLANK query operator, you can create dynamic queries (such as Address Line 1 blank). • Make the output just the Constituent ID. • Then go to the results tab and double click to open the first record. • Clean up or fix the data, Save and then click the arrow to go to the next record. 22
Ways to Utilize Queries Clean Up Database • With the BLANK query operator, you can also create Business Rules that will automatically help clean up your database over time • Create the query and attach it to a Business Rule and then when a user tries to save the Record, it will pop up informing the user to go back and fix the data 23
Learn to be more productive with SQL Server 2005 Express Edition with this quick introduction to the T-SQL language and the basics of getting ...
You can also search using LDP, which is included in Microsoft Windows Server™ 2003 and Windows® 2000 Server support tools. This tool gives you the ...
A query is a set of instructions that describes what data to retrieve from a given data source (or sources) and what shape and organization the returned ...
5:24; Introduction to queries; 4:45; Building queries with the Query Designer; 4:45; Build queries that use more than one data source; 2:50; Using outer joins
XQuery Basics. XQuery Basics. Sequence and QNames (XQuery) Expression Context and Query Evaluation (XQuery) Atomization (XQuery) Effective Boolean Value ...
Database basics. Applies To: Access 2016, Access 2013, Access 2010, ... A select query simply retrieves the data and makes it available for use.
University of Maine System – PeopleSoft Query Tutorials S. Dunson Consulting 1-3 Query Basics 1 To view a listing of the Hot and Access Keys online, use ...
$(document).ready() Before you can safely use jQuery to do anything to your page, you need to ensure that the page is in a state where it's ready to be ...
jQuery Basics covers why you'd want to use jQuery, what it is and how to include it in your projects. You'll build several projects over the course to give ...
SQL is a standard language for accessing databases. Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server ...