Κοινή χρήση μέσω

Learn how to read the line items in your Partner Center reconciliation files

  • Άρθρο

Appropriate roles: Billing admin

Download reconciliation files from Partner Center to view charges for each billing period. Check the charges for each customer's subscriptions and the events that affect them, such as adding licenses mid-term. For detailed invoice information, refer to read your bill.

Understand reconciliation file fields

Here are the reconciliation files and what they show:

Reconciliation file field Includes
Legacy license-based Costs and quantities of legacy Office and Dynamics products for a billing period
Legacy usage-based Costs and usage of legacy Azure products for a billing period
New commerce daily rated usage Daily usage and charges for new commerce (Azure plan) usage-based products for a billing period
New commerce invoice reconciliation Aggregated costs, quantities, and usage for all new commerce products for a billing period

Understand charge types in reconciliation files or line items

Learn about the different types of charges in reconciliation data in the ChargeType attribute.

Fix date formatting issues in reconciliation files

Reconciliation line items might have formatting issues if the en-us locale isn't used.

To address the formatting issues, follow these steps:

  1. Open the reconciliation file (in .csv format) in Microsoft Excel.
  2. Select the first column in the file.
  3. Open the "Convert Text to Columns Wizard".
  4. Select Data on the ribbon, then select Text to Columns.
  5. Choose Delimited file type, then select Next.
  6. Check Comma as the delimiter (if Tab is already selected, you can leave this option selected) then select Next.
  7. Choose Date:MDY for the column data format, then select Next.
  8. Select Text for all amount columns, then select Finish.

Download reconciliation files programmatically

To ensure accurate financial auditing and reporting, it's crucial to have detailed reconciliation files. These files often contain large amounts of transactional data, which can be challenging to download directly from the portal.

To make this process smoother, use the billed invoice reconciliation API. This method helps you retrieve detailed invoice data efficiently, even for large files.

Where do I download the file asynchronously?

To improve the download experience and boost efficiency, the Partner Center now processes new commerce reconciliation files asynchronously. To download a file:

  1. Sign in to Partner Center and select the Billing workspace.

  2. Go to the Billing overview (NCE) task menu > Billing | Billing Overview (NCE) page

  3. Choose your desired year and currency (if multiple currencies are available) at the upper right corner.

  4. Select the invoice number.

  5. Find the reconciliation file on the side panel.

  6. Select Download report. A notification appears at the top of the page indicating the file is being processed.

  7. Navigate to the Reports (NCE) page to track the progress of your download. You can monitor the status of your request in the status column of the request line item.

Understanding the status of your requests is key to efficient workflow management. Here's a clear and concise breakdown of each status and the actions you can take:

Status definitions

  • Queued: Your request is submitted and is waiting in line to be processed.

  • Processing: The system is actively working on your request, gathering and compiling the necessary data.

  • Completed: Your report is ready and available for download. You can access it immediately and proceed with your tasks.

  • Failed: An error occurred during the report generation due to technical issues or invalid input. Try submitting the request again or contact support for help.

  • No data found: The system couldn't find relevant data based on your criteria. Review and adjust your criteria and try again.

Actionable steps based on status

  • Queued: Your request is in the system and should be processed in due course. No further action is needed.

  • Processing: While you wait, ensure that you're prepared to utilize the report once it's ready. During this step, set aside time for analysis or preparing any necessary follow-up actions.

  • Completed: Download the report promptly and proceed with your analysis or tasks.

  • Failed: Review the input parameters of your request and ensure they're correct. If the problem persists, contact the support team.

  • No data found: Adjust the parameters to better align with available data and try again.

By understanding these statuses and the recommended actions, manage your requests and ensure timely access to the information you need.

If your file exceeds the row limit in Excel

If you can't open your .csv file in Microsoft Excel because it exceeds Excel's row limit, you have these options to open the file:

  • Open a reconciliation file in an Excel Power Query: Power Query allows you to import and transform data from .csv files without facing the constraint of Excel's row limit. It's useful for large datasets that require data cleaning and manipulation before analysis.
  • Open a reconciliation file in Power BI: Power BI is a powerful tool for handling large datasets. It provides robust visualization and reporting capabilities, making it ideal for comprehensive data analysis.

