introduction to advance web - mit resource...

Introduction to Advance Web

SunGard Higher Education - Confidential & Proprietary 1


Chapter 7: Standard Lookups, Advanced Lookups, Clipboard, Reporting

February 15, 2012

SunGard Higher Education

• This document contains information that is confidential or proprietary to SunGard (or its direct and indirect subsidiaries). By accepting this document, you agree that: (1) if there is any pre-existing contract containing disclosure and use restrictions between your institution and SunGard, you and your institution will use this information in reliance on and subject to the terms of any such pre-existing contract; or (2) if there is no contractual relationship between you or your institution and SunGard, you and your institution agree to protect this information and not reproduce, disclose or use the information in any way, except as may be required by law.

February 15, 2012 | www.sungardhe.com 2



Basic Lookups Defined

• A Basic Lookup uses a built-in template that allows users to perform searches within a single subsystem

• A default output is matched to the lookup template selected


Accessing Basic Lookups

• Navigate to the Lookups Application by selecting Lookups from the Main Menu




Basic Lookup Application

• When the Lookups Application displays, default selections display for

—Template,

—Results Format,

—Output Type

• Use these defaults or select different values

• When selecting a different template, the results format changes accordingly


Basic Lookup Templates

• User selects a pre-defined lookup template from the template dropdown





• Templates have a set of pre-defined attributes for establishing lookup criteria

— i.e. address, name, affiliation, etc

• Lookup form changes based on template chosen

• Selecting additional attributes from the Lookups Page Tree is considered a variation from one of the pre-defined templates - No template appears in the dropdown



• Some lookup templates consist of a single form, while others consist of multiple forms

• The template lookup forms are the same lookup forms that appear when selecting equivalent forms in the Lookups Page Navigation Tree

—For example, if you select the Bio – Entity Template, the ID, Entity, Address and Geo Code Lookup forms appear.

—These same forms, with the same fields, can also be accessed individually by selecting each from the Biographic branch of the Lookups Page Tree




Lookup Results Format

• Results format that corresponds to the template selected appears in the “Select results format” drop-down list

—Example: Bio – Entity template displays Entity Lookup List as the default results format

—Default results format can be changed


Lookup Result Format

• The Lookup template and results format must correlate to each other

—When the Giving Transaction Lookup List results format is selected, Advance requires that gift/pledge search criteria be entered

—User receives a message if required search criteria is not entered




Select Output Type

• The output type allows you to specify how you wish to view the results of the lookup

—default is to display the results on your screen

• Additionally, you are not limited to one output type.

—For example, you may choose to first get a count of results, then view the results, then save the results to a file, and finally, copy the results to the Clipboard.


Output Types

• Display results/open on exact match

• Display results

• Show count

• Print results

• Copy to Clipboard

• Copy to Clipboard/Replace

• Save as Id list

• Export to file

• ** More on these later




Lookup Criteria Considerations

• Being familiar with the way your institution or organization stores data helps when you are establishing criteria for your lookup.

• Knowing what to look for, and where to look for it, can go a long way in not only making your job easier, but making your results accurate.

• Know your institution’s policies and procedures

—Can help with performing lookups


Enter Lookup Criteria

• Select appropriate lookup form/template for your search

• Select the desired operator from the dropdown

—Default values are Begins with or =

• Enter desired lookup criteria

• Drill-downs are populated by corresponding TMS views


Operator list

Enter lookup criteria



Lookup Operators

Operator Description

= equal to

!= not equal to

> greater than

< less than

>= greater than or equal to

<= less than or equal to

Begins with match values must begin with entered search criteria; acts as wild card (eg., smith%)

Like used with wild cards % and _

Ends with match values must end with entered search criteria; acts as wild card (e.g., %smith)

Contains match values must contain entered search criteria; acts as wildcard (e.g., %smith%)


Lookup Output Result List




Additional Output Options

• Additional output options are available from Lookup Results Actions list


Lookup Output

• If you accept default output type, results display in a Lookup Results list

• Display Results/Open on exact match displays results of query to your display in a list

— If there is only one match, Advance opens the record and displays it

• Display results (without Open on exact match) always displays lookup results in list

— If there is only one match, the lookup result list consists of a single record

— *** Other output types will be covered later




Results Display Note

