Spreadsheet Template for Global Usage Rules

Usage rules are defined in an *.XLSX format spreadsheet and uploaded to Gotransverse. The spreadsheet application you use must support the *.XLSX format. Refer to Global Usage Rule SpreadsheetFunctions for more information.

You will need the following information to define a usage rule spreadsheet:

• Type of usage.

• How the usage is measured.

• How the usage is charged.

• The output that is expected or needed for the customer's invoice and your own records.

Certain types of information need to be in specific cells on the spreadsheet. When creating a new usage rule, download the template file to use as a basis for your new usage rule. Refer to Download Global Usage Rule Spreadsheet for more information.

The template file includes every existing Rate or Lookup table in Gotransverse. These tables are included in the spreadsheet template as additional sheets. When using a lookup function to reference one of these tables, be sure to use the exact name as shown on the identifying Sheet tab.

Usage Lookup Tables in Gotransverse have 21 columns. The first column is identified as the Key column, containing the information on which the search is based. Columns 2 through 21 are Value columns that contain the information for which you are searching. The Key and first Value columns are required. The rest of the Value columns can contain data values or be blank.

The template file has the following features:

• Column A — Field Names. This column includes the following labels that describe the type of information expected in the corresponding cells in Columns B and C:

  • A2 - Limit

  • A3 - Remaining

  • A4 - Service Resource

  • A5 - Start Date

  • A6 - End Date

  • A7 - UOM

  • A8 - Amount

  • A9 - Description

  • A10 through A14: Text 1 through Text 5

  • A15 through A19: Number 1 through Number 5

  • A20 through A24: Boolean 1 through Boolean 5

  • A25 through A29: Date 1 through Date 5

  • A30 - Overwrite Amount

  • A31 - SVS Balance

  • A32 to A36: Rule Text 1 through Rule Text 5

  • A37 - Is Service Period Dirty

  • A38 - Usage Charge Category

  • A39 through A43 - Charge Text 1 through Charge Text 5

  • A44 - Re-Rating

  • A45 - Stop Rating

  • A46 - Stop Rating Error Message

