Argos - Moving into the Community

1
Argos - Moving into the Community

• Presented by Bruce Knox
• University of Arkansas
• Division of Agriculture, Cooperative Extension
  Service

BAS S274 October 13, 2008

2
Introduction Purpose and Benefits of this
Presentation

• Purpose
• Discuss the Argos User Community
• Converting from MS Access to Argos
• Benefits
• Free Software
• Conversion Tips
• Tools for Ad Hoc Banner Reporting

3
Our Agenda

• Whats an Argos?
• Trying the Product
• The Argos Community
• Converting from MS Access
• Banner Record Selection Criteria

4
Whats an Argos?
5
Whats an Argos?

• Argos is a Web Based
• Evisions
• Ad Hoc Reporting Tool
• Designed for Banner

6
A.R.G.O.S. Ad hoc Report Generation and Output
Solution

• Secure
• Easy to Use
• Fast

7
Argos, in Design Mode looks a lot like MS Access
8
Argos, in Design Mode looks a lot like MS Access
9
Whats an Argos

• MS Access Built for the SQL Database

10
Trying the Product
11
Trying the Product Get a Password

• Get a password

12
Trying the Product Download and Install
13
Trying the Product DBA and SysAdmin

• You will need IT for early parts of the Server
  Install

14
Trying the Product Download and install

• the Argos Sample Datablocks for SCT Banner

15
Trying the Product
16
Trying the Product
17
Trying the Product
18
Trying the Product
19
Trying the Product
20
The Argos Community
21
The Argos Community

• Community is a big part of Argos
• Sharing in Argos secured repository is
  encouraged.

22
The Argos Community
23
The Argos Community
24
The Argos Community
25
The Argos Community
26
Converting from MS Access
27
Converting from MS Access

• We have been using MS Access with Banner for 8
  years
• How to move from our existing MS Access?

28
Converting from MS Access

• Build Datablocks via the Query Design GUI?

29
Converting from MS Access

• Enter the Oracle Code?

30
Converting from MS Access

• Either way will work for you, but
• you still need to know the Banner Record
  Selection Criteria

31
Banner Record Selection Criteria
32
Banner Record Selection Criteria

• Determining which Banner Tables are actually used
• A Handy Find Query

33
Banner Record Selection Criteria

• Determining which Banner Tables are Actually Used
• A Handy Find Query

34
Banner Record Selection Criteria Tables Actually
Used
35
Banner Record Selection Criteria Tables Actually
Used
36
Banner Record Selection Criteria Tables Actually
Used

• Collect the ones that look like Banner Tables
  into a file
• argos_tables.txt

37
Banner Record Selection Criteria Tables Actually
Used

• argos_tables.txt
• containing
• _at_table_to_argos FABBKTP
• _at_table_to_argos FABCHKA
• _at_table_to_argos FABCHKS
• _at_table_to_argos FABINCK
• _at_table_to_argos FABINVH
• _at_table_to_argos FARDIRD
• _at_table_to_argos FARINTX
• _at_table_to_argos TURVERS

38
Banner Record Selection Criteria Tables Actually
Used

• Then in SQLPlus
• 103239 BKNOX PRODgt START argostables.txt
• This runs table_to_argos.sql for each Table in
  the file.
• The script concatenates each result into a single
  text file containing the Table information
  required for constructing Queries or determining
  Record Selection Criteria.

39
Banner Record Selection Criteria Here is a
snippet

• -- FTVORGN Organization Validation Table
• SELECT -- Created from TABLE FTVORGN
  Organization Validation Table
• FTVORGN_COAS_CODE,
• FTVORGN_ORGN_CODE,
• TRUNC(FTVORGN_EFF_DATE)
  FTVORGN_EFF_DATE,
• TRUNC(FTVORGN_ACTIVITY_DATE)
  FTVORGN_ACTIVITY_DATE,
• FTVORGN_USER_ID,
• TRUNC(FTVORGN_NCHG_DATE)
  FTVORGN_NCHG_DATE,
• TRUNC(FTVORGN_TERM_DATE)
  FTVORGN_TERM_DATE,
• FTVORGN_TITLE,
• FTVORGN_STATUS_IND,
• FTVORGN_ORGN_CODE_PRED,
• FTVORGN_FUND_CODE_DEF,
• FTVORGN_PROG_CODE_DEF,
• FTVORGN_ACTV_CODE_DEF,
• FTVORGN_LOCN_CODE_DEF,
• FTVORGN_DATA_ENTRY_IND,
• FTVORGN_FMGR_CODE_PIDM,
• FTVORGN_ENCB_POLICY_IND,

40
Banner Record Selection Criteria Here is a
snippet part2

• -- AND FTVORGN_STATUS_IND '?'
• -- AND FTVORGN_DATA_ENTRY_IND '?'
• -- AND FTVORGN_ENCB_POLICY_IND '?'
• -- AND FTVORGN_HIERARCHY_TABLE_IND '?'
• -- AND FTVORGN_ALT_POOL_IND '?'
• -- AND FTVORGN_ACTIVITY_DATE gt
  TO_DATE('06/30/2006 000000','MM/DD/YYYY
  HH24MISS')
• -- AND TRUNC(FTVORGN_ACTIVITY_DATE) gt
  TO_DATE('01/26/2007 000000','MM/DD/YYYY
  HH24MISS')
• -- AND TRUNC(FTVORGN_ACTIVITY_DATE)
  TO_DATE('01/26/2007 000000','MM/DD/YYYY
  HH24MISS')
• -- AND TRUNC(FTVORGN_ACTIVITY_DATE) lt
  TO_DATE('01/26/2007 000000','MM/DD/YYYY
  HH24MISS')
• -- AND TRUNC(FTVORGN_ACTIVITY_DATE) BETWEEN
  TO_DATE('07/01/2006 000000','MM/DD/YYYY
  HH24MISS') AND TO_DATE('06/30/2007
  235959','MM/DD/YYYY HH24MISS')'
• --FTVORGN_COAS_CODE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_ORGN_CODE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_EFF_DATE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_ACTIVITY_DATE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_USER_ID NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_NCHG_DATE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_TITLE NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_STATUS_IND NOT NULLABLE is
  Probably Key or Indexed Field
• --FTVORGN_DATA_ENTRY_IND NOT NULLABLE is
  Probably Key or Indexed Field

41
Banner Record Selection Criteria Reports

• You will need a sample of the Report

42
Banner Record Selection Criteria Reports to
Queries

• Double-Click the Report Selector
• The Dark Square within the Gray Square left of
  the Ruler Line.
• Alternately, Right-Click and Select Properties,
  then
• Select Data.

43
Banner Record Selection Criteria Reports to
Queries

• Record Source is the MS Access Query behind the
  Report
• Double-Click on the Ellipsis button and you have
  the Query

44
Banner Record Selection Criteria Reports to
Queries

• MS Access Query behind the Report

45
Banner Record Selection Criteria Reports to
Queries

• MS Access Query behind the Report

46
Banner Record Selection Criteria Reports to
Queries

• MS Access Query behind the Report

47
Banner Record Selection Criteria Reports to
Queries

• SELECT
• IIf(FGBGENL_FUND_CODE"11100","U",
• IIf(FGBGENL_FUND_CODE"11200","U",
• IIf(FGBGENL_FUND_CODE"14000","U",
• IIf(FGBGENL_FUND_CODE"21110","U",
• IIf(FGBGENL_FUND_CODE"21120","U",
• IIf(FGBGENL_FUND_CODE"21160","U",
• IIf(FGBGENL_FUND_CODE Between "13000" And
  "13199","U",
• IIf(FGBGENL_FUND_CODE Between "13250" And
  "13999","U",
• IIf(FGBGENL_FUND_CODE Between "22000" And
  "22999","U",
• IIf(FGBGENL_FUND_CODE Between "24000" And
  "24999","U",
• IIf(FGBGENL_FUND_CODE Between "26000" And
  "26999","U",
• IIf(FGBGENL_FUND_CODE Between "29000" And
  "29999","U",
• IIf(FGBGENL_FUND_CODE"27000","U",
• IIf(FGBGENL_FUND_CODEgt"30000","P","R")))))))))))
  ))) AS Group,
• FGBGENL 04.FGBGENL_ACCT_CODE AS GLACCT,
• FTVACCT.FTVACCT_TITLE AS ACCT TITLE,
• FTVACCT.FTVACCT_ATYP_CODE AS ACCT TYPE,
• FTVATYP.FTVATYP_TITLE AS ACCT TYPE TITLE,

48
Banner Record Selection Criteria Reports to
Queries

• But, note that this code is MS Access SQL Not,
  Oracle SQL.
• Group
• IIf(FGBGENL_FUND_CODE"11100","U",
• IIf(FGBGENL_FUND_CODE"11200","U",
• IIf(FGBGENL_FUND_CODE"14000","U",
• IIf(FGBGENL_FUND_CODE"21110","U",
• IIf(FGBGENL_FUND_CODE"21120","U",
• IIf(FGBGENL_FUND_CODE"21160","U",
• IIf(FGBGENL_FUND_CODE Between "13000" And
  "13199","U",
• IIf(FGBGENL_FUND_CODE Between "13250" And
  "13999","U",
• IIf(FGBGENL_FUND_CODE Between "22000" And
  "22999","U",
• IIf(FGBGENL_FUND_CODE Between "24000" And
  "24999","U",
• IIf(FGBGENL_FUND_CODE Between "26000" And
  "26999","U",
• IIf(FGBGENL_FUND_CODE Between "29000" And
  "29999","U",
• IIf(FGBGENL_FUND_CODE"27000","U",
• IIf(FGBGENL_FUND_CODEgt"30000","P",
• "R"))))))))))))))

49
Banner Record Selection Criteria Reports to
Queries

• CASE
• WHEN FGBGENL_FUND_CODE '11100' THEN 'U'
• WHEN FGBGENL_FUND_CODE '11200' THEN 'U'
• WHEN FGBGENL_FUND_CODE '14000' THEN 'U'
• WHEN FGBGENL_FUND_CODE '21110' THEN 'U'
• WHEN FGBGENL_FUND_CODE '21120' THEN 'U'
• WHEN FGBGENL_FUND_CODE '21160' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '13000' And
  '13199' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '13250' And
  '13999' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '22000' And
  '22999' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '24000' And
  '24999' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '26000' And
  '26999' THEN 'U'
• WHEN FGBGENL_FUND_CODE Between '29000' And
  '29999' THEN 'U'
• WHEN FGBGENL_FUND_CODE '27000' THEN 'U'
• WHEN FGBGENL_FUND_CODE gt '30000' THEN 'P'
• ELSE 'R'
• END AS FUNDGROUP

50
Banner Record Selection Criteria Reports to
Queries

• The FROM and WHERE need to lose the and
  Double Quotes.
• FROM ((FGBGENL
• LEFT JOIN (FTVACCT INNER JOIN FTVATYP ON
  FTVACCT.FTVACCT_ATYP_CODE FTVATYP.FTVATYP_ATYP_C
  ODE)
• ON FGBGENL.FGBGENL_ACCT_CODE
  FTVACCT.FTVACCT_ACCT_CODE)
• LEFT JOIN FTVFUND ON FGBGENL.FGBGENL_FUND_CODE
  FTVFUND.FTVFUND_FUND_CODE) INNER JOIN
  FTVFTYP
• ON FTVFUND.FTVFUND_FTYP_CODE
  FTVFTYP.FTVFTYP_FTYP_CODE
• WHERE (((FGBGENL.FGBGENL_PERIOD)ltSelect a
  period (pp))
• AND ((FTVFUND.FTVFUND_FTYP_CODE) Not Like
  "BF"))
• FROM ((FGBGENL
• LEFT JOIN (FTVACCT INNER JOIN FTVATYP ON
  FTVACCT.FTVACCT_ATYP_CODE FTVATYP.FTVATYP_ATYP_C
  ODE)
• ON
  FGBGENL.FGBGENL_ACCT_CODE FTVACCT.FTVACCT_ACCT_C
  ODE)
• LEFT JOIN FTVFUND ON FGBGENL.FGBGENL_FUND_CODE
  FTVFUND.FTVFUND_FUND_CODE) INNER JOIN FTVFTYP
• ON FTVFUND.FTVFUND_FTYP_CODE FTVFTYP.FTVFTYP_FTY
  P_CODE
• WHERE FGBGENL.FGBGENL_PERIODltpp
• AND FTVFUND.FTVFUND_FTYP_CODE Not Like 'BF'

51
Banner Record Selection Criteria Reports to
Queries

• That works!
• And it ran much, much faster than my old style
  Oracle SQL.
• FROM ((FGBGENL
• LEFT JOIN (FTVACCT INNER JOIN FTVATYP ON
  FTVACCT.FTVACCT_ATYP_CODE FTVATYP.FTVATYP_ATYP_C
  ODE)
• ON
  FGBGENL.FGBGENL_ACCT_CODE FTVACCT.FTVACCT_ACCT_C
  ODE)
• LEFT JOIN FTVFUND ON FGBGENL.FGBGENL_FUND_CODE
  FTVFUND.FTVFUND_FUND_CODE) INNER JOIN FTVFTYP
• ON FTVFUND.FTVFUND_FTYP_CODE FTVFTYP.FTVFTYP_FTY
  P_CODE
• WHERE FGBGENL.FGBGENL_PERIODlt'06'
• AND FGBGENL_FSYR_CODE '06'
• AND FTVFUND.FTVFUND_FTYP_CODE Not Like 'BF'
• AND TRUNC(FTVACCT_EFF_DATE) lt SYSDATE
• AND (FTVACCT_NCHG_DATE gt TRUNC(SYSDATE)
  OR FTVACCT_NCHG_DATE IS NULL)
• AND (FTVACCT_TERM_DATE gt TRUNC(SYSDATE)
  OR FTVACCT_TERM_DATE IS NULL)
• AND TRUNC(FTVATYP_EFF_DATE) lt SYSDATE
• AND (FTVATYP_NCHG_DATE gt TRUNC(SYSDATE)
  OR FTVATYP_NCHG_DATE IS NULL)
• AND (FTVATYP_TERM_DATE gt TRUNC(SYSDATE)
  OR FTVATYP_TERM_DATE IS NULL)

52
Banner Record Selection Criteria Reports to
Queries

• Paste it into Free Type and Run It

53
Banner Record Selection Criteria Reports to
Queries

• And it works!

54
Banner Record Selection Criteria Reports to
Queries

• And it works!
• But, this MS Access Query was mostly Oracle Joins
• Converting the MS Access Functions is a Challenge
• access_to_argos.shl UNIX Shell Script Can Help

55
Banner Record Selection Criteria Reports to
Queries

• access_to_argos.shl include conversions for the
  most common MS Access Functions plus some hints
  on the complex ones
• Automated Conversion includesremoves all
  removes all  changes Double Quotes to Single
  QuotesInserts the CR, Carriage Returns or
  Newlines is just to help visually format the code
  to make it more readable, IMO   Iff
  inserts CR in front of IIf   FROM CR after
  FROM   WHERE CR after WHERE   HAVING CR after
  HAVINGchanges   Chr( to CHAR(   Len(
  LENGTH(   Now() SYSDATE   Nz( NVL(  
  UCase( UPPER(   LCase( LOWER(and since  I
  use Upper Case for Oracle Keywords   Abs(
  to ABS(   LTrim( LTRIM(   RTrim( RTRIM(  
  Trim( TRIM(   Round( ROUND(The following
  are too complex for sed and tr to convert, but
  here are some hints on making the changes  
  Left(AnyString, n) SUBSTR(AnyString,1,n)  
  Right(AnyString, n) SUBSTR(AnyString,LENGTH(AnyStr
  ing)-n1,n)   IIf Can be replaced with CASE or
  DECODE (use CASE if any IIf ... Between ...
  used)Warning One must be careful that the
  input contains no Lower Case Selection Criteria,
  because the output is UPPER Case

56
Banner Record Selection Criteria Reports to
Queries

• You can build the Query using the
• COLUMNs, Tables, WHERE, GROUP BY, ORDER
  BY, and HAVING
• Or, Build the Query from your code.
• Either will allow you to use parameters.
• Another option would be to build an Oracle VIEW
  from the code, but you would probably need a lot
  more help from IT to do that.

57
Argos Training
58
Argos Training Traditional, Live and Recorded

• Options to match your needs

59
Argos Training Recorded

• Recorded Training for End-User, Designer,
  Administrator

60
Argos Training Recorded

• Recorded Training for End-User

61
Argos Training Recorded

• Recorded Training for Designer and Administrator

62
More Argos Resources

• Typical Calendar of Live Classes

63
More Argos Resources

• Knowledge Base, Listserv, Help Desk,

64
More Argos Resources

• http//www.evisions.com/products/argos/index.asp
  Argos
• http//www.uaex.edu/bknox/BannerArgos.htm
  BannerArgos

65
Summary

• Argos is the Web Based Ad Hoc Reporting Tool for
  Banner
• Download and Try it Now!
• Go to the Argos Community for Free Code and Help

66
Questions Answers

• Questions?

67

• Thank You! And, thanks to the Tennessee Board
  of Regents (TBR) and the Middle Tennessee State
  University
• Bruce Knox
• bknox _at_t uaex.edu
• http//www.uaex.edu/bknox/BannerArgos.htm