• Address displayed in Entity Lookup List will always be the preferred address, but…

• An entity with any address row (whether seasonal, business, home, etc.) that meets the entered criteria (e.g. state=New York) will be returned

• May appear to have incorrect results, but the selected entity has another address row with state=New York

• The data qualifying the entity to be in the results set may not be the data that displays in the results set


Simple Lookup with Entity ID

• Enter the entity ID in the ID field

• Valid IDs will display entity link next to ID field

—Alt ID does not display hyperlink




Bio – Affiliation Criteria

• Scenario

—Find all entities with Board of Trustees affiliation and record type of Alumnus Alumna

• Steps

—Select Bio-Affiliation lookup template

—Select Board of Trustees from the Affiliation drop-down

—Select Alumnus Alumna from the Record Type drop-down

—Click Search


Bio – Affiliation Lookup Results




Gift – Transaction Lookup

• Scenario

—Find all entities that have given gift of $1000 or more to the 2007 Annual Fund allocation

• Steps

—Select the Gift – Transaction lookup template

—Type Annual Fund in the Allocation textbox

—Type 1000 with an operator of >= in the Amount textbox

—Type 2007 with an operator of = in the Year Of Giving column

—Click Search


Gift – Transaction Lookup Criteria

• Transaction Lookup searches for gifts by default

• Lookups can search for gifts, matching gifts, and pledges


Link to Allocation Overview



Gift – Transaction Lookup Results


Prospect – Prospect Lookup

• Scenario

—Find all active, major, individual prospects with a rating of $10,000 or higher

• Steps

—Select Prospect – Prospect lookup template

—Select Major Prospect from the Prospect drop-down

—Select Active from the Active drop-down

—Select Individual from the Type drop-down

—Select $10,000 from the Rating drop-down and an operator of >=

—Click Search




Prospect – Prospect Lookup Criteria


Prospect – Prospect Lookup Results




New Searches from Result List

• Click on the New Search button to return to the blank default Bio – Entity basic lookup page

• Message returned when Lookup returns no results


Refining Searches

• Refine searches to retrieve desired output or to change result format or output type




Refining Searches – Viewing Criteria


Refining Searches

• Click on Lookup links to modify/enter new search values

• Search criteria from previous lookup defaults (Refine only)




Task 1 – Perform basic bio, gift and prospect lookups


Objectives

• Understand the difference between basic and advanced lookups (continued)

• Learn about advanced lookup features and functions

• Navigate within the Advanced Lookup forms

• Understand how the database processes an Advanced Lookup

• Discuss technical boundaries of an Advanced Lookup




Advanced Lookups Defined

• Advanced Lookup is a built-in feature that allows end users to perform complex, multi-dimensional queries

• Unlike a Basic Lookup, queries can be built with criteria from a combination of the Advance subsystems


Basic vs Advanced Lookups – Key Differences

• Basic lookups

—Enter criteria within one subsystem

—Default output type matched to lookup template

• Advanced lookups

—Enter criteria across subsystems

—Output type defaults to Entity Lookup List




For Example...

• Basic Lookup – Allows selection of criteria within one category

—Entities from the Class of 1991 living in New York

• Advanced Lookup – Allows selection of criteria from bio, gift, prospect, event tracking and/or membership categories

—Entities from the Classes of 1991-1995 living in New York or Connecticut who have made a gift of $100+ in past year


Accessing Advanced Lookups

• Navigate to the Lookups Application by selecting Lookups from the Main Menu




Advanced Lookup Links


Advanced Lookup Features

• Basic Lookup template drop-down blank when Advanced Lookups activated




Advanced Lookup Result Format

• Result format defaults to Entity Lookup List for Advanced Lookups

• User must select appropriate result format

• Same result formats as basic lookups


Output Type

• Output types the same for Basic and Advanced Lookups

— ‘Display results/Open on exact match’ default output type




Establishing the Relationship

Some points to remember

• Advance subsystems, while separate, fit together like a puzzle

• Advanced lookups can be performance-based on criteria entered across the subsystems but….

• Choosing a results type associated with one Advance subsystem (i.e. Prospect Lookup List) requires at least one criterion related to that subsystem (Prospect)


Entering Search Criteria

• Each subsystem expands to display context-specific lookups




Multi-dimensional Criteria

