AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Getting Started with Acumatica and Power BI – Part 2

December 19, 2017 by Tim Rodman

Getting Started with Acumatica and Power BI. In this example, finding unused inventory by displaying the inventory value with the last time it was ordered.

Are you using Acumatica?

Have you heard about Power BI, but aren’t sure how to get started?

In this blog post series, my goal is to help you get started.

Here’s the plan:

  1. I create a working Power BI example that is connected to Acumatica via Query Quarry.
  2. If you want to try it on your own Acumatica data, you can install Query Quarry together with the Power BI Desktop file at the bottom of this post.

Sound like a good plan?

Great! Let’s get started.

 

Today’s Example – Finding Unused Inventory

Have you ever looked at the stuff sitting in your warehouse and wondered, “when was the last time that someone actually ordered that?”

It’s so easy for the Purchasing department to buy stuff, then, oops, the customer’s order gets canceled, and, bummer, now you have excess inventory.

Getting product out to the customers is always the #1 priority. And it can be difficult to get the Inventory department to care about excess Inventory.

Hey, as long as we still have room in the warehouse, everything is fine right? Well, not if you’re trying to do a better job of managing your precious cash.

One of the strengths of Power BI is that you can combine different sets of data together in one report.

In this Power BI example, I created a simple table that pulls from two places which are the first two columns:

  1. Total Inventory Valuation: This column pulls the inventory valuation numbers from the same place as the Inventory Valuation report in Acumatica.
  2. Most Recent Order: This looks at all of the sales order lines across all order types and finds the most recent dated order.
  3. Days Since Most Recent Order: Not a separate data source. Just the difference between the Most Recent Order column and today’s date.

Pro Tip: You can also filter on specific warehouses using the Warehouse slicer on the right-hand side.

Note: Currently this includes Non-Stock Items which is kind of useless. I can filter them out, but I didn’t notice it until I was writing up this post and I just want to get this post published. So, if anyone is interested I’ll filter them out. Otherwise, I’m just going to leave it as-is for now.

 

Connect This to Your Acumatica Data

Are you curious to see what this Power BI example looks like on your Acumatica data?

First, you need to download and install Query Quarry since this example is based on Query Quarry. Click here and scroll to the bottom for instructions.

Second, if you don’t already have Power BI Desktop installed, you need to download it from Microsoft. Click here and then click the DOWNLOAD FREE button. Run the install after it’s downloaded.

Third, you need to download the .pbix Power BI Desktop file used in this example. Click here to download.

Fourth, open the .pbix Power BI Desktop file used in this example in Power BI Desktop and make the following changes to point to your data:

 

Click Edit Queries -> Edit Parameters

Power BI and Query Quarry

 

 

Change the Instance parameter to your Acumatica Instance URL.

Change the Company parameter to your Acumatica Company. It should match the company name shown after the @ symbol in the upper right-hand corner when you’re logged into Acumatica. If there is no @ symbol after your username when you’re logged in, then it means that you only have one company in your Acumatica instance so you need to leave the Company parameter empty.

Click OK

Power BI and Query Quarry

 

 

Click Refresh

Power BI and Query Quarry

 

If prompted, choose Basic, then enter your User name and Password, then click Connect

Power BI and Query Quarry

 

You should now be looking at this Power BI example with your own data. Cool huh?

Filed Under: Query Quarry Tagged With: Acumatica, Acumatica Blog, Acumatica Learning, Acumatica Reporting, Acumatica Training, Getting Started with Power BI, Power BI

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • RE: Construction Module/Purchase Orders - Commitments

    Do you see them in the Commitment tab of the project? I...

    By johnymt, 1 day ago

  • RE: Compressing Images

    That would be cool. Not that I know of personally. Are ...

    By Tim Rodman, 1 day ago

  • RE: Support for custom fonts

    @ckwiat That's what I've experienced with MICR which is...

    By Tim Rodman, 2 days ago

  • RE: Support for custom fonts

    I actually came across this that I will try:

    By Cory, 2 days ago

  • RE: Support for custom fonts

    @timrodman do you know if anything was definitely figur...

    By Cory, 2 days ago

  • RE: Adding bill description on check stub

    @cfritsch Chip great modifications. Just a quick questi...

    By Jswartz, 2 days ago

  • RE: Setting Up System Accounts with Individual Email Accounts for all Users

    Cory, Did your company end up making a decision o...

    By nsmith, 2 days ago

  • Problem when i have more then one Alternate ID

    Hi, I have Stock Items that includes more than one A...

    By Fosse, 3 days ago

  • Best way to Learn Microsoft PowerApps

    Hi All, I recently started PowerApps Training at Mind...

    By Steveskok, 3 days ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.