NiUG Discove

ryOrlando, FL

April 16-18, 2012

Advanced IQA

Kye Hittle, Director of ITBeta Theta Pi Foundation

Monday, April 16, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Why use IQA?

• Design a query once for multiple uses• More flexible search options

Multiple parameters in one query Parameters can be calculations Flexible results display

NiUG Discove

ryOrlando, FL

April 16-18, 2012

When should we use IQA?

Displaying small result sets on-screen

NiUG Discove

ryOrlando, FL

April 16-18, 2012

When should we use IQA?

Results need to be live links to a Customer, Event registration, donation, etc.

NiUG Discove

ryOrlando, FL

April 16-18, 2012

When should we use IQA?

Reports with many parametersCrystal Reports IQA

NiUG Discove

ryOrlando, FL

April 16-18, 2012

When should we use IQA?

Extracting results into a spreadsheet

NiUG Discove

ryOrlando, FL

April 16-18, 2012

When should we NOT use IQA?

Reports that require: • Formatting • Layout• Multiple levels of grouping • “Think spreadsheets”

NiUG Discove

ryOrlando, FL

April 16-18, 2012

How End-users Run IQAs

1. Find and select IQA2. Click Run (or double-click)3. (optional) Supply filter values & click 4. (optional) Select record or extract results

NiUG Discove

ryOrlando, FL

April 16-18, 2012

How Report Designers create IQAs

1. Gather & Analyze reporting requirements2. Determine/create data Sources (Business

Objects) and link3. Create Filters4. Select Display fields5. (optional) Set Sorting order, Security6. Test7. Save

IQA Design Overview

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Gather reporting requirements

• Do we already have something similar? Where else will the organization need to use similar queries?

• Where is the data located in the iMIS interface? How is it linked?

• What filters are needed?• What columns do you need to display?

Any sorting?

1. Reporting Requirements

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Analyze reporting requirements

• Do Business Objects exist to access required data?

• Are subtotals, counts or averages needed?

• Are

1. Reporting Requirements

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Basic vs. Advanced Mode

• Basic Mode Define sources + filters + what to display Run or Run as Report

• Advance Mode Using Queries in iMIS searches Aggregate criteria & display SQL Expressions in queries

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Choosing Business Objects

iMIS Standard Prefixes• Most built-in biz objects start with ‘Cs’• Biz Objects built for Custom Demographic

tables typically start with ‘CsDemo_’• Use Biz Obj Designer to see

tables/views/fields in each Biz Obj• …use a naming standard for custom

Business Objects (e.g. prefix with org acronym)

• 2. Sources (Biz Objs) & Links

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Linking Business Objects

• In order to link two Business Objects they must share a common field (often iMIS ID)

• The field names do not have to be identical but the data must match for a record to be included (using default link)

• This is the same concept as (inner) joining relational database tables

CsContact

• iMIS ID• Full

Name• Informal• Email• …

CsActivity

• ID• Activity

Type• UF 1• UF 2• …

• 2. Sources (Biz Objs) & Links

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Saving: Folders & naming

• Decide whether to store all IQAs inCustomers or spread out among individual iMIS modules

• Create and enforce an organizational folder (e.g. departmental, functional) and IQA naming strategy to easily find IQAs

• Create a policy among designers to make copies when they are significantly modifying an IQA – leave original for current uses

• 7. Save

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Saving: Folders & naming

• Well thought-out folders give more context to IQA titles

• Use the description field when creating/updating for historical notes (unfortunately only visible to designers, not end users)

• 7. Save

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Aggregate Functions

• Can be used in two places in IQA Filters Display

• Scenario – Searching for high value constituents Spent within the past year Life time spending is over 10,000

NiUG Discove

ryOrlando, FL

April 16-18, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Subtotals in 15.2

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Business Objects are an abstraction layer

Business Objects can enable field aliases, data formatting, code lookups,

data from multiple tables/views & more

IQA

Business ObjectBusines

s ObjectBusiness Object

Table or

View

iMISDatabase

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Business Object Designer

• Allows you to create/modify Business Objects• Located under Tools or iSA tab

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Business Object Designer

NiUG Discove

ryOrlando, FL

April 16-18, 2012

1. Create view in SQL Mgmt Studio; grant the iMIS Group SELECT permissions: (grant select on <view_name> to IMIS)

2. iMIS > Tools > Business Object Designer

3. New > Design Business Definition4. Give the biz obj a Name according to

your naming scheme & Description > Create Object