• Enter Lookup criteria in as many tabs and sub-tabs as desired

• Criteria from different fields and/or sub-tabs are joined by operator AND (i.e. records must meet all criteria to be selected)


Multi-Dimensional Examples

• Select criteria from bio, gift, prospect, event tracking and/or membership categories

—Board of Trustees (Bio, Affiliation) who made a gift in fiscal year 2011 (Gift, Transaction)

—Gifts to the Annual Fund (Gift, Transaction) by your assigned Prospects (Prospect, Prospect and Assignment)

—Contact Reports (Prospect, Contact Reports) for prospects in the solicitation stage (Prospect, Prospect)




Special Note on Gift Forms

• Gift, Pledge and Matching Gifts are designed as separate forms with expanded field selection in Lookups, thus a search must be limited to one (Remember, criteria across attributes are joined by AND)

• Different from using Lookups templates which allow you to search for any combination of the three transaction types


Special Note on Gift forms

• When searching by giving amounts in Lookups, you have three options

—Use the Gift, Pledge or Matching Gift Forms to search based on an entity's individual giving amounts (legal or soft credit)

—Use the Donor Summary tab to search based on an entity's lifetime giving totals (legal or soft credit)

—Use the Giving Total tab to search based on an entity's fiscal year giving totals (legal or soft credit)




Advanced Lookups – A Review

• Designed to

—select multiple values or ranges within a criteria field

— join criteria in fields within subsystem and across subsystems by AND

—alter output type to view results in different formats

• Not designed to

—search for absence of data

— join criteria in fields within subsystems and across subsystems joined by OR

— join to a table twice to find multiple instances of a value

—create outer joins to allow the retrieval of results whether or not all criteria is met


Understanding Your Data

• Users must understand where their institution stores its data

• There are fields on different tabs with the same or similar labels, but they query different data


Q Lookup Sub-tab Field Name Table Queried

Entity Record Type Class Entity Record Type

Entity-Person Pref Class Year Entity

Affiliation Affiliation Year Affiliation

Degree Year Degree



Beyond Advanced Lookups

• The Advanced Lookup tool expands reporting functionality available to the end user, but…

• Some report needs will be out of the scope of the Advanced Lookup design

• Queries or selections that are out of the scope of Advanced Lookups should be handled as an ad hoc report request at your institution


Task 2 – Perform Advance Lookup combining bio, gift, and prospect tracking information




QUESTIONS?


Saving Criteria

• Save lookup criteria for reuse

• Saves time when running similar lookups with multiple search values

• Can modify or save new criteria lists




Saving Criteria Features

Existing Saved Criteria list

Saved criteria owner user group

Is the saved criteria shared

Attributes of Saved

Criteria list


Saving Criteria Features

Existing Saved Criteria list

Is the Saved Criteria shared

Attributes of Saved Criteria list




Saved Criteria

• Modifying an existing saved criteria list

—Select appropriate saved criteria list – detail and security appear

—Make appropriate changes

—Click Save or Delete as appropriate

• Saving a new criteria list

—Select New

—Provide a name, description, owner usergroup, and mark the Public box if appropriate

—Click Save

—Apply security as appropriate


Saved Criteria Security

• Saved Criteria Lists display if

— you created it

— you are a ‘Super Group’ user

—Or you belong to the rights group that can execute Public criteria and the rights group has been granted execute rights for that set of criteria


Rights groups and permissions

currently assigned

Modify or assign new rights groups



Security

• Criteria lists can be saved as Public or Private

—Private – Inquiry/Maintenance by the creator and Super Group users only

—Public – Open for all to work with based on privilege level


Security Privilege Levels

• There are three public list privilege levels

—Execute – Grants users the ability to view a criteria list and execute searches

—Modify – Grants users ability to update saved list information

—Delete – Grants ability to delete a saved criteria list

• Privilege levels can be granted/denied using Rights Identifiers

• Super Group Privileges (indicated in user detail of security profile) allows ability to execute/maintain all saved criteria lists




Loading Saved Criteria

• Saved Criteria… link is accessible from the Lookups page

Only list the user has access to will appear



• Select the saved criteria list you wish to use from the list and click OK

• Saved Criteria will appear in the View Criteria form

—Note: Saved criteria is appended to existing criteria present in the lookup forms

