Solved

Issue with Summing up Lines


Userlevel 4
Badge

Hi All,

I am creating a report that will help me identify amount of defect / return over a period of time. I used SO.61.10.00 Sales Order Detail By Customer as base report. 

The issue I am having is that the SOLine.ClosedQty is not able to sum based on the different order types. I have multiple order types that I need to subtract each other in order to produce the result I am looking for. Below are what I am trying to do:

Sum of Sales Order - Sum of Correction (Custom Order Type)

Sum of Credit (Custom Order Type) + Sum of Replacement (Custom Order Type)

I used IIF function in on the detail and it work fine, only when it comes to SUM where the number is off. 

=IIF([SOOrder.OrderType]='SO',
[SOLine.ClosedQty],
IIF(
[SOOrder.OrderType]='CO',
sum([SOLine.ClosedQty]),
'0')

Are there any suggestion on what I can do in order to achieve what I want?

Kind regards,

Calvin Law

icon

Best answer by Dioris Aguilar 24 June 2022, 21:03

View original

14 replies

Userlevel 5
Badge +2

@Calvin What I can see from the information provided is that the formula does not seem right: since it has the same field (SOLine.ClosedQty) with different conditions (different order type) where the field itself is returned for one condition and the Sum of it is returned for the other. 
Usually an aggregate function (Sum()) is used in a footer section of a group by.
Could you please share more information? For example, the location of this formula inside the report and the entire formula itself.

Userlevel 4
Badge

Hi @Dioris Aguilar,

Thank you for taking a look at this post. Below will be the screen shot of what the report looks like in Report Designer. The green arrows are the fields that are working with individual transaction. The red arrows are the fields that I am trying to get the sum of specific condition. 

This is what the report current generates.

Ultimately I am trying to create a report that will provide a clear pictures of how many each item is sold, how many defect and how many return over a period of time. 

Kind regards,

Calvin

Userlevel 5
Badge +2

@Calvin Ok, thanks for the information provided, could you also share the whole formula for a field in the groupFooter section? (any with the red arrow)

Userlevel 4
Badge

Hi @Dioris Aguilar,

Here are the formulas:

 

Sold QTY - 

=IIF([SOOrder.OrderType]='CO',sum([SOLine.ClosedQty]),'')

Defect QTY - 

=IIF([SOOrder.OrderType]='CD',sum([SOLine.ClosedQty]),IIF([SOOrder.OrderType]='RP',sum([SOLine.ClosedQty]),''))

Return QTY - 

=IIF([SOOrder.OrderType]='CM',sum([SOLine.ClosedQty])'')

 

Kind regards,

Calvin

Userlevel 5
Badge +2

@Calvin You should accumulate the value in the detail section to be able to evaluate the OrderType. Try creating a variable in the header section like this:

 

Then, update its value in the detail section like this:
 

And finally, use this variable $SoldQty in the footer section instead of the formula you currently has.

Userlevel 4
Badge

Hi @Dioris Aguilar,

Thank you for the variable formula. I can see it now count the order type specify by the variable in the footer section. This sums up the number of occurrences the order type has during the time period. This would help me out on some other report I am working on. 

The only issue is that I am trying to have it sum up the Value base on each Inventory ID and then reset on the next item. Looking at below screen shot, I am looking for a way to get the value to add up as 36 for the purple highlight and 60 for orange highlight.

How would I be able to achieve the result of total for each item?

Kind regards,

Calvin

Userlevel 5
Badge +2

@Calvin Try defining the variable in the first group:
 

Since it is defined inside a group, in your case the Inventory Item (first group), the variable will be reset automatically for the next group, i.e., for the next inventory item, so, you can leave the ResetGroup field empty.

Userlevel 4
Badge

Hi @Dioris Aguilar,

Thank you for the info.

I was playing around with the formula and was able to get partial result that I am looking for. Currently I created a variable for each type of order types. I input the formula below to the group detail Variable field:

 =IIF([SOOrder.OrderType]='SO',sum([SOLine.ClosedQty]),'SO No') 

=IIF([SOOrder.OrderType]='CO',sum([SOLine.ClosedQty]),'CO No')

=IIF([SOOrder.OrderType]='RP',sum([SOLine.ClosedQty]),'RP No')

=IIF([SOOrder.OrderType]='CM',sum([SOLine.ClosedQty]),'CM No')

And below are the layout highlighted for each of their placement in the report:

If you look at below result page, you can see that it is able to sum up quantity under SO type, however, It does not work when there are more then one order type in the item. 

 

 

 

This is close to what I want to achieve but still not working properly. 

Kind regards,

Calvin

Userlevel 5
Badge +2

@Calvin It’s much easier if you could share the report. Some of the fields highlighted in red seem to have a sum of two variables, so, not sure what you have there.
I suggest to not use the Sum() operator in the group detail section, it makes more sense to use it in the group footer section.

Userlevel 4
Badge

Hi @Dioris Aguilar,

I tried but I do not know how to attached the report on the forum. It would not recognize the RPX file. Would you be able to advise how I would attach the report file?

For grouping, when I followed your method, it does do count of how many transaction each other type has, but it did not sum the value of each transaction. How would I group in the footer section to achieve the final result of total sum of all value in each order type?

I am not familiar with coding nor SQL so it is a real struggle to try and get something that seems simple working in Report Designer. 

Kind regards,

Calvin

Userlevel 5
Badge +2

@Calvin Try zipping your report file (.rpx) and attach it here to check it.

Userlevel 4
Badge

Hi @Dioris Aguilar,

Here is the file. 

Calvin 

Userlevel 5
Badge +2

@Calvin 

Define the variable in the first group section filling the reset fields like this:
 

 

And try applying the formula this way:
 

As you can see, based on the OrderType value, the variable $SO is incremented or decremented at the details section and the total is calculated properly.

Final result with different OrderTypes and OrderNbr:
 

 

IMPORTANT: This is a weak relationship for the INSiteStatus table, tables must be joined by their key fields otherwise they could duplicate the resulting records:
 

 

Instead, do it this way using SOLine (not InventoryItem):
 


HINT: You can place a temporary field with a variable to check how its value changes on each record:
 

Attached is the resulting report.

Userlevel 4
Badge

Thank you @Dioris Aguilar!
I had follow the changes and made minor modification and now it works for both SUmmary and Detail view!

Sincerely,

Calvin

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved