Questions BI for Sales Reports

Hi Sam,

Sorry for the late reply. I kind of put things on hold over the holidays.

Some things to consider.

Report Designer: When it comes to slicing and dicing where you want something that is interactive, Report Designer in Acumatica is not that great. Report Designer is really better for printed forms like Checks, Customer Invoices, Sales Orders, Purchase Orders, etc. As soon as you want to change your mind on something, you basically have to go build a new report and you have to use a tool that is more of a programmer's tool, not so much an end-user tool. Bottom line, Report Designer is not interactive so it's not for slicing and dicing.

Before going further, let me say that the foundation for everything going forward is the Generic Inquiry in Acumatica. Checkout the picture in this post (click here) to visualize what I mean by that.

Acumatica Pivot Tables: In my opinion, the Pivot Table should be the first step for anyone who needs slicing and dicing. Acumatica has Pivot Tables beginning in Acumatica 6. Click here for an overview of them. They are a good starting point and they might do the job for you, but don't expect them to be anywhere near as powerful as Pivot Tables in Excel.

Excel Pivot Tables: Microsoft Excel Pivot Tables have been around forever, but they made dramatic improvements in Excel 2016 by bundling the Power BI features in Excel out-of-the-box. And, if you are using Office 365, you have access to Excel 2016. With traditional Excel Pivot Tables, you have a limited list of functions and they can only be applied to one value: Sum, Count, Average, Max, Min, etc. With Pivot Tables in Excel 2016, you can write your own formulas called Measures which opens up a whole new world of possibilities for what shows up in the Values area of the Pivot Table. The downside here is that you are outside of Acumatica, but if you save your Excel file into OneDrive, you can refresh the data automatically using Power Update (click here) and then embed it inside of Acumatica like this (click here).

Power BI: The Power BI engine is available in Excel 2016, but you are limited to the Excel visuals. If you want visuals that are more like what Tableau offers (although not quite as robust), checkout PowerBI.com. Basically, you build your dashboards using a local program called Power BI Desktop (free), then publish them to PowerBI.com so you can use them in your web browser or with your Power BI Mobile App (free). If you want to share your dashbaords with others, then you pay $10/user/month.

Acumatica Dashboards: If you want to see some real simple charts, like Sales by Salesperson or Sales by Product, you might look at just using the regular Acumatica Dashboards. These also now work on the Acumatica mobile app which is cool. You can drilldown on a bar just by clicking it and it will take you to the underlying Generic Inquiry to see the detailed records. These get limited though if you want to slice by more than one thing, like maybe see Salesperson with Product indented underneath it, or see monthly buckets across the top. That's where Pivot Tables do a better job. But here is a screenshots of what Sales by Salesperson would look like.

What kind Sales reports are you looking for specifically? Maybe you could list out some ideas.