Business Central / NAV UG
Business Central / NAV UG
Organizer:
- Organized by
-
-
Using Power BI to recreate the “Inventory Valuation” report
-
Using Power BI to recreate the “Inventory Valuation” report
At our last User Group meeting, users mentioned wanting to know more things they can do in the Microsoft Ecosystem with Business Central. One of the most time-saving things I have done for our company is recreating the “Inventory Valuation” report inside of Power BI to be always available and up-to-date. No more waiting hours for it to run! I’ll outline how I did it below.
Background:
- BC 21 On-Prem
Prerequisites:
- Power BI Gateway installed and running
- Power BI Desktop installed with access to:
- SQL Server where BC database is running
- BC On-Prem connection inside of Power BI
- Gateway properly mapped inside of the Power BI web app
(If you have any questions about the prerequisites, just ask!)
**This works for BC – the NAV table structure is a little different and doesn’t use the same steps as below **
Inside of Power BI, you’ll want to import the following tables:
Using the Business Central On-Premise Connection:
- Item_Card_Excel
- Item_Ledger_Entries_Excel
Using the SQL Connection:
- Item$xxxxxxxxxxxxxxxxxxxx (Unique identifier depends on your system)
Once imported, you’ll want to establish the following relationships:
- Item_Ledger_Entries_Excel (Item_No) –> Item_Card_Excel (No)
- Item$xxxxxxxxxxxxxx (No_) –> Item_Card_Excel (No)
Next, you’ll want to create a custom column that adds together the “Cost Amount Actual” with the “Cost Amount Expected” – This is critical since the inventory valuation report will use the expected cost until the transaction is finalized and “Adjust Cost – Item Entries” is run. Without this step, your Power BI report will not tie out exactly to your Inventory Valuation Report within BC.
From here, you’ve set up the report relationships enough to create some visuals.
I like to always pull the item number from the “Item” table, as well as its description. This ensures that the report will always utilize the same number and description that is actively on the item card. Sometimes these get updated and are different than what is in the Item Ledger Entries table.
I pull the Item Table to get the dimensions I have set on the item card, in addition to the Item Posting Groups. This allows us to easily filter the data inside of Power BI.
To get a table that looks like the Inventory Valuation Report, I select the following:
From the Item_Ledger_Entries_Excel Table:
- Quantity
- Custom Column that adds the “Cost Amount Actual” with the “Cost Amount Expected”
From the Item_Card_Excel table:
- No
- Description
This will give you a table that directly mirrors the Inventory Valuation Report.
If you want to include a slicer to easily filter between dates, you will need to do the following:
- Open the Power Query Editor for the Item_Ledger_Entries_Excel table
- Ensure that the “Posting Date” Column does not include the time
- Open the settings for your current report and disable the “Automatic Time Intelligence”
Now, when you select the “Posting Date” as a slicer, you can configure it as a slider!
The last step is publishing the report to Power BI and configuring the automatic refresh through your gateway connection.
I don’t have this configured in a test environment to post screenshots, but if this gets enough traction, I might make a more detailed post on it.
I hope this helps someone else out there!
-
1 Reply
The discussion ‘Using Power BI to recreate the “Inventory Valuation” report’ is closed to new replies.
