I still have a lot of hope for learning how the Data Access Classes in Acumatica can contribute to better reporting. As far as I can tell, I think the Data Access Classes function like the Acumatica Reporting??? side of the picture below. However, I think that I’m going to revert to my comfort zone for a little while to see how well I can create reports directly against the Acumatica database like in the Traditional Reporting side of the picture below. In more traditional ERP systems, your only option is to report directly on the database so this is what I’m used to.
Still Waiting For Better Power Query and Power Pivot Microsoft Excel Connectivity
Gabriel Michaud’s comment on a previous post made me hopeful that the Acumatica development team will eventually be able to tie their Excel connectivity feature (new in version 4.1) directly into Microsoft Excel’s Power Query and Power Pivot features. However, until then, I have decided to spend more time learning to report directly on the Acumatica Database using Power Query and Power Pivot.
The Next Several Weeks – Temporarily Abandoning the Acumatica Data Access Classes
Over the next several weeks, I’m planning to create a number of different sample reports directly on the Acumatica Database using the Acumatica DEMO company. I’ll be temporarily abandoning the Acumatica Data Access Classes. These reports will point Microsoft Excel directly to the Acumatica Database using Power Query and Power Pivot (maybe even Power Map). This exercise should help me to get more familiar with how the Acumatica Database is laid out.
I am very interested in learning how to create better reports in Acumatica. In my opinion, reporting should drive an ERP implementation. Most companies focus on getting data into their ERP system before they care about getting data out. In reality, if you want to do a good job of designing how data should go in, you need a clear vision of how you want data to come out. What better way to do some quick report mockups than in Microsoft Excel. Well, here it goes…
Mike,
I’m going to schedule the post for Wednesday instead of Monday so I can space out my posts a little more.
Mike,
I changed my mind and decided to go ahead and take a look at this. There is something about real-world examples that makes learning this stuff a lot more fun so I couldn’t resist. Please take a look at this video and let me know if this is what you were looking for. I’ll put up an official blog post about it on Monday.
Also, I wanted to ask, have you considered using Attributes in this situation? I think that Attributes would allow you to accomplish the same thing, but they have the advantage in that it’s much easier to add new future values to a drop-down since you don’t have to keep re-opening the screen customization. You could also control them at the Opportunity Class (CR209000) level if you wanted to which is another advantage.
https://www.youtube.com/watch?v=lJaVrcjOkus
Thanks for the kind words Mike. It was nice to have a real-world example to work on with the Project Quote (original post here).
Regarding adding fields to screens, I don’t know how to do that yet, but it’s definitely on my list of interesting things that I’d like to learn. Especially since Acumatica allows unlimited users, there is a lot of incentive to abandon the isolated old-school Access applications and build something within the Acumatica framework to capture whatever information you want. I’m just not sure yet how involved it’s going to be to learn.
Currently I’m focussed on reporting directly on the database, but next I would like to move back into the framework to understand how it works, including adding fields to screens. Have you tried using the T-series courses in Acumatica University to help you with this?
Hi Tim, thank you once again for your help with the Quote for a Project. It has been really useful. Have you started trying to do screen upgrades yet? I am in the process of adding two fields to the opportunities screen in Customer Management. First time I have done it. The video isn’t helping much. I thought I would ask you if you had any hints or tricks to try. Hope your enjoying the learning!!