argos moving into the community l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Argos - Moving into the Community PowerPoint Presentation
Download Presentation
Argos - Moving into the Community

Loading in 2 Seconds...

play fullscreen
1 / 67

Argos - Moving into the Community - PowerPoint PPT Presentation


  • 265 Views
  • Uploaded on

Argos - Moving into the Community Presented by: Bruce Knox University of Arkansas Division of Agriculture, Cooperative Extension Service BAS S274 October 13, 2008 Introduction: Purpose and Benefits of this Presentation Purpose: Discuss the Argos User Community

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Argos - Moving into the Community' - issac


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
argos moving into the community

Argos - Moving into the Community

Presented by: Bruce Knox

University of Arkansas

Division of Agriculture, Cooperative Extension Service

BAS S274 October 13, 2008

introduction purpose and benefits of this presentation
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
our agenda
Our Agenda
  • What’s an Argos?
  • Trying the Product
  • The Argos Community
  • Converting from MS Access
  • Banner Record Selection Criteria
what s an argos5
What’s an Argos?
  • Argos is a Web Based
  • Evisions
  • Ad Hoc Reporting Tool
  • Designed for Banner
what s an argos9
What’s an Argos
  • MS Access++ Built for the SQL Database
trying the product dba and sysadmin
Trying the Product: DBA and SysAdmin
  • You will need IT for early parts of the Server Install
the argos community21
Community is a big part of Argos

Sharing in Argos’ secured repository is encouraged.

The Argos Community
converting from ms access27
Converting from MS Access
  • We have been using MS Access with Banner for 8 years
  • How to move from our existing MS Access?
converting from ms access28
Converting from MS Access
  • Build Datablocks via the Query Design GUI?
converting from ms access29
Converting from MS Access
  • Enter the Oracle Code?
converting from ms access30
Converting from MS Access
  • Either way will work for you, but
  • you still need to know the Banner Record Selection Criteria
banner record selection criteria33
Banner Record Selection Criteria
  • Determining which Banner Tables are Actually Used
  • A Handy Find Query
banner record selection criteria tables actually used36
Banner Record Selection Criteria: Tables Actually Used
  • Collect the ones that look like Banner Tables into a file
  • argos_tables.txt
banner record selection criteria tables actually used37
Banner Record Selection Criteria: Tables Actually Used
  • argos_tables.txt
  • containing:

@table_to_argos FABBKTP

@table_to_argos FABCHKA

@table_to_argos FABCHKS

@table_to_argos FABINCK

@table_to_argos FABINVH

@table_to_argos FARDIRD

@table_to_argos FARINTX

@table_to_argos TURVERS

banner record selection criteria tables actually used38
Banner Record Selection Criteria: Tables Actually Used
  • Then in SQL*Plus:

10:32:39 BKNOX: PROD> 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.
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,

FTVORGN_ORGN_CODE_NSF,

FTVORGN_HIERARCHY_TABLE_IND,

FTVORGN_ALT_POOL_IND

FROM FTVORGN

WHERE

TRUNC(FTVORGN_EFF_DATE) <= SYSDATE

AND (FTVORGN_NCHG_DATE > TRUNC(SYSDATE) OR FTVORGN_NCHG_DATE IS NULL)

AND (FTVORGN_TERM_DATE > TRUNC(SYSDATE) OR FTVORGN_TERM_DATE IS NULL)

Banner Record Selection Criteria: Here is a snippet
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 > TO_DATE('06/30/2006 00:00:00','MM/DD/YYYY HH24:MI:SS')

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) >= TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) = TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) < TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) BETWEEN TO_DATE('07/01/2006 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('06/30/2007 23:59:59','MM/DD/YYYY HH24:MI:SS')'

--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

--ORDER BY FTVORGN_ORGN_CODE

-- **FTVORGN** Organization Validation Table

Banner Record Selection Criteria: Here is a snippet part2
banner record selection criteria reports
Banner Record Selection Criteria: Reports
  • You will need a sample of the Report
banner record selection criteria reports to queries
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.
banner record selection criteria reports to queries43
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
banner record selection criteria reports to queries47
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_CODE]>"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],

