DCC Schools Oracle Quick Guide Discoverer Viewer – Running Reports
General Instructions Relating To All Reports Step 1a – Establishing a connection A new connection must be set up the first time that Oracle Discoverer Viewer is used on a PC. A separate connection is required by each Oracle user. Start at Step 1b if this has already been done. a) b) c) d) e) f) g) h) i) j) k) l) Launch Internet Explorer Enter URL as https://fms.derby.gov.uk/discoverer/viewer Click on „Favorites‟ on menu bar or tool bar Click „Add to Favorites‟ on dropdown Amend name to „Oracle Discoverer‟ and click „OK‟ Click on „Create Connection‟ (moves to new screen) Select “Oracle Applications” in „Connect To‟ dropdown Enter name as „GL‟ Enter your usual Oracle User Name and Password Enter database as „PROD‟ Click „Apply and Connect‟ (moves to new screen) From the „Responsibility‟ dropdown select your General ledger responsibility – this should be named „DCC GL (Plus) Schools (your school name)‟ m) Click „Continue‟ (moves to new screen)
Step 1b - Connecting to Oracle Discoverer Viewer Once a connection has been successfully established. a) b) c) d) e) Open Internet Explorer Click on „Favorites‟ Select „Oracle Discoverer‟ (moves to new screen) Click on connection „GL‟ (moves to new screen) Enter your Oracle password and click „Go‟ (moves to new screen)
Version Date 13 May 2008
1
Step 2 – Creating a Report a) Click on the blue arrow next to report grouping you wish to select from, for instance „RMS03 Revenue Monitoring for schools.DIS‟ b) Choose the appropriate report from the sub-list now visible under your selected report grouping (moves to new screen) c) Enter the appropriate parameter details. All parameters must be completed; default parameters may be amended if necessary. Ensure you enter your school objective code with a capital 'E'. d) Click on „Go‟ (moves to new screen) e) Creation of the report will commence and the screen will refresh regularly until the results are displayed at the bottom of the parameter selection screen. You may be told that the report will take xx minutes to run and asked do you want to continue. Click Yes estimates are unreliable although summary reports could take 2 - 3 minutes. f) The report is displayed on screen, below the parameters you have just entered.
Step 3 - View report on screen a) Use scroll bars to move up/down and across the first page of data. b) Use up and down arrows at the foot of the screen to view additional pages of data (if any). c) Should the report not return the results you were expecting, you are able to change the parameters you entered. Click on „Go‟ to re-run the report. Some reports will allow you to select a subset of information based on the range of parameters you have entered. For instance, limit the report to one finance period where a range of periods have been selected. Above the report is a menu labelled „Page Items‟; choose the criteria you wish to change and select the appropriate choice from the dropdown list displayed. The report will then refresh to show only the transactions or balances relating to the revised criteria. To revert to the full report select „All‟ from the dropdown list.
Version Date 13 May 2008
2
Printing a report a) Click „Actions – Printable page‟ at top left of screen (moves to new screen) b) Click on „Page Setup‟ (moves to new screen) c) Ensure paper setup is set to „A4‟ and „landscape‟ d) Amend top and bottom margin settings to 0.2 inches each e) Amend left and right margin settings if advised under specific report instructions f) Set column widths. Suggested widths are described in Annex 1. g) If you want to check appearance prior to printing; i. Click „Preview sample‟ (moves to new screen) ii. Screen shows PDF file is being created (moves to new screen) iii. Click „Open PDF‟ to view sample (moves to new screen) iv. Check that report is only one page wide v. Note that only the first 50 lines of report will be shown on this sample report vi. If formatting is acceptable close PDF file using „x‟ at top right and proceed from step h to print report vii. If formatting not acceptable, close PDF file using „x‟ at top right, click on Print Options and continue from step 5d above by amending column widths as necessary h) If you do not need to check appearance prior to printing; i. Click „printable PDF button‟ (moves to new screen) ii. Screen shows PDF file is being created (moves to new screen) iii. Click on „click to view or save‟ PDF (moves to new screen) iv. Check that report is only one page wide v. If formatting is acceptable print report by clicking „Print‟ button on toolbar, then close PDF file using „x‟ at top right and then click Return to Worksheet vi. If formatting not acceptable, close PDF file using „x‟ at top right, click Return to worksheet and then repeat from step 5d above
Version Date 13 May 2008
3
Exporting a report to Excel Exporting a report to Excel has a number of advantages, as the data in the report can be easily manipulated by using Excel‟s many functions. Regular Excel users may also prefer to export a report to Excel prior to printing, as it‟s simpler to set column widths, delete unwanted data, and ensure the report prints out at the desired size. Click on „Export‟ at top left of screen (moves to new screen) Select „Microsoft Excel Workbook (*.xls)‟ from dropdown Click „Export‟ (moves to new screen) Click on „Click to view or save‟ (moves to new screen) The following screens will depend upon the version of Microsoft Excel installed on your computer. You may have an option to view or save the file. You may be asked if you wish to enable macros, in which case select 'enable'. You can then sort, subtotal or rearrange data as you would in any excel file. f) In Oracle Discoverer click on „return to worksheet‟ a) b) c) d) e)
Run further reports or Exit a) If you click on 'return to worksheet' you will return to the screen view of the report and can then adjust the page header selections and run another report without losing the printing column widths that are currently set. b) When all reports are run click on Exit (top right of screen), answer 'no' to saving changes and then close Internet Explorer.
Version Date 13 May 2008
4
Specific Instructions For Particular Reports
RMS03 Analysis Codes This is set to allow you to run the report for a range of analysis codes and then enable you to select an individual analysis code from the dropdown list in the page header. An optional tab has been added to the report allow schools to export budget data in a format for specific use with the schools forecast spreadsheet. An RMS03 report may now be run for a range of cost centres.
GLT12 Actuals If you have selected more than one period in the parameters the currently displayed period will be shown on page header, just above the main body of the report. Click on this and change the period if necessary. The report will then refresh to show only the transactions relating to the new period. To produce a report showing the values arising from the accounts payable or accounts receivable sub-ledgers in the period select 'payables' or 'receivables' from the 'source' list shown in the page header. The resulting list of transactions is displayed in GL posting date order and can then be compared to the GLT10 or GLT11 report. See under those reports for more details on reconciliation. An optional tab has been added to the report allow schools to export transaction data in a format for specific use with the schools forecast spreadsheet GLT10 GL Details (AP Sub-ledger) and GLT11 Print Version (AR Sub-ledger) To print these reports amend the page setting margins to 0.2 inches for both left and right margins in order to fit the report on one page width and minimise number of pages used. If you are reconciling to a GLT12 'payables' or „receivables‟ report enter the 'Start Date' and 'End Date' according to the start and end dates of the period selected for the GLT12 report. These dates are listed on the
Version Date 13 May 2008 5
Oracle for All Users page of the Derby City Council website (www.derby.gov.uk/EducationLearning/Finance/Oracle+for+All+Users) Note that the General Ledger posting date shown on the GLT12 General Ledger report is normally one day later than the Accounting Date shown on the GLT10 Accounts Payable report. In some circumstances it is possible that the General Ledger Posting Date could be some days later if there has been a problem with the transaction. However, the period cannot be closed in general ledger until all transactions in Accounts Payable with an accounting date on or before the last day of the period have been posted or altered. If the reports do not reconcile check if the period was closed before the general ledger report was run.
Version Date 13 May 2008
6
Annex 1
Report column widths (suggested)
RMS03 Summary and RMS03 Analysis Codes
Annual Budget Income/Exp Subjective Group Column size (Inches) Subjective Subjective Description Budget To Date Actual To Date Variance To Date Commit Annual Budget Available
1.1
1
0.6
2.4
0.7
0.7
0.7
0.7
0.7
0.7
RMS03 Cost Centre Summary
Annual Budget Cost Centre Column size (Inches) Cost Income/Exp Subjective Subjective Subjective Centre Group Description Description Budget Actual Variance Commit To Date To Date To Date Annual Budget Available
0.6
0.8
0.8
0.8
0.8
0.6
2.7
0.7
0.7
0.7
0.7
0.7
RMS03 Summary inc Analysis
Ann Budget TD Actual to Budget CURR Date CURR Inc/Exp Subjective Subjective Subjective Group Description Variance To Date Commitme Annual nts Budget Available CURR
Column size (Inches)
1.1
0.9
0.6
1.5
0.7
0.7
0.7
0.7
0.7
0.7
CFR01 Summary – Print
Annual Budget Income/Exp CFR Category Column size (Inches) Budget To Date Actual To Date Variance To Date Commit Annual Budget Available
1.6
3.4
0.8
0.8
0.8
0.8
0.8
0.8
Version Date 13 May 2008
7
GLT10 GL Details (Accounts Payable Transactions)
Line Value Subjective Analysis Accounting Supplier Supplier Supplier Code Date No Name Site Code Invoice Date Invoice No Voucher Line No Line Number Description
Column size (Inches)
0.6
0.8
0.8
0.6
1.7
0.7
0.8
1.2
0.6
0.3
1.5
0.7
GLT11 Print Version (Accounts Receivable Transactions)
Net Amount Subjective Analysis Customer Customer Invoice Number Name Date Invoice No Transaction Line No Line GL Date Type Description
Column size (Inches)
0.6
0.8
0.8
2
0.8
1
1
0.3
1.8
0.8
0.7
GLT12 Actuals (General Ledger Transactions)
Net Amount Subjective Subjective Description Analysis Analysis Code Description Source Category Doc No Created By Posted Line Description
Column size (Inches)
0.7
1.4
0.7
1
0.6
0.6
0.9
0.7
0.8
3
0.7
Version Date 13 May 2008
8