How do I organize BDRs by Sub-account, duration, and amount?

Follow

Douglas Sessions

Updated

This article explains how you can use the Excel functions to organize calls in your Billing Detail Records (BDRs) by Sub-account, duration, and amount (charges), as well as generate the telephone number (TN)/Sub-account sheets in the Bandwidth Dashboard. 

Please follow the steps in the Exporting TNs section before moving on to the Sub-account (Cell AC2) section. Once you create the TN/Sub-account sheet, you can copy and paste the functions directly into the BDRs. Although this works for both Statement BDRs and (daily) BDRs, we recommend using Statement BDRs if you’re reconciling an invoice.

Note: You’ll need to create a new sheet with the TNs and Sub-account names for the functions to work properly.

Exporting TNs

  1. Log into the Bandwidth Dashboard and select Manage sub-account for the Sub-account you’re exporting. 

manage-sub-account.png

  1. Click Numbers below the selected Sub-account.

numbers.png

  1. Click Show all numbers on your account (sub-account / location), select the Location (if the Sub-account has more than one), and click Search.

show-all-numbers-on-your-account.png

  1. Click Export All to download an Excel file with a list of the TNs on that account.

    Note: If you don't see the downloaded file, please check the Downloads section in your browser or the Downloads folder on your computer.

export-all.png

  1. Once you open the file, copy the function =SUM(A2+10000000000) and paste it into cell B2. Then double-click the green square in the lower-right corner of the cell to autofill the rest of the column. This will add a “1” to the TNs in column A, which is necessary because the country code “1” will be included in your BDR.

cell-b2.png

  1. Enter the name of the Sub-account that you exported the Excel file from and double-click the green square in the lower-right corner of the cell to autofill the rest of the column.

cell-c2.png

  1. Copy the range of cells in columns B and C, and paste the data into a new sheet (Sheet 1 in this example) in your BDR by selecting Paste Special -> Values. We recommend creating an Excel file with all of the TNs and Sub-accounts in the following format. This way you can quickly import this data into future BDRs as an additional sheet.

copy-column-b-and-c.png

paste-column-b-and-c.png

Sub-account (Cell AC2)

  1. Open your BDR and paste the function =VLOOKUP(E2,Sheet1!A:B,2,FALSE) into cell AC2. If you chose a different name for your sheet, make sure to replace “Sheet1” in the formula with that name. 
  2. Double-click the green square in the lower-right corner of the cell to autofill the rest of the column. This will identify which Sub-account a source TN belongs to.
  3. Give cell AC1 a title like “SubAccountName”.

column-ac.png

Note: If the Sub-account, Total cost, and Total duration cells are displaying “#N/A”, that means the source TN isn’t included in your TN/Sub-account sheet (Sheet1).

Additional formulas

Total charges accrued to a Sub-account

The following function will give you the total charges accrued to a Sub-account 

  1. Pick any cell next to a Sub-account in column AD (in this example, we picked AD481). 
  2. Paste the function =IF(AC481=$AC$1,"",SUMIF(AC:AC,AC481,I:I)) in the chosen cell. Make sure to replace the referenced cell we’ve highlighted with the cell one column over from the one you chose (in this example, it’s cell AC481).
  3. That's it! You should now see the total usage charges accrued to the relevant Sub-account.

column-ad.png

Total duration (in seconds) accrued to a Sub-account

The following function will give you the total duration (in seconds) accrued to a Sub-account

  1. Pick any cell next to a Sub-account in Column AE (in this example, we picked AE481). 
  2. Paste the function =IF(AC481=$AC$1,"",SUMIF(AC:AC,AC481,G:G)) in the chosen cell. Make sure to replace the referenced cell we’ve highlighted with the Sub-account name cell in the row you chose (in this example, it’s cell AC481).
  3. That’s it! You should now see the total duration (in seconds) of every call made from the relevant Sub-account.

column-ae.png

Questions? Please open a ticket with your Bandwidth Billing Team or hit us up at (855) 864-7776!

Article is closed for comments.