1 / 66

Argos - Moving into the Community

Argos - Moving into the Community. Presented by: Bruce Knox University of Arkansas Division of Agriculture, Cooperative Extension Service. May 1, 2007 2:15 pm. Introduction: Purpose and Benefits of this Presentation. Purpose: Discuss the Argos User Community

raina
Download Presentation

Argos - Moving into the Community

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Argos - Moving into the Community Presented by: Bruce Knox University of Arkansas Division of Agriculture, Cooperative Extension Service May 1, 2007 2:15 pm

  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 • What’s an Argos? • Trying the Product • The Argos Community • Converting from MS Access • Banner Record Selection Criteria

  4. What’s an Argos?

  5. What’s 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. What’s an Argos • MS Access++ Built for the SQL Database

  10. Trying the Product

  11. Get a password Trying the Product: 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. the Argos Sample Datablocks for SCT Banner Trying the Product: Download and install

  15. Trying the Product:

  16. Trying the Product:

  17. Trying the Product:

  18. Trying the Product:

  19. The Argos Community

  20. Community is a big part of Argos Sharing in Argos’ secured repository is encouraged. The Argos Community

  21. The Argos Community

  22. The Argos Community

  23. The Argos Community

  24. The Argos Community

  25. Converting from MS Access

  26. Converting from MS Access • We have been using MS Access with Banner for 7 years • How to move from our existing MS Access?

  27. Converting from MS Access • Build Datablocks via the Query Design GUI?

  28. Converting from MS Access • Enter the Oracle Code?

  29. Converting from MS Access • Either way will work for you, but • you still need to know the Banner Record Selection Criteria

  30. Banner Record Selection Criteria

  31. Determining which Banner Tables are actually used A Handy Find Query 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: Tables Actually Used

  34. Banner Record Selection Criteria: Tables Actually Used

  35. Banner Record Selection Criteria: Tables Actually Used • Collect the ones that look like Banner Tables into a file • argos_tables.txt

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

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

  38. -- **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

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

  40. Banner Record Selection Criteria: Reports • You will need a sample of the Report

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

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

  43. Banner Record Selection Criteria: Reports to Queries • MS Access Query behind the Report

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

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

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

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

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

More Related