Update Pricing Profile from Excel

Use this option when you need to add or update a large number of meters in the existing Pricing profile. This feature allows the admin to edit and add meters from the existing Pricing profile through Microsoft Excel and to update it back into the Billing system through the Billing tool. When you need to add or update very few meters, using the UI is recommended.

Connect and Load Pricing Profile

• Get the Pricing Profile ID from Pricing Profile Page in Hybr Billing.

• Download the below SQL Query and replace the {PricingProfileId} with the Identifier value of the Pricing profile to be loaded.

769B

PricingProfileQuery.sql

• To connect Excel to a Billing database, open Excel, and then create a new workbook or open an existing Excel workbook.

• In the menu bar at the top of the page, select the Data tab, select Get Data, select From Database, and then select From SQL Server Database.

• In the SQL Server database dialog box, type the Server and Database name to connect to Billing Database. Select Advanced Options and fill the SQL Statement with the SQL Query prepared in step #2. Select OK to open the credentials window.

• Enter the credentials to connect to the Database and select Connect.

• Click Load to load the data in sheet.

Update the meters

• To edit the existing meter, edit the monthly unit price as required and set IsCollectionEnabled to TRUE.

• To add new meters, fill in all the fields and set the ID as 0.

• Save the file.

Update the Pricing Profile

To update the changes to the Pricing profile, follow the below steps.

• Login to Billing RP server and use the latest Billing Tool.

• Copy the file inside the Billing tool folder

• Ensure the MS Access DB engine is installed in the machine from where the billing tool is run. (https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255).

• Open the Windows Command Prompt in the Billing tool folder. Replace the variable in the below command and execute to update changes in Pricing Profile.

Billing.exe AddOrUpdateMeteredResourcePricing /sheetName:{sheetName} /ApiEndpoint:{billingApiUrl} /Username:{billingApiUserName} /Password:{billingApiPassword} 

• Once completed, Meters Updated message should be displayed.