By using this website, you agree to our Terms of Use (click here)
We enter in Projects and Sales Orders to create our backlog. I need to be able to combine the Project Revenue Total Budget and the Sales Order Total to provide a total amount booked for the week, month and fiscal year. I am able to get them individually and put them on a dashboard.
Is there a way of combining on a GI (to turn into a dashboard) Projects and Sales Order to get this value combined? I can download the data and do it in Excel, examples attached.
I like Excel for this scenario.
What you are missing in a Generic Inquiry is the UNION concept from SQL where you can have two completely separate queries, but combine the results together.
The only way I can think of at the moment to do this within Acumatica would be to create a SQL view that does a UNION between the two queries, then connect the SQL View to a Data Access Class that feeds a Generic Inquiry that feeds a Dashboard.
Use Power BI - you will be able to accomplish so much more.
Note that you can use Power BI inside Excel. Using Data -> Get Data -> From Other Sources -> From OData Feed on the Excel Ribbon will take you into the Power Query window where you can combined multiple inquiries in a similar way to doing a UNION in SQL.
You can get this to work using GIs. If you check out This Post I go over making an attribute you can join off of and rope a bunch of information together. Although in that post I'm using it to do a cardinal(cross) join, you can use the trick to effectively union information together.
Use tables: FinPeriod
CSAttribute (Inner Join on CSAttributeDetail.AttributeID ='MYPIVOT' and CSAttributeDetail.SortOrder <= 2)
SOOrder (Left Join on FinPeriod.FinPeriodID as one link and CSAttributeDetail.SortOrder = 1 as a second link)
Project* (Left Join on FinPeriod.FinPeriodID as one link and CSAttributeDetail.SortOrder = 2 as a second link)
*I'm not sure the name of the actual project DAC, we don't have that module.
This should effectively give you information like this:
01-2019, 1, [Sales Orders], NULL
01-2019, 2, NULL, [Projects]
02-2019, 1, [Sales Orders], NULL
02-2019, 2, NULL, [Projects]
03-2019, 1, [Sales Orders], NULL
03-2019, 2, NULL, [Projects]
etc..
Group By your FinPeriod, and then have your fields as SUM(ISNULL(Order Total, 0)), SUM(ISNULL(Project Total,0)), and SUM(ISNULL(Order Total, 0)+ISNULL(Project Total,0)) for both.
Client First and Power Storage Solutions found a solution. It is working really well to track bookings as projects and SO are open. We then use it to forecast revenue out into the future based upon SO ship dates and Project Scheduled end dates. We have the monthly forecast move down as we invoice to see we completed everything in the month.
I cannot explain or now how to post the GI solution. Kristi (Clients First) and Zach (Power Storage Solutions) created the solution together. We would be happy to share.
This is turning into a really cool Discussion Topic!