Open a reconciliation file in Excel Power Query

  1. Download the reconciliation file from Partner Center.

  2. Open a new blank workbook in Microsoft Excel.

  3. Navigate to Data tab, then select Get Data > From File > From Text/CSV, and import the .csv (Comma-Separated Values) reconciliation file.

  4. After a while, you can see a window with the file preview.

  5. In that window, select the Load dropdown menu > Load To.

    Screenshot of the File preview window, with the dropdown next to Load open, and the selection Load To inside the drop-down selected.

  6. In the Import Data dialog box, select the options as indicated in the following screenshot.

    Screenshot of the Import Data window, with two items highlighted: Only Create Connections and Add this data to the Data Model.

  7. Select OK to start the loading process. Wait until it's complete.

  8. See the file name and number of rows on the right side. Double-click this area to open the Power Query Editor.

    Screenshot of the Queries & Connections window, with a sample of a report called Test Recon Data highlighted.

  9. As you scroll down, you see that new rows are added dynamically.

    Tip

    Fix data loading problems caused by line breaks:

    • Go to the "Advanced Editor" under "Home" tab.
    • Search for the "Csv.Document" function.
    • Update the "QuoteStyle" value to "QuoteStyle.csv."

  10. To search for specific line item, use the filter option as shown in the screenshot. Additionally, aggregate line items or perform calculations with the "Group By" or "Statistics" features.

    Screenshot of Excel Power Query Editor, with two highlighted items, Group By and Statistics.

  11. For instance, to find the number of billable days in the charge cycle, use the Custom Column feature to calculate the duration between the charge start and end dates. Power Query also provides a range of features for different computations.

    Screenshot of Excel Power Query Editor. In the menu bar, Add Column is highlighted.

  12. After applying the required filters in the Power Query, transfer the data to Excel by following these steps.

    Screenshot of Excel Power Query Editor. In the spreadsheet, the CreditReasonCode drop-down is open. The menu item: Copy Entire Table is highlighted.

Open a reconciliation file in Power BI

To open a reconciliation file in Power BI:

  1. Download the reconciliation file as you normally would.
  2. Download, install, and open an instance of Microsoft Power BI.
  3. Select Get data on the Power BI Home tab.
  4. Choose Text/CSV from the list of Common data sources.
  5. Open your reconciliation file when prompted.

Negative amount displayed

Some reconciliation line items show negative charges for the following reasons:

  • To reflect changes in your subscription due to cancellations, upgrades, or license modifications.
  • To account for credits received from service level agreement (SLA) violations, Azure platform migration, or price adjustments.

Understand these negative amounts by referring to the ChargeType attribute in the reconciliation line items. For instance, if the ChargeType is "customerCredit," it indicates a refund due to SLA violations, Azure credit, or price adjustments.

How is the total cost calculated for new commerce license-based purchases?

To determine costs of a license-based subscription, always use the EffectiveUnitPrice, not the UnitPrice. Here's why and how it works:

The EffectiveUnitPrice reflects your final cost per unit after accounting for:

  • Promotions: Discounts or special offers applied to reduce pricing.

  • Tiered pricing: Volume-based pricing where prices increase or decrease as quantities change.

  • Adjustments such as partner earned credit: Credits earned through managed services that lower costs.

  • Prorated billing adjustments: Costs might be adjusted based on the number of days billed within a charge cycle. The number of days billed is counted from when a billing event occurs until the end of the charge cycle. For example, if a billing event occurs on the 10th day of a 30-day charge cycle, you're billed for remaining 20 days.

Note

The subscription's UnitPrice remains fixed for the entire billing term. Changes in the UnitPrice within the reconciliation line items might occur but don't affect the actual costs. Always use EffectiveUnitPrice for precise cost calculations.

Why does my invoice reconciliation data show a recurring charge for a product without daily charges?

If you see recurring charges for products like Azure savings plans or reservations without daily usage fees, here's what's happening—and how to stay in control:

Key points to understand

  • Fixed price commitment: You're billed a fixed amount each month, regardless of usage. For example, A USD 100/month Azure savings plan bills USD 100 every month, even if you use zero hours of the service.

  • No extra charges until the limit is exceeded: Extra costs apply only if usage surpasses your commitment. Once your usage surpasses the agreed limit, the pay-as-you-go rate applies. For example, if your plan covers 100 hours of virtual machine usage and you use 120 hours, you're charged pay-as-you-go rates for the extra 20 hours.

  • Visible recurring charges: Azure savings plans and reservations are commitment-based. You're billed the agreed amount monthly to reserve capacity, ensuring availability and discounted rates. Extra charges apply only when usage exceeds the agreed limit. For example, if you agree to pay USD 100 per month for a service, this amount is billed every month regardless of usage. Extra charges apply only when usage exceeds the agreed limit.

