Procedure for downloading Banner screen data to Excel

1
Procedure for downloading Banner screen data to
Excel
2
Introduction

• Allows data from Banner screens to be captured in
  Excel
• Allows customized sorts, groups and sums
• To strengthen Excel skills, classes are available
  through ITC

3
Banner screens

• This feature is available in finance with the
  following screens
• FGIBDST - Budget Status form
• FGITRND - Transaction Detail form
• NHIDIST - Labor Distribution form
• FGITBSR - Trial Balance Summary form
• FGIGLAC - General Ledger Activity form

4
Select Banner data

• Start by selecting data in Banner to be
  downloaded into Excel
• Click Help on Menu bar
• Click Extract data with key or .no key
• Key refers to header information
• Creates Excel spreadsheet with all data selected,
  not just data visible on the Banner screen
• Format for personal requirements
• Must have Temp folder in C drive.

5
Represents data to be downloaded to excel.
6
FGITRND after Help is selected on the menu bar.
Next select Extract data no Key.
7
Open the Temp folder in the C drive and select
the file just created.
8

• Select all data by clicking the box in the upper
  left hand corner of the data sheet above the 1
  and to the left of the A.

9

• Sort the data by the field that you want to have
  a sum calculated for each change in value, ex
  Sum for each change in account
• Select Data then click on Sort

10

• After clicking on sort choose the column to sort
  ex A
• If you do not sort the field being grouped you
  may return incorrect results

11

• Select row 1, click Insert on the menu bar
  and select rows
• Designates row 1 as the title row

12

• Select all data by clicking the box in the upper
  left corner of the spreadsheet, above the 1 and
  to the left of the A

13
After selecting all data, click data on the
menu bar, then select subtotals.
14

• When the pictured message box appears click OK.
• This designates the first row as the label row.

15

• At each change in click on the drop down box
  and select the column to sort on. Ex column A if
  you want to sort on account.
• At Use function click on the drop down box and
  select sum. This will sum all rows for each
  different value in the column you are sorting on.
• At Add subtotal to click on the dropdown box
  and check the column you want to sum (the amount
  column).

16
(No Transcript)
17

• After clicking OK you will see results similar
  to the following.

18

• Below is a Microsoft Excel Help screen
  explaining the how to use the Outline Symbols
  at the left of the spreadsheet.
• This feature allows you to hide detail

19

• This is the Excel worksheet after clicking on the
  2 Outline Symbol

20
Summary

• Run query in Banner
• Download to c/temp file
• Sort by column to be grouped on
• Create new row at top of the spreadsheet
• Select all rows and columns of data
• Click Data", "Subtotals
• Select fields to be grouped on and summed
• Click OK
• Use Outline Symbols to hide unwanted detail