Power BI analytics

Bitta Consignment comes with a Power BI template that reads your consignment data live from Business Central. It has eight pages: an overview, vendor stock, aging and expiry, sales and margin, settlement and what you owe your vendors, turnover and slow movers, customer sites, and a trace of lots and serial numbers.

What you need

• Power BI Desktop, to open the template. To share the report, a Power BI account that can publish to the Power BI service.
• The template file BittaConsignment.pbit. Bitta Apps sends it with the app, or you can download it from the Bitta Consignment page.
• A Business Central user with one of the permission sets BAA CONS READ, BAA CONS EDIT or BAA CONS SETUP. The user also needs read access to vendors, locations, items, customers, posted purchase and sales documents and item and value entries. Most users have this through D365 READ.

The report reads data only. It never needs a license seat.

Connect the template

1. Find your company's ID. In a browser, open https://api.businesscentral.dynamics.com/v2.0/<tenant ID>/<environment>/api/v2.0/companies, sign in, and copy the id of your company.
2. Open BittaConsignment.pbit in Power BI Desktop.
3. In ServiceRoot, enter https://api.businesscentral.dynamics.com/v2.0/<tenant ID>/<environment>/api/bitta/consignmentAnalytics/v1.0/companies(<company ID>)/ with your tenant ID, environment name (for example Production) and company ID.
4. Leave QuerySuffix empty. On an on-premises server that hosts tenants, enter ?tenant=<tenant>, for example ?tenant=default.
5. Choose Load. When Power BI asks how to sign in, choose Organizational account and sign in with your Business Central user. Apply the setting to https://api.businesscentral.dynamics.com/.

The report loads. Save it as a .pbix file, or publish it.

Read the pages

Page	Shows
Consignment overview	Vendor stock value, sales of vendor stock, what you owe the vendors, stock value at customer sites and shrinkage %, with sales and settlements by month
Vendor stock	Vendor stock per vendor and location at the end of the period, its value by month, and what was received, sold, returned and bought out
Aging and expiry	Open vendor stock by age and by expiry, stock older than the vendor's aging period, and every lot and serial number with its age and days to expiry
Sales and margin	Sales, average selling price, vendor cost and gross margin per item, customer and vendor
Settlement and payable	What the vendors invoiced, what is received and not invoiced yet by vendor and age, and each line still to settle
Turnover and slow movers	Turnover and days on hand per item and vendor, and stock not sold for more than the days you choose
Customer sites	Stock at each customer site, usage, replenishment, shortages, write-offs, shrinkage % and days of cover
Trace	Every consignment movement of a lot or serial number, and the lots and serial numbers at customer sites

The vendor cost on Sales and margin is what the vendors invoiced for your sales plus what is received and not invoiced yet. A settlement invoiced in a later month moves cost into that month, so compare whole periods or the total to date.

Stock value of vendor stock shows only when Value Vendor Stock is on in Consignment Setup.

Show the report on your role center

After you publish the report to the Power BI service, you can show it on the Business Manager, Order Processor or Purchasing Agent role center.

1. On the role center, choose Personalize.
2. Show the Consignment Power BI part, then choose Done.
3. In the part, choose Select reports, choose the Bitta Consignment report and then OK.

The first time, Business Central asks you to connect to Power BI. Follow the steps in Get started with Power BI.

If the report does not load

Message	What to do
We couldn't authenticate with the credentials provided	In Power BI Desktop, open File > Options and settings > Data source settings. Clear the old entries for api.businesscentral.dynamics.com and load again.
Access denied, or a page stays empty	Give the user BAA CONS READ and read access to items and customers, then refresh.
The resource is not found	Check that ServiceRoot ends with companies(<company ID>)/ and that the environment name is right.