Credit and collections management Power BI content

This topic describes what is included in the Credit and collections management Microsoft Power BI content. It explains how to access the Power BI reports, and provides information about the data model and entities that were used to build the content.


The Credit and collections management Power BI content was created for credit and collections managers, and collections clerks. It provides key credit and collections metrics, such as days sales outstanding, balance overdue, credit exposure, and customers that are over their credit limit. It uses transactional data, and provides aggregate views of credit and collections across all companies. It also provides a breakdown per company, customer group, and customer.

This Power BI content consists of 10 report pages:

  • Two overview pages (one page for a credit overview and one page for a collections overview)
  • Eight details pages that provide details of credit and collections metrics that are sliced and diced across various dimensions

All the amounts are shown in the system currency. You can set the system currency on the Systems parameters page.

By default, the credit and collections data for the current company is shown. To see the data across all companies, assign the CustCollectionsBICrossCompany duty to the role.

Accessing the Power BI content

The Credit and collections management Power BI content is shown in the Customer credit and collections workspace.

Reports that are included in the Power BI content

The CustCollectionsBICrossCompany Power BI content includes a report that consists of a set of metrics. These metrics are visualized as charts, tiles, and tables. The following table provides an overview of the visualizations in the CustCollectionsBICrossCompany Power BI content.

Report page Visualization
Collections overview
  • Customers past due
  • Customers over credit limit
  • DSO 30 days
  • Open cases
  • Average days to close case
  • Open activities
  • Average days to close activities
  • Open interest notes
  • Open collections letters
  • Customer hold
  • Sales hold
  • Aged balances
  • Collections status amounts
  • Collections code amounts
  • Write-off by reason
  • Balance due by region
  • Expected payments
Credit overview
  • Customers past due
  • Credit limit Vs balance due
  • Customers over credit limit grid
  • Customers over credit limit per company
  • Credit used Vs total credit limit
  • Credit limit Vs Credit used by region
  • Customers per credit rating
Credit limit
  • Credit limit
  • Credit limit details
  • Credit limit per customer
  • Credit limit per customer group
  • Credit limit per credit rating per company
  • Credit limit vs credit used by region
Customers over credit limit
  • Customers over credit limit
  • Customers over credit limit detail
  • Customers over credit limit per company
  • Customer over credit limit per customer group
  • Customers over credit limit by region
Customers past due
  • Customers past due
  • Customer past due details
  • Customer past due per company
  • Customer past due per customer group
  • Customer past due by region
Aged balances
  • Aged balances
  • Aged balances details
  • Aged balances per company
  • Aged balances per customer group
Expected payments
  • Expected payments
  • Expected payments details
  • Expected payments per company
  • Expected payments per customer group
  • Expected payments by region
  • Write-offs by region
  • Write-offs details
  • Write-offs by main account
  • Write-offs per company
  • Write-offs per customer group
  • Write-offs by region
Collections status
  • Disputed
  • Promise to pay broken
  • Promise to pay
  • Collections status details
  • Collections status amounts
  • Open cases
  • Open activities
Collections letters
  • Collection code amounts
  • Collection code amount details
  • Collection letter amount per company
  • Collection letter amount per customer group
  • Collection letter amount by region

The charts and tiles on all these reports can be filtered and pinned to the dashboard. For more information about how to filter and pin in Power BI, see Create and Configure a Dashboard. You can also use the export underlying data functionality to export underlying data that is summarized on a visualization.

Understanding the data model and entities

The following data is used to fill the report in the Credit and collections management Power BI content. This data is represented as aggregate measurements that are staged in the Entity store. The Entity store is a Microsoft SQL Server database that is optimized for analytics. For more information, see Overview of Power BI integration with Entity store.

Entity Key aggregate measurements Data source Field Description
CustCollectionsBIActivitiesAverageCloseTime NumOfActivities, AveragecClosedTime smmActivities AverageOfChildren(AverageClosedTime) Count(ActivityNumber) The count of closed activities and average time to close those activities.
CustCollectionsBIActivitiesOpen ActivityNumber smmActivities Count(ActivityNumber) The count of open activities.
CustCollectionsBIAgedBalances AgedBalances CustCollectionsBIAgedBalancesView Sum(SystemCurrencyBalance) The sum of aged balances.
CustCollectionsBIBalancesDue SystemCurrencyAmount CustCollectionsBIBalanceDueView Sum(SystemCurrencyAmount) The amounts that are overdue.
CustCollectionsBICaseAverageCloseTIme NumOfCases, CaseAverageClosedTime CustCollectionsCaseDetail AverageOfChildren(CaseAverageClosedTime) Count(NumOfCases) The count of closed cases and the average time to close those cases.
CustCollectionsBICasesOpen CaseId CustCollectionsCaseDetail Count(CaseId) The count of open cases.
CustCollectionsBICollectionLetter CollectionLetterNum CustCollectionLetterJour Count(CollectionLetterNum) The count of open collection letters.
CustCollectionsBICollectionLetterAmount CollectionLetterAmounts CustCollectionsBIAccountsReceivables Sum(SystemCurrencyAmount) The balance of posted collection letters.
CustCollectionsBICollectionStatus CollectionStatusAmounts CustCollectionsBIAccountsReceivables Sum(SystemCurrencyAmount) The balance of transactions with collection status.
CustCollectionsBICredit CreditExposed, AmountOverCreditLimit CustCollectionsBICreditView Sum(CreditExposed), Sum(AmountOverCreditLimit) The sum of credit exposure and amounts that customers are over their credit limit.
CustCollectionsBICustOnHold Blocked CustCollectionsBICustTable Count(Blocked) The number of customers that are on hold.
CustCollectionsBIDSO DSO30 CustCollectionsBIDSOView AverageOfChildren(DSO30) Days sales outstanding for 30 days.
CustCollectionsBIExpectedPayment ExpectedPayment CustCollectionsBIExpectedPaymentView Sum(SystemCurrencyAmounts) The sum of expected payments within the next year.
CustCollectionsBIInterestNote InterestNote CustInterestJour Count(InterestNote) The number of interest notes that have been created.
CustCollectionsBISalesOnHold SalesId SalesTable Count(SalesId) The number of total sales orders that are on hold.
CustCollectionsBIWriteOff WriteOffAmount CustCollectionsBIWriteOffView Sum(SystemCurrencyAmount) The sum of transactions that have been written off.