• Column B - Event In. This column is used to input usage information into the spreadsheet and, therefore, into the usage rule. These cells are predefined for the following types of information:

  • B2 - Limit. If the customer or the product has a usage limit (that is, the number of usage units that are allowed in any given billing period), enter that limit here.

  • B3 - Remaining. If the customer or the product has a usage limit from which part of the total available has been used, enter the number of units remaining here.

  • B4 - Service Resource. If the product has an associated identifier that associates the usage to a specific customer (for example, a password, an IP address, a phone number, and so on), enter that identifier here.

  • B5 - Start Date. If there is a starting date that is applied to the usage and is used in the calculations, enter that date here.

  • B6 - End Date. If there is an ending date that is applied to the usage and is used in the calculations, enter that date here.

  • B7 - UOM. Unit of Measure. When you click this cell, a down-pointing arrow is shown to enable you to view a list of units of measure. Select the unit of measure most suitable for your usage rule.

  • B8 - Amount. The amount of usage to which the usage rate is being applied. The number in this cell is the number of Units of Measure used by the customer.

  • B9 - Description. The information you enter into this cell is included in reports that you create. This cell could contain any appropriate information (for example, the name of the product on which usage is being calculated).

  • B10 through B14 - Text 1 through Text 5. Tenant-defined cells. Suggested usage for these cells could be any textual input that is used by the usage rule. For example, one of these cells could be used for the name of the customer whose usage rate is being calculated.

  • B15 to B19 - Number 1 through Number 5. Tenant-defined cells. Suggested usage for these cells could be any numerical input that is used by the usage rule. For example, one of these cells could be used for the customer's billing account number (with, or in lieu of, a customer name).

  • B20 through B24 - Boolean 1 through Boolean 5. Tenant-defined cells. Suggested usage for these cells could be any Boolean input that is used by the usage rule.

  • B25 through B29 - Date 1 through Date 5. Tenant-defined cells. Suggested usage for these cells could be any date-based input that is used by the usage rule other than starting and ending dates (specified in cells B5 and B6).

  • B30 - Overwrite Amount. If you need to overwrite the usage amount returned from the usage event, enter it next to this column in Column C - Event. This value will be used as the usage amount if the service period is marked as 'dirty' and the usage event is re-rated.

  • B31 - SVS Balance. If the customer has a prepaid balance with an amount of money or quantity of a product or service that can be used, it is populated here by the SVS balance at the time the rule processes the rating. That current SVS Balance value is then used in the rule. Prepaid balance is managed by the Stored Value Service through Prepaid Register Types and is used when a customer pays for the usage in advance. To enable it, select the Enforce Balance checkbox when adding a Register Type.

  • B32 through B36 - Rule Text 1 through Rule Text 5. Tenant-defined cells. Suggested usage for these cells could be configuring tiered pricing. If the rule is set as Tiered, crossing a certain tier triggers the re-rate process to determine which tier is used to rate all events. Tiered usage rule rating is set up together with the B37 - Is Service Period Dirty field, which enables re-rating of the service period.

  • B37 - Is Service Period Dirty. If the service period should be re-rated and marked as ‘dirty’, enter True or 1 in Column C - Event.

  • B38 - Usage Charge Category. If you want to associate a Usage charge category with the usage rule, enter its name here.

  • B39 through B43 - Charge Text 1 through Charge Text 5. Tenant-defined cells. Suggested usage for these cells could be any textual input for charge information records. For example, you can use these fields to add more details to the usage event charge information records

  • B44 - Re-Rating. If you need to re-calculate usage charges, use this field to trigger the re-rating event. For example, you may want to re-rate the usage charges when the usage record changes or is updated; the rules for rating usage are changed during a service period; or a service agreement is configured with rating rules so that when usage records arrive out of sequence they are sorted properly.

  • B45 - Stop Rating. If you need to stop the rating process for a usage event in case of an error in rating for that specific rule, enter True or 1. The rating is stopped only when the usage rule has both Stop Rating value set as True or 1, and the rule returned 0 or False in Column D - Rule Result, which means the calculation is not valid and the next usage rule is invoked.

  • B46 - Stop Rating Error Message. If you set the B45 - Stop Rating cell to True or 1, enter the error message to display when the usage rule rating stopped due to an error. The custom error message provides a way to identify the specific usage rule that the rating process stopped on, investigate the usage event, and then work to resolve the usage event issue. The message can be up to 255 characters and can include data, such as results of formula's evaluation available in the spreadsheet. If you do not add a custom Stop Rating Error Message in this field, a default message will display when the Stop Rating event occurs.

• Column C - Event Out. If you cause information to be entered into one or more of these cells (cells C7 through C29), that information will be saved on the event record in Gotransverse with the corresponding label.

  The rate calculator uses 100% of the event's amount to consume. That is, the entire event is always consumed when the rate is calculated by the global usage rule, even when that amount exceeds the rule's limit. If you want the spreadsheet to calculate only part of the amount, you need to set cell C8 with the usage amount the spreadsheet rated. Then, the remainder of the event will be sent to the next rule.

• Column D - Rule Result. For each event being submitted to the usage rule, this cell should result in either True or False. If True, the calculation is valid and the rate is applied. If False, the calculation is not valid and the next usage rule is invoked. When creating the spreadsheet, decide which parameter is best qualified to control this action. For example, the formula that you add to this cell could check that a valid customer name is in one of the input cells.

• Column E - Rule Rate. The chargeable rate that is calculated by the usage rule to apply to the usage that triggered the calculation. If the usage rule is True, this calculation is applied to the usage being evaluated.

• Column F. This unlabeled column is used to separate the input and result part of the spreadsheet from the working area.

• Column G through Column Z - Work Area. The working area of the spreadsheet. This area is reserved for any calculations or related information that is used in the calculations. Initially, most of these columns are minimized. To use them, expand (widen) as many columns as you need. If entering related information here, you should use this area only for information that is not expected to change. For an example of a spreadsheet with this type of information, refer to Usage Rule Example for Internet Provider. You should not add a list of customers here, because that type of list is dynamic and changes whenever new customers are added or when existing customers' information changes. Customer information that is involved in the calculations should be in a Lookup Table that is easily updated. For information about Lookup Tables, refer to Usage Lookup Tables Module.

 

 

 

Topic updated: 06/2024.