By using this website, you agree to our Terms of Use (click here)
We have two generic inquiries. One returns the forecasted revenue for kit items. The second returns the MRP costs for the forecasted items. We need to link the two inquiries so we get a data-result that shows the revenue and the costs for the forecasted periods. In SQL we'd write to an output table and append records and then query the combined results.
Is there a way we can do this in Acumatica using generic inquiries. The end result we want is a pivot table that shows forecasted revenue and forecasted (MRP) costs by period so we can see the cash requirements by period.
We are open to any ideas as to how to get a cash-flow forecast from forecasts and MRP requirements.
Have you looked at PowerBI?
@roycelithgo Not yet. Thanks for the suggestion.
We are looking into OData and we can get the GI's to Refresh in Excel but the data is still on two separate worksheets and we need to combine the data (automagically) into one table somehow.
If you have the data in 2 worksheets in Excel shouldn't be too difficult to merge.
I do this in PowerBI all the time, which was why I suggested it. It does have a bit of a learning curve though.
I'd like to second the Power BI idea from @roycelithgo for this scenario. Note that you can do it in Excel too since the Power Query functionality is available in Excel.
Supposedly, in the future, Generic Inquiries will include the ability to do subqueries. I wonder if they will also include the ability to "append" two Generic Inquiries together when they add this functionality.
For now, the best way that I know of to do this within Acumatica Generic Inquiries, is to use what I call the Wyatt.ERP Technique. It's essentially a way to do a SQL UNION statement:
https://www.augforums.com/forecasting-future-acumatica-project-revenue-in-monthly-buckets
Since I wrote that article, I learned a cleaner way to use the technique, without having to create Attributes, by using the DateInfo table.
I just added the Wyatt.ERP Technique to my list of ideas for a Tips & Tricks lesson in the Building Generic Inquiries & Pivot Tables course (click here). I know I've been silent so far on Tips & Tricks, but today is the first day that I'm getting into my "Tips & Tricks Friday" routine. The goal is to dedicate Fridays to "Tips & Tricks" lessons. That's actually why I read this post today. I'm "hunting" for an idea for today 😀
Thank you for the reply Tim. We ultimately used four generic inquiries tied to four refreshable OData Excel sheets and used the Excel Merge function to combine the four table results. Then we built a pivot table off the merged data and got a reasonable cash flow statement with data from the Forecast, AR Aging details, AP aging details, and MRP results.
We will check out your WyattERP technique as well.
Thanks for posting
@mikelupro I just published Part 1 of the new lesson series in the course and summarized it in a blog post here:
https://www.augforums.com/the-wyatt-erp-technique-for-generic-inquiries
Stay tuned for additional parts that walk through how the example was built.