To effectively monitor your costs, make sure to regularly review how your Azure reservation or savings plan is linked to your Azure subscription. Take control of your Azure costs today by regularly reviewing your subscription links and usage limits!

How do you find the Azure subscription ID for a product?

To find out your Azure subscription ID for each Azure resource, follow these steps:

Tip

To easily identify products purchased through different or the same Azure subscriptions, use resource groups or tags. This approach ensures clarity by linking each product with its respective subscription, even when subscriptions have identical names. It allows billing admins to organize resources, streamline reporting, and enhance operational clarity.

Pay-as-you-go resource

To identify your Azure subscription ID for any pay-as-you-go resources,

  • Find the SubscriptionId on your invoice reconciliation line item.

  • Match it with the SubscriptionId on your daily rated usage line item.

  • The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

Azure reservation

To identify your Azure subscription ID for an Azure reservation,

  • Find the ReservationOrderId on your invoice reconciliation line item.

  • Connect it with the "ReservationOrderId" within the AdditionalInfo on the daily rated usage line item.

  • The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

Azure savings plan

To identify your Azure subscription ID for an Azure savings plan,

  • Find the ReservationOrderId on your invoice reconciliation line item.

  • Connect it with the BenefitOrderId on the daily rated usage line item.

  • The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

How to identify Azure credits in invoice reconciliation and daily rated usage data

Understanding how to track Azure credits is essential for effective reconciliation and credit management of Azure products. Here's how you can find these credits in both invoice reconciliation and daily rated usage data.

Invoice reconciliation

To identify the credits:

  • Use the ChargeType field and filter the "customerCredit" line items. The CreditReasonCode specifies the type of credit helping to identify the reason behind the credit.

  • Look for Subtotal and Total fields showing negative amounts for these line items.

  • For accuracy, group "customerCredit" line items by these fields: ProductId, SKUId, AvailabilityId, OrderDate, and either CustomerName or CustomerId. Then, sum the Total to determine the credit amount.

  • Ensure the BillableQuantity is the same for both charge and credit line items to maintain consistency and accuracy in your credit management process.

Daily rated usage

To filter and find credit amounts:

  • Use the CustomerId, CreditType (for example, Azure credit offer), and 100 for the CreditPercentage value to filter the line items.

  • Sum the BillingPreTaxTotal of these line items to find the credit amount. This step ensures that you capture the total pretax credit amount accurately for your records.

Note

While daily rated usage data provides granular insights into resource consumption, certain Azure credits might not appear in the daily rated usage data. Always refer to the invoice reconciliation data for complete credit information.

By following these steps, you can accurately track and manage Azure credits, ensuring a clear understanding of your reconciliation data.

How do you reconcile telco pay-as-you-go charges?

Follow these steps to track your telco product charges on a daily and monthly basis:

  • Daily rated usage reconciliation: Check your daily charges under BillingPreTaxTotal and your call minutes in "NumMin" under AdditionalInfo. This method ensures that your daily charges and call minutes are accurately recorded and billed.

Note

Quantity in the daily rated usage data represents an approximation and not the exact call minutes used.

CustomerName ProductId SkuId ProductName UsageDate ChargeType UnitPrice Quantity UnitType BillingPreTaxTotal BillingCurrency AdditionalInfo EffectiveUnitPrice
Constoso CFQ7TTC0JL2N 1 Microsoft 365 International Calling Plan - Outbound US 9/17/2021 New 1 0.1524 Per Call 0.15 USD { "NumMin": 0.2} 0.984251969
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/18/2021 New 1 6.6275 Per Call 6.623381835 USD { "NumMin": 24.1} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/21/2021 New 1 2.97 Per Call 2.968154515 USD { "NumMin": 10.8} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/22/2021 New 1 0.0585 Per Call 0.05846365 USD { "NumMin": 0.3} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/23/2021 New 1 0.4485 Per Call 0.447924684 USD { "NumMin": 2.3} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/24/2021 New 1 0.0975 Per Call 0.097374931 USD { "NumMin": 0.5} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/29/2021 New 1 0.156 Per Call 0.15579989 USD { "NumMin": 0.8} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 International Calling Plan - Outbound US 9/29/2021 New 1 0.2114 Per Call 0.211128825 USD { "NumMin": 1.4} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/30/2021 New 1 0.178 Per Call 0.177771669 USD { "NumMin": 8.9} 0.998717244
Total 10.89 49.3
  • Invoice reconciliation: Check your total call minutes and costs per call. Look at BillableQuantity and Subtotal.