Sum([FGBGENL_SUM_PERIODIC_DR]-[FGBGENL_SUM_PERIODIC_CR]) AS Balance

FROM

(([FGBGENL 04] LEFT JOIN

(FTVACCT LEFT JOIN

FTVATYP ON FTVACCT.FTVACCT_ATYP_CODE = FTVATYP.FTVATYP_ATYP_CODE) ON [FGBGENL 04].FGBGENL_ACCT_CODE = FTVACCT.FTVACCT_ACCT_CODE)

LEFT JOIN [FTVFUND any status] ON [FGBGENL 04].FGBGENL_FUND_CODE = [FTVFUND any status].FTVFUND_FUND_CODE)

LEFT JOIN FTVFTYP ON [FTVFUND any status].FTVFUND_FTYP_CODE = FTVFTYP.FTVFTYP_FTYP_CODE

WHERE

((([FGBGENL 04].FGBGENL_PERIOD)<=[Select a period (pp)]) AND (([FTVFUND any status].FTVFUND_FTYP_CODE) Not Like "BF"))

GROUP BY 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_CODE]>"30000","P","R")))))))))))))), [FGBGENL 04].FGBGENL_ACCT_CODE, FTVACCT.FTVACCT_TITLE, FTVACCT.FTVACCT_ATYP_CODE, FTVATYP.FTVATYP_TITLE

HAVING (((Sum([FGBGENL_SUM_PERIODIC_DR]-[FGBGENL_SUM_PERIODIC_CR]))<>0))

ORDER BY 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_CODE]>"30000","P","R")))))))))))))) DESC ,

[FGBGENL 04].FGBGENL_ACCT_CODE;

Banner Record Selection Criteria: Reports to Queries
banner record selection criteria reports to queries48
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_CODE]>"30000","P",

"R"))))))))))))))

Banner Record Selection Criteria: Reports to Queries
banner record selection criteria reports to queries49
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 > '30000' THEN 'P'

ELSE 'R'

END AS FUNDGROUP

Banner Record Selection Criteria: Reports to Queries
banner record selection criteria reports to queries50
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_CODE)

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)<=[Select 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_CODE)

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<=‘&pp‘

AND FTVFUND.FTVFUND_FTYP_CODE Not Like 'BF'

Banner Record Selection Criteria: Reports to Queries
banner record selection criteria reports to queries51
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_CODE)

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<='06'

AND FGBGENL_FSYR_CODE = '06'

AND FTVFUND.FTVFUND_FTYP_CODE Not Like 'BF'

AND TRUNC(FTVACCT_EFF_DATE) <= SYSDATE

AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL)

AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL)

AND TRUNC(FTVATYP_EFF_DATE) <= SYSDATE

AND (FTVATYP_NCHG_DATE > TRUNC(SYSDATE) OR FTVATYP_NCHG_DATE IS NULL)

AND (FTVATYP_TERM_DATE > TRUNC(SYSDATE) OR FTVATYP_TERM_DATE IS NULL)

Banner Record Selection Criteria: Reports to Queries
banner record selection criteria reports to queries52
Banner Record Selection Criteria: Reports to Queries
  • Paste it into “Free Type” and Run It
banner record selection criteria reports to queries54
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
banner record selection criteria reports to queries55
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 includes:removes 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(AnyString)-n+1,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

banner record selection criteria reports to queries56
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.
more argos resources
More Argos Resources

Typical Calendar of Live Classes

more argos resources63
More Argos Resources

Knowledge Base, Listserv, Help Desk, …

more argos resources64
More Argos Resources

http://www.evisions.com/products/argos/index.asp Argos

http://www.uaex.edu/bknox/BannerArgos.htm BannerArgos

summary
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

slide67
Thank You! And, thanks to the Tennessee Board of Regents (TBR) and the Middle Tennessee State University

Bruce Knox

bknox @t uaex.edu

http://www.uaex.edu/bknox/BannerArgos.htm