I’m trying to create a BAQ which pulls some basic AR Invoice data for display in Power BI. Currently I’m using the invchead table in a trivial BAQ simply to display fields with some light filtering on the table.
It’s worth noting I’m not the Epicor developer in our organisation, I have knowledge of BAQs and the basics of how Epicor is setup but I have never done any development myself. All of what I know has been gained from figuring out where to pull data from or why data might not be pulling as expected.
My issue is I need the invoiced amount without tax included but I can’t seem to find the correct field in the InvcHead table. I investigated AR invoice tracker with field help and application studio and it’s completely stock as far as I can tell. Our value without tax is displayed in the Subtotal field under Invoice totals on tracker with an EpBinding InvcHead.Dsp… This is the case for all fields under Invoice Totals and while some have a binding under Like without the Dsp prefix (these are displayable in BAQ) the Subtotal field does not. After some digging I gather that these are calculated at the BO layer from other fields each time, not stored in tables and therefore not possible to obtain form a BAQ.
Possible solutions I have considered so far are:
Calculating amount without tax in the BAQ from the amount including tax- This would be a last resort as it would include logic to determine tax exempt invoices.
Pull the Tax amount into the BAQ from InvcTax table on top of the amount including tax- This introduces extra complexity into the BAQ and seems like it should be unnecessary when the field is already being calculated somewhere but I wouldn’t be against this method, much more rigid than the above option.
I have not come across this situation before when creating BAQs so I’m mostly interested in confirming if my suspicions about the field being calculated at the BO layer are correct and the method, if possible of:
Customising the application and/or creating a UD field to hold the amount without tax for display in a BAQ.
Viewing the calculation occuring at the BO layer so I can recreate it in the BAQ.
If adding the InvcTax table to my BAQ is the simplest method then this shouldn’t be an issue for me to achieve I’m just trying my best to learn about areas of Epicor I’ve not delved into yet when the chance presents itself.
First and foremost, welcome to Epiusers and welcome to the roller coaster that is Epicor! You picked one of the worst tables to learn BAQs on!
1st question is how do you want your data displayed? This will control how the BAQ is constructed.
Do you want to see a single row for each invoice with the total, subtotal, discounts, tax, and miscellaneous charges?
Or do you want a row for each of the invoice lines?
Since you’re end goal is to report it in PowerBI think about if you want to do the calculations in Epicor (option 1 above) or PowerBI (option 2).
Personally when I know I’m going to do a PBI report I tend to write a “messy” baq and return all of the data and then do as many calculations in PBI as possible. I found that I end up making overly complicated BAQs to handle grouping and other things that PBI handles effortlessly with a matrix and some measures.
To answer your question, you 100% want to add the InvcTax table. You’ll probably also want the InvcMisc table to pick up things like freight charges (this is held in InvcDtl but if you want to know what type of charge it is you’ll need InvcMisc). How these are added to the BAQ depends on option 1 or 2.
In a nutshell, you’re going to have to take the InvcDtl and InvcTax tables, and add (InvcDtl.DocExtPrice - InvcDtl.DocDiscount + InvcDtl.DocTotalMiscChrg + InvcTax.DocTaxAmt)
Thanks for the welcome, my colleague has already brought me up to speed with the roller coaster of Epicor and I have already observed some of its ‘quirks’ in my time at the company.
I have a fair bit of experience now with the system of creating BAQs and pulling them into Power BI. I’ll have to confirm with those creating invoices to determine if/when multiple lines are used, it’s fair to say some of our Epicor practices are a bit unorthodox so it might not be how you’d expect. In most of my previous BAQs I’ve gone down the same lines as yourself and performed calculations within Power BI instead of Epicor and I imagine this will be no different.
I actually only need this BAQ to return shipment invoice types (I’m trying to get our sales figures) so I’m not sure if this removes the need for some of the fields you mention. This however is still extremely useful since I will need to delve deeper into AP & AR invoicing eventually (its something we have not looked at yet because we only started invoicing 100% in Epicor in the last few months).
I’m thinking it may simplify down to InvcDtl.DocExtPrice - InvcTax.DocTaxAmt. I will have to confirm to be certain but I don’t believe we ever apply discounts as prices are agreed contractually with customers in advance. Since I am only looking at sales I can also ignore shipment costs, this is purely our sales numbers (without any tax included).
I don’t think anybody uses Epicor the way Epicor expects us to use it
You might want to check with your accounting department because you may have to hit the TranGLC table to filter against the sales GL account and Sales Journal (if they have one)
Assuming you don’t have to use TranGLC, this is pretty easy.
Join InvcHead > InvcDtl
Left Join InvcDtl > InvcTax
Filter InvcHead by InvoiceType = SHP
Sales Price = InvcDtl.DocExtPrice
If you have multiple invoice lines this will return a row for each one. If you only want one row for each invoice then group by invoice number and your calculated sales price = SUM(InvcDtl.DocExtPrice). Depending on your dataset I would probably forego the grouping and just do Sales Price as a measure in powerBI.
I would second checking if the invoice tables are sufficient for the purpose, they may be but depending on what they are looking for and the structure of you Product Groups, GLs, Parts, etc it may not be enough.
If you sell in multiple currencies I would also look to whether you want the Doc or non Doc amount (Doc is the Customer’s Currency amount, non Doc is your company’s currency).
I believe the root cause of your issue not being able to report on that sub-total field is that it is an External Field which means it doesn’t live in the DB like other fields, its one of Epicor’s black boxes.
It turns out it is even simpler now as our DocExtPrice, rightly or wrongly, doesn’t include tax. This was only obvious to me when I added the InvcDtl table and the DocExtPrice field. The invoice header displays the totals slightly differently with regards to inlucding tax so I had assumed it would remain consistent at the line level. Moral of the story, never assume with Epicor.
I’ll still double check with accounting to determine if I need the TranGLC table or not but my expectation is that I won’t.