—Clear pre-existing lookup criteria prior to loading saved criteria when appropriate





• Use the Clear button to remove existing lookup criteria before loading saved criteria (if desired)

• Advance appends the saved criteria to existing criteria specified for the lookup


Viewing SQL Details

• The View SQL button generates previews of SQL

• An SQL statement will be performed for each sub-tab with entered criteria

• SQL statements can be viewed prior to performing lookup




Viewing SQL

• Users may preview SQL to proof entered criteria

• Users may want to print SQL when working with report programmers to fine-tune their queries

• Report programmers may want to preview or export SQL to avoid writing code from scratch


Viewing SQL

• Users can view the SQL generated by their lookup criteria from the View Criteria window




Viewing SQL

• The view criteria window will appear displaying the SQL associated with the provided lookup criteria

• Several different queries may be present in the SQL window to cover all lookup criteria


Comparing SQL to Lookup Criteria

• The search values from your lookup criteria will appear in the appropriate SQL statement

• SQL statements




Expert Mode

• In addition to existing methods of looking up data, lookups can be performed in Expert Mode

• Select Expert Mode from the Actions menu


Using Expert Mode

• In Expert Mode, the data fields have additional information displayed

• The search options outlined above provide additional flexibility in fine-tuning your criteria to perform a lookup, as described on the next slide




Using Expert Mode

• The ‘+’ allows you to enter multiple rows of criteria for the same attribute. A new row, with the same fields and operators, is added to your criteria display

