Penn Computing


Penn Computing


Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

Download and Reformat Your Telecommunications Statement

Return to Reviewing Phone Location Information.

1. Access your statement in Ben Reports and download it to your workstation.

  • Select ISC Billing Reports from the Ben Reports home page.
  • Select ISC Telecommunications Detail from the ISC Billing Reports page.
  • Enter query options.
    • We recommend viewing a single org at a time. To do this, type in the same org number in both the From Org and To Org fields.
    • Enter the dates for your last full month's charges. For example, if today is June 15, 2006, your last full month's statement would be from May 1, 2006 to May 31, 2006.
  • Click the Run Report button to see the results of your query.
  • Scroll to the bottom of your report page, and click the Download full results to Excel link.
  • A dialog box will open that tells you its downloading a "show" file. Select the Save to File option, and click the Okay button.
  • Once the file downloads, open it in Excel. The file downloads to a SYLK format. Save it as an Excel spreadsheet in order to keep working in the file.
    • From the File menu, select Save As. In the dialog box that opens, change the Save as Type field to Microsoft Excel Workbook (or some variation of that). You may also want to rename the file to something you'll more easily be able to find later (for example, "9109161 Telephone Bill"). Click the Save button.
    • Now you've got a copy of your latest Telecommunications bill saved on your computer. Feel free to close Ben Reports at this point.

2. Make formatting changes to the downloaded spreadsheet.

  • Delete row 2, the row that is blank across the entire spreadsheet.
  • Delete columns 1 (which is blank) and 2 (which is info used by Ben Reports). Also delete the column at the far right of the spreadsheet titled End Bill Date.
  • Select the column headings across the top of the spreadsheet starting with Invoice Date and ending with Start Bill Date. Change the format of these cells to bold. This will let Excel know that these are column headers rather than data.
  • Click on any cell within the data. Select Sort from the Data menu. In the Sort dialog box, sort by the Tran Code Desc and the Item fields. Like this:
    Sort Dialog View
  • Click the OK button. If a dialog box asks you how to handle numbers formatted as text, select the "sort anything that looks like a number, as a number" option.
  • Hide the columns you won't need during your review. The columns you do need are:
    • Item - Usually a telephone number, although other items may be listed as well.
    • Building Id - A three character code that refers to a building on campus.
    • Location - Room or floor information.
    • Desc 1 - Description of the service charge.
    • Tran Code Desc - Type of charge.
    • Start Bill Date - The date on which this service was first charged to this budget code.
  • To hide the other columns, select them (either one at a time or in a group). Then from the Format menu, Select Column -> Hide.
  • Finally, add two colums after the Start Bill Date field titled "Action" and "Info to Change" . These are the fields you'll use to track corrections to be made.

3. Identify the line and location information for review.

The records you will use to review line location information are labeled "EQUIPMENT COST" in the Tran Code Desc field. For now, just ignore records with any other description in this field. You may want to cut the other records from this spreadsheet and paste them elsewhere if they seem in the way.

 

Page updated on September 21, 2009



top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


Penn Computing University of Pennsylvania
Information Systems and Computing, University of Pennsylvania