CustomerName ProductId SkuId ProductName ChargeType UnitPrice Quantity Subtotal TaxTotal Total Currency ChargeStartDate ChargeEndDate EffectiveUnitPrice BillableQuantity
Contoso CFQ7TTC0JL2N 1 Microsoft 365 International Calling Plan - Outbound US new 0.381 1 0.36 0 0.36 USD 9/1/2021 9/30/2021 0.225 1.6
Contoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US new 0.352836167 1 10.53 0 10.53 USD 9/1/2021 9/30/2021 0.220755 47.7
Total 10.89 49.3

If you exceed your free talk time, you pay for each extra minute. To reconcile the charges and call minutes between your daily usage and monthly invoice, use the ProductId and SkuId.

Check charges and minutes by call type: domestic, international, or toll-free. The bill shows total call minutes and total amount before tax. The subtotal equals the sum of BillingPreTaxTotal, and BillableQuantity equals the sum of "NumMin."

How to find tiered pricing in the reconciliation files or line items

Understanding tiered pricing in reconciliation data and analyzing it effectively is essential for optimizing costs and maximizing value from your usage.

Let's delve into this concept and explore how you can use it to your advantage.

Tiered pricing: a staircase analogy

Think of tiered pricing as a staircase where each step represents a different usage level. As you use more, the cost per unit decreases, encouraging higher consumption.

Key fields

To find the tiered pricing in your reconciliation files or line items, look at the following fields and ensure you're optimizing your usage to benefit from the best rates.

  1. Unit price: This price is the initial rate per unit for the first tier. It's like the standard price before any discounts.

  2. Effective unit price: This price shows the actual rate you pay per unit after accounting for your total usage and tier discounts, reflecting the actual price after adjustments.

  3. Price adjustment description: Details about any modifications applied to your total cost, including:

    • Partner-earned credits: Rewards offered to partners for meeting specific criteria after providing managed service to their customers.
    • Volume-based discounts: Reduced rates for higher usage.
    • Promotional discounts: Special offers that lower the overall cost.
    • Other price modifications: Other adjustments affecting the final price.

Calculate your final cost

To determine your final cost, consider the EffectiveUnitprice and any adjustments noted in the PriceAdjustmentDescription.

Example

Imagine you used 150 units in a billing period with the following pricing tiers:

  • Tier 1: 0-100 units at USD 10 per unit

  • Tier 2: 101-200 units at USD 9 per unit

  • Unit price: USD 10

  • Partner earned credit (PEC): 15%

  • Effective unit price: USD 7.65 (USD 9 for Tier 2) – (USD 9 x 15% = USD 1.35 for PEC)

  • Price adjustment description: Partner earned credit 15%; Tier discount 10%

Optimizing usage for better pricing

  • Monitor usage: To approach higher tiers, regularly track your resource usage.

  • Plan strategically: If you're near a higher tier, consider increasing usage slightly for significant savings.

By addressing each aspect of tiered pricing systematically, you can make informed decisions to maximize cost efficiency and align your usage with optimal pricing tiers.

Understand ReservationOrderId prefixes

Efficient management of Azure reservations starts with understanding the ReservationOrderId prefixes. Knowing these prefixes helps you identify the origin of your Azure reservations, which allows you to organize and optimize your resources better. Here's what each prefix means:

  • PCTest: This prefix is for reservations acquired through the Partner Center for testing purposes. It's useful for distinguishing between test environments and production setups.

  • RIOderId: Reservations bought from the Partner Center for production use have this prefix. Identifying these reservations is crucial for managing live environments and ensuring proper resource allocation.

  • No prefix: If there's no prefix, the reservation was purchased directly through Microsoft Cost Management. This approach is ideal in segregating reservations made outside the Partner Center.

By familiarizing yourself with these prefixes, you can streamline your reservation management process and make informed decisions.

Related content