• The ‘(‘ allows you to ‘nest’ your query and specify how the criteria are grouped for the query

• The ‘)’ allows closure of the ‘(‘ to complete the expression for your query

• ‘And’ and ‘or’ operators are necessary to identify a range of information (and) or are mutually exclusive of each other (or)

— For example, if you want to present a range of dates between which you want to search on Class, you would join the rows with ‘and’, like

—Class >= 1999 AND <= 2010


Using Expert Mode

• In this query, you are specifying the lower and upper limits of your date range

• In this example the lookup uses the ‘and’ operator to create a class range

• The ‘or’ operator can also be used in Expert Mode but the results can be very different




AND versus OR

• To make the distinction between how using ‘and’ vs. ‘or’ can change the outcome of your lookup, let’s take the same criteria as shown above and, instead of using ‘and’, use ‘or’….your lookup would then be Class >= 1999 OR <= 2010

• This query would bring back all entities!

—All entities would have a class year either greater than 1999 or less than 2010


Correct Use of OR Operator

• To use ‘or’ properly, you may wish to find entities from the Class of ’99 and the class of ’08. For this query, your first row and second row, separated by ‘or’ are specific by stating

• Class = 1999 OR Class = 2010

• In this query, you are identifying two distinct class years




OUTPUT FOR LOOKUP RESULTS


Result Output – Saved ID List

• Save the results of a lookup directly to a clipboard list by choosing Save as ID list from the output type drop-down

—Saves step of retrieving records before copying to clipboard




Result Output – Sort

• Controls the order of how the data is displayed

• Select Sort from the Actions menu


Result Output – Sort

• Same output look and feel but entities are now sorted by id_number rather than the default of sortname




Result Output – Copy to Clipboard

• The results of a lookup can be copied to the clipboard for use elsewhere in Advance

• There are several different ways to copy to the clipboard

—Copy – copies/appends only the active record to the clipboard

—Copy/Replace – copies the active record to the clipboard replacing all existing clipboard records

—Copy All – copies/appends all lookup records to the clipboard

—Copy All/Replace – copies all records to the clipboard replacing existing clipboard records


Result Output – Copy to Clipboard

• Clipboard application accessible from the Title Frame menu




Result Output – Reports

• The report output option displays the Report List allowing the user to select the appropriate report

• The records from the lookup will be passed to the report

—Reports must be capable of accepting a list of ids, receipt numbers, etc., from the lookup


Result Output – Print Form

• Displays a page of records for print purposes

• Data displayed depends on the result format selected

• Output displayed in a separate browser window




Result Output – Print Form

• When the result format returns an entity id_number, the user will be shown an intermediary page showing which entities are included in the list

• This form comprises the list of entities that have not been excluded for print, based on entity exclusion logic

Print Form takes user to output list


Result Output – Export Form

• The Export Form option exports the results of the lookup to a Comma Separated Values file (*.CSV) or…

• User can also save the *.csv file locally




System Option Considerations

• There are several system options that directly affect the Advance Web lookups and should be reviewed prior to performing lookups

• 130 Web Lookups - Display/Print Governor - Soft Limit

—Specifies how many rows can be returned before the system yields a warning message – default is 100 records

• 131 Web Lookups - Display/Print Governor - Hard Limit

—Specifies how many rows can be returned before the system prevents you from continuing with the lookup -default is 500 records



• 132 Web Lookups - Copy To Clipboard Governor - Soft Limit

—Specifies how many rows can be returned before the system yields a warning message – default is 1,000 records

• 133 Web Lookups - Copy To Clipboard Governor - Hard Limit

—Specifies how many rows can be returned before the system prevents you from continuing with the lookup–default is 5,000 records

• 134 Web Lookups – Export/Save as Id list governor- Soft limit

—Specifies how many rows can be returned before the system yields a warning message – default is 100,000 records





• 135 Web Lookups - Export/Save As ID List Governor –Hard Limit

—Specifies how many rows can be returned before the system prevents you from continuing with the lookup -default is 300,000 records

• 175 Default Public Clipboard Rights Group

—This system option allows you to specify the Rights Identifier code that will automatically be associated with a Clipboard when it is created via the Clipboard window

• 202 – Maximum rows for lookup – default is 500

—Lookups exceeding this count will require the user to restrict the lookup


Documentation

• Review the Advance Web Lookup User Guide and Configuration Utility User Guide for more details and a complete listing of the Advance Web Lookup features

• [email protected]




CLIPBOARD


Clipboard Application

• The clipboard allows you to store lists of entity id’s that can be viewed, saved and manipulated

• IDs may be copied to ID Lists from lookup results

• The Clipboard is used to access and modify lists that have been compiled for later use or to be shared

• Use hypertext links to quickly access an entity profile via the clipboard




Creating a Clipboard

• From a Lookup Results list, click the Actions menu to select

—Copy

—Copy/Replace

—Copy All

—Copy All/Replace

—Reports

—Print Form

—Export Form


Accessing a Clipboard

• From the Title Frame menu, click the Clipboard icon




Clipboard List

Clipboard lists

Link to Entity Profile


Clipboard Buttons

• Click New to add an ID to your clipboard list

• Delete may be used to delete an ID from the list

• Clear will remove all of the entity ID’s from your clipboard; this will not affect any saved lists

• Clear All will remove all the entity IDs from all active clipboards

• Import List will import a text file of id’s

• Save will allow you to save the id list




Clipboard Buttons

• Load List will open a saved id list

• Merge List will merge id lists together

• Intersect will combine id’s that exist on both list A and list B

• Exclude will remove entities from the clipboard based on an exclusion clipboard list


Saving a Clipboard

• Click the New button

• Enter clipboard name, click the Active checkbox, indicate whether private or public

• Click the Save button




Accessing a Saved List

• Click the Load List button to view previously saved lists


Accessing a Saved List

• The Lookup button (ellipsis) opens the Clipboard List

• The View button (page) opens Lookup result set




VCR Feature

• Scroll through list of IDs from clipboard to view information

• Select VCR from Actions menu


Task 3 – Work with the Clipboard




QUESTIONS?


Reports

• Institution specific/developed

• Recent Activity Report

• Entity Profile Report

• Labels




Reporting

• Reports application

—Access via Main Menu

—Three options for Report Development

• Web Forms

• InfoMaker/PowerBuilder

• Crystal Reports

—Security by report

—Prompts supported


Reporting

• Reports application

Click drilldown button

on left to select report




Reporting

• Select a report, enter prompt values (if applicable), click Run Report


Reporting

Link to Entity Profile

Export list to .csv




Reporting

• Output options differ depending on the tool used to author the report

Crystal Reports

Web Forms

IM/PB


Recent Activity Report

• Allows you to view the activity on a user’s assigned prospects similar to Advance




Recent Activity Report


Print Other Reports

• From lookup results, select Actions > Reports




Entity Profile Report

Move attributes you want to have display

on report from left to right side


Entity Profile Report Results




Labels


Labels Output




Task 4 – Generate reports


QUESTIONS?


introduction to advance web - mit resource...

Documents