Important! You will need access to your departmental BAIRS report to create this analysis spreadsheet. If you do not have access, please contact your departmental financial officer for access rights or have them run you a BAIRS report to use.
LINE USAGE KB ARTICLE
With these instructions and a fundamental knowledge of excel spreadsheets, you will be able to create a Line usage, Line feature, and Line cost analysis report from your BAIRS billing statement that is simple to read and understand. This will assist you in managing your Telecom Services better, so as to make more informed decisions about your services.
Any questions: email ISTbill@berkeley.edu
Step (1.)
Part (1.) CREATING THE BAIRS EXCEL SHEET for the USAGE REPORT
-
Using your CalNet ID, login to BAIRS https://citrix.berkeley.edu/Citrix/vAppsWeb/
-
Click on Explore
-
Click on Financial Reports
-
Click on the “IST Billing Folder”
-
Click on “IST Phone Usage Detail”
-
Using Control-Click, pick last 3 months of usage on menu (within same fiscal year).
-
On the menu leave the account box data “as is”
-
On the menu click the “ignore” on the Fund Box.
-
On the menu in the Department ID Box, click the drop-down menu and select “Org Node Level”
-
On the menu in the Department ID Box, click the department level“L4 Department”
-
This will open a alphabetical drop-down menu on the right hand side of the Department ID Box… in this menu select your department by L4 department code (example: VRIST)
-
On the menu click the “ignore” on the Chartfield 1 Box.
-
On the menu click the “ignore” on the Chartfield 2 Box.
-
Now click “Run Report”
-
After the report is up, click view.
-
After clicking view, click on view pane.
-
On the left hand menu below that has come up from clicking “view pane,” click on “Results”
-
Then on the ribbon menu, click on the Excel Icon with the “07” underneath it.
-
Click on Open,
this opens the BAIRS Report in an Excel Sheet.
-
On the excel file that your last command created, select the entire screen and do a “copy all” on the BAIRS provided excel report.
-
Open a blank page in your Excel App in your PC and name it “My Department’s BAIRS Review”
-
Save this sheet to a new Excel page within your own PC and rename TAB as “USAGE.”
-
Name and Save this excel spreadsheet into your own file on your own PC.
You now have a excel spreadsheet with all the information on it that you will need to complete the LINE USAGE REPORT, without having to constantly go back and forth to your BAIRS FILE for needed information.
Step (2.)
Creating the USAGE SUMMARY SPREADSHEET (TAB 01) of the USAGE REPORT:
-
Create a new tab (sheet 2)
and rename it
Usage Summary
-
Now you will need to create a summary, with the usage data from the saved USAGE TAB page on the excel workbook.
-
Click on the USAGE SUMMARY Tab to bring up the Summary sheet.
-
Click on “INSERT”
on the menu ribbon at the top:
-
Then Click on “Pivot Table Menu”
-
This will bring up the Pivot Table Option Screen .
-
Now you need to select your Table/Range
-
Click on the USAGE Tab of the workbook, and select all columns with data.
-
Then Click “OK”
-
In the Pivot Tables Screen, click and drag” Calendar Full MO NM” into the columns section of the Pivot Table screen.
-
In the Pivot Tables Screen, click and drag “Service ID” into the rows section of the Pivot Table screen.
-
In the Pivot Tables Screen, click and drag “Count of Usage Minutes Qty” into the Values section of the Pivot Table screen.
-
Now you need to change the “VALUES” Field Settings from count to “SUM” for the Usage Minutes Qty section. To do this click the “count of usage minutes qty” in the “VALUES” section of the Pivot table and select “value field settings…”
-
In the value field settings screen change the setting from “count” to “sum.”
-
Then Click “OK”
Mark this pivot table on tab as the Usage Summary
Now the customer can see the usage report-
Step (3.)
Now we need to download the IST Recharge Services Summary BAIRS report
-
-
-
-
-
Click on IST_RECHARGE_SERVICES…
-
In the IST RECHARGE SERVICES Menu Box select the most current month of the 3 month’s picked in the IST USAGE report menu. (e.g. “June” from April, May, June)
-
-
-
-
-
-
-
-
Now click on “Run Report.”
-
Click “OK” on the Reports are ready to review button.
-
At the top click on view in the Ribbon Menu
-
Select “View Pane” from the drop down menu.
-
-
-
Click on Open,
-
On the excel file that your last command created, select the entire screen and do a “copy all” on the BAIRS provided excel report.
-
-
Rename TAB as “SERVICES.”
-
Now paste the BAIRS Excel Services Spreadsheet to your Excel Spreadsheet named “My Department’s BAIRS Review” on the SERVICES tab (in your own PC).
-
You now have a excel spreadsheet on a separate tab with all the information on it that you will need to complete the Service Summary Report, without having to constantly go back and forth to your BAIRS FILE for needed information.
Step (4.)
-
Building your Service Summary report:
-
Create a “Services Summary” Tab on your Spreadsheet
-
Now we will create a pivot table on the Services Summary Tab
-
Click on the SERVICES SUMMARY Tab to bring up the Summary sheet.
-
Click on “INSERT”
-
Then Click on “Pivot Table Menu”
-
This will bring up the Pivot Table Option Screen .
-
Now you need to select your Table/Range
-
Click on the SERVICES Tab of the workbook, and select all columns with data.
-
Then Click “OK”
-
Now we create the Pivot Table by inserting the needed data.
-
Click and drag the Service ID down to the “ROWS” section of the Pivot Table Menu Box.
-
Click and drag the “Build Charge desc 1” to underneath the “Service ID in the “ROWS” section of the Pivot Table Menu Box.
-
Click and drag the “Charge amt” into the “VALUES” section of the Pivot Table Menu Box.
-
Now you need to change the “VALUES” Field Settings from count to “SUM” for the Charge Amt section. To do this click the “count of Charge Amt” in the “VALUES” section of the Pivot table and select “value field settings…”
-
In the value field settings screen change the setting from “count” to “sum.”
-
Then Click “OK”
Now you have a summary of all your services and their associated costs.
All Phone Summary Report Tab:
To make a all phone summary report tab on your spreadsheet:
-
Create a new tab and name it ALL PHONE SUMMARY.
-
Click on the SERVICES TAB to open it.
-
Highlight and copy column “A”
-
Then open the ALL PHONE SUMMARY tab on the spreadsheet and paste the copied column “A” from the SERVICES tab to the column “A” from the ALL PHONE SUMMARY tab, then Highlight column “A” of the ALL PHONE SUMMARY.
-
Now we will need to remove all the duplicates from the data. Which requires us to click on the DATA menu in the upper ribbon menu and then click on “Remove Duplicates.”
-
When the remove duplicate wizard comes up, click OK.
-
When the Duplicate Data information Box comes up, click OK.
-
Since we only want a functional list of phone numbers in our summary, we will need to sort out everything that is not a service ID (telephone number). To do this we need to click on the SORT button on the DATA ribbon menu tab.
-
Then Click OK in the Sort Sub-Menu Box
-
And click “Sort anything that looks like a number, as a number” on the Sort-Warning Boxl
-
Then click “OK” in the Sort Warning Box.
-
Scroll down until your phone number list ends in column “A” and the delete any data after it further down the spreadsheet to the end
-
Now in Column “A” you have a complete list of phone numbers for your level 4 department to work with.
-
We now need to make a new phone tab and name it “PHONES WITH USAGE”
-
Now on the Usage Summary tab we want to copy column “A (phone number)” and column “E(grand total)” by using your control-click function.
-
Then open the PHONES WITH USAGE tab and paste columns “A” and “E” to that tab
-
Make sure that column “A” in both the ALL PHONE SUMMARY tab and the PHONES WITH USAGE tab are of the same data type (character or numeral)
-
Now click on the ALL PHONE SUMMARY tab and you will need to create a V-LOOKUP function in cell B1 of that tab
-
The formula should look like this
-
Now you will want to copy the data formula down column “B.” This can be done by clicking the little icon box at the bottom right of the cell B-1
-
Now that you have completed your ALL PHONE SUMMARY tab, the way you read the report is like this:
Look at row number 434, and it has the phone number 510-664-4219 in column “A” and then it says #N/A in column “B.” this means that the line in column “A” has no usage on it for the original BAIRS report monthly input (which was in this test 3 months April-May-June). Further, in row number 435, it has the phone number 510-664-4219 in column “A” and 19.38 in column “B.” This means that in that same 3 month timeframe this phone number had 19.38 minutes of usage total.
This concludes the instructions on how to assemble and use this “Line Usage” report.
Any questions: email ISTbill@berkeley.edu