Questions Dashboard to display Sales for current month and current year for each salesperson

For Sales Comparison by Item Class what should be the relation between ARInvoice and InventoryItem table?

Right-click to Download

What version of Acumatica are you running?  I get an error trying to upload the xml file.  

I was able to upload the xml file into build 17.204.0019. But I've had trouble sometimes with these xml files. Not completely sure why though.

For the join, you want to join on ARTran, not ARInvoice because the Inventory Item lives at the line level of the invoice.

Try this, making sure to set the Join Type to Left because there may or may not be an Inventory Item on the line.

Joining tables together is by far the trickiest thing about building generic inquiries. I used to try to teach people how to do it, but eventually settled on, "hey, you can do everything else, but call your partner when it gets to the Relations tab."

The reason is that you really need an understanding of normalized databases, primary keys on tables, and how joins work. It's also helpful to know how ERP tables are typically structured in general so you know where to expect to find certain information.

For your particular example, what I would do is open the database and find the primary key on the CustomerClass table. The primary key could be made up of more than one column. Now, even though Data Access Classes which you are working with in the Generic Inquiry and the Tables in the database are not the same thing, I would say that about 90% of the time, they happen to have the same names. So, this isn't a 100% of the time method, but usually it works.

Checking the CustomerClass table in the database shows me that CustomerClassID is the only column needed to form a primary key.

But you won't find CustomerClassID in any of your existing tables. You'll need to join to Customer on ARInvoice.CustomerID = Customer.BAccountID, then from Customer you can join to CustomerClass on Customer.CustomerClassID = CustomerClass.CustomerClassID.

Hi Everyone,

I have been following Tim’s video to do a report that will show sales by item class and by period. I managed to make it work as a GI but since I need I to  grouped it by period as well, I had to make it into a report. However, the sum formula doesn’t work in this case, it is somehow combining numbers from the group instead of summing. Is it the iif formula that is throwing it off?

This is the formula I used to sum MTD:

=Sum(IIf(Year([SOOrder.OrderDate])=Year(@AsofDate) And Month([SOOrder.OrderDate])=Month(@AsofDate),[ARTran.NetSalesAmount],0))

and this is the GI which is working:

It would be great if there was a simpler way in pulling sales by period ([ARTran.FinPeriodID]) instead of pulling them by order nbr and grouping them. My end goal would be to display those columns by period and then broken down by item class.

I stumbled upon this wonderful thread and it has been very helpful. The issue (I think) I am having is that the report doesn't account for credit memos properly. I have another report I use for activity and it uses the AR Tran table. I had to include this formula in my sales column to account for transactions that were credit memos =IIf( [ARTran.DrCr]='D', -1, 1 )*[ARTran.TranAmt] (so that if a credit memo then the amount would post as a negative amount and subtract from the sales).

My question is what is the best way to roll this (=IIf( [ARTran.DrCr]='D', -1, 1 )*[ARTran.TranAmt]) into your formulas above? I have tried a couple variations but no luck, but I've noticed our sales rep #'s for MTD are slightly off and I believe its because the system is adding a credit memo instead of subtracting the value.

Any insight would be helpful.

In case anyone else runs into this scenario......THE ANSWER IS.......

=iif([ARTran.DrCr]='D',(IIf(Year([ARInvoice.DocDate])=Year(Today()) And Month([ARInvoice.DocDate])<=Month(Today()),(-1*[ARTran.CuryExtPrice]),0)),(IIf(Year([ARInvoice.DocDate])=Year(Today()) And Month([ARInvoice.DocDate])<=Month(Today()),(1*[ARTran.CuryExtPrice]),0)))

After many hours and persistence, I was finally able to get a formula that displayed the proper values (taking into account negative signs for credit memos)