Create Business Objects from Custom SQL Views (1/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

5. Go to the Database tab and click the Add button

6. Find view (or table) (type first few letters) > Click Add

7. Click Add All as Properties button (below field list)

8. Click Save, Click Publish

Create Business Objects from Custom SQL Views (2/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Tips & Tricks

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Creating live links

Keywordskey_contact

key_event

key_order

CustomersEvents

Orders

iMIS IDOrder No

Order No

Full list: http://docs.imis.com/15.2/accessingm

odulebasedreports4.htm

Use keywords on Display tab in Alias field to enable Select option

Note! Arrows will not display in Edit mode –

only at run-time.

NiUG Discove

ryOrlando, FL

April 16-18, 2012

SQL Expressions in Display tab

Use for non-persistent values you don’t want to store in the database but

need for reporting(e.g. specialized member name format)

vBoCsContact.FirstName+' '+vBoCsContact.LastName+' '+'is the '+vBoCsContact.Title+'.'

NiUG Discove

ryOrlando, FL

April 16-18, 2012

IQAs fields that display an email/URL (e.g. CsContact.Email) can display

their data as clickable links

3

Turn Email and Website Fields in Queries into Links (1/3)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

1. On the Display tab > Custom > SQL Expression

2. Enter the following into the SQL Expression field:

char(60) + 'a href="mailto:' + vBOCsContact.email + '"' + char(62) + vBoCsContact.Email + char(60) + '/a' + char(62) 

For URLs, leave out: mailto:' +

3. Give the SQL Expression an Alias, such as LinkedEmail

4. Click the symbol to add it to the list of Display fields

Turn Email and Website Fields in Queries into Links (2/3)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Turn Email and Website Fields in Queries into Links (3/3)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Creating live links in 15.2

• Expanded options: content records, navigation items or ASPX web pages

• Display tab > Link field (Advanced mode)

http://docs.imis.com/15.2/linkingiqaresults2.htm see “completed orders web part” example

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Use relative date criteria

Make selections based on current date rather than requiring user to enter a fixed date.

Click the Run button and IQA will enter the @date command, which means

current date.

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Multiple Filter values

To search for multiple values, enclose in quotes and separate with commas

(e.g. area code equals “314”,”512”)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Avoid Excel Extract Security Warning

See this article for registry entry:http://support.microsoft.com/kb/948615

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Alternative Link TypesBeyond the Equals Join

• Left Join will include all records from Biz Obj on left CsContacts Left Join CsDonations will include

all contacts, even those who didn’t donate

• Not Exist will return records not contained in the other Biz Obj CsContacts Not Exist CsEventActivity will

return customers who did not register for a previous meeting

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Formatting date/time fieldsAnnoyed by the time showing in your XLS?

Update field in Business Object DesignerProperties > Display

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Adding value lists to Filters (1/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Adding value lists to Filters (2/2)

Update field in Business Object DesignerProperties > Values

NiUG Discove

ryOrlando, FL

April 16-18, 2012

1. Tools > Task Area (left) > 2. Utilities > User defined

tables3. Click Build All (to the far

right)

Create Business Objects for all User Defined Tables

NiUG Discove

ryOrlando, FL

April 16-18, 2012

IQAs can be Sources

Create very generic, frequently used IQAs and add as you would a Biz Obj.

Make sure to display at least one key field in the sub-IQA.

This is great for code

lookup tables. Many are

already built in. See the doc

system.

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Find the IQA being used by Public Views

Display IQAs on Public Views (1/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Display IQAs on Public Views (2/3)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Search on

this Public View page are identical to the search criteria defined in the IQA query on the previous slide

Display IQAs on Public Views (3/3)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Saving: Folders & naming

• Decide whether to store all IQAs inCustomers or spread out among individual iMIS modules

• Create and enforce an organizational folder (e.g. departmental, functional) and IQA naming strategy to easily find IQAs

• Create a policy among designers to make copies when they are significantly modifying an IQA – leave original for current uses

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Saving: Folders & naming

• Well thought-out folders & naming scheme give more context to IQA titles

• Use the description field when creating/updating for historical notes (unfortunately only visible to designers, not end users)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Previous Versions of Biz Objs

• You can revert to previous versions of Business Objects, but the versioning system is not yet available for IQAs In the Business Object Designer select Biz Obj, click

Versions, select desired version and click Revert. You will need to Compile and Publish the object again

NiUG Discove

ryOrlando, FL

April 16-18, 2012

New IQA Features in 15.1

• Totals, counts, subtotals

• Output to PDF• Output to SQL Server Reporting Services

(formatting, grouping, global formats) without leaving iMIS

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheets

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheet: ASI IQA Reference

http://docs.imis.com/15.2/iqaqueries.htm

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheet: Activity Table (1/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheet: Activity Table (2/2)

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheet: Addresses

http://docs.imis.com/15.2/commonaddressjoins.htm

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Cheat Sheet: Tables/Joins

http://docs.imis.com/15.2/index.htm?turl=commonreporttablesandjoins.htm

NiUG Discove

ryOrlando, FL

April 16-18, 2012

Support IQA!advsol.com > Support > Tech Support > Support Request