Exclude Orders from Sales Order Summary report

0
votes

I want to exclude the Sales Order completely based on the InventoryItem I select in report parameter for Sales Order Summary report (Screen ID - SO610500). Meaning, if I select a particular InventoryItem in the parameter, the report should not include the entire Sales Order where I have the selected InventoryItem as one of the line item.

I have tried Additional Filter by adding SOLine.InventoryID, Does Not Contain condition but that seems to work when I have only 1 line item for the order which needs to be excluded but if there are more line items then it is not working.

I have also tried adding new additional parameter to exclude the select InventoryID but that also does the same.

Can anyone please suggest any better way to achiev this goal.

Sales Order 1 (Example) - Contains only 1 line item which I want to exclude from report. enter image description here Sales Order 2 (Example) - Contains 2 line items out of which 1 item I want to exclude from the report. enter image description here Sales Order Summary Report - Additional filter condition I have added
enter image description here Sales Order Summary Report - The report itself, which do not list the first sales order but has second sales order. enter image description here

1
acumatica
Not sure who has downvoted but please provide an explanation as to what I am missing in the question? Please explain. – Krunal
Don't worry about it, question looks fine. Some people unfamiliar with Acumatica watch review queues and do that from time to time. – Hugues Beauséjour
Thank you @HB_ACUMATICA – Krunal
The hard part here is the SQL. If you were able to achieve the filter using a single SQL query without a SQL sub-query then it could be implemented in the report table schema. We had a tutorial on SO documentation showing how to use a Graph with a report which allows you to have view delegates and more complex data manipulation. That approach is more work to setup but is more flexible and will allow the filter easily: riptutorial.com/acumatica/example/25273/… – Hugues Beauséjour
Is there a way I can use SubReport, pass the selected report parameter value to SubReport first and then use this SubReport as some source to exclude the Orders in the report which I do not want it. – Krunal

1 Answers

0
votes

Finally found the trick to achieve what I need.

To do this in Report Designer, the best would be to do a variable in the Detail section. It could be an IIf formula that returns TRUE if the InventoryID on the row is equal to the InventoryID that you are looking for or FALSE if not. Then you'd need to set the ResetExpr on the variable to reset to 0 when it reaches the next Order. Then you would need to move the fields from groupHeaderSection2 to groupFooterSection2 because the variable won't exist until after the Details section. Finally, you'd need to put the variable in the VisibleExpr field of groupFooterSection2 so the section does not print when the variable is false. You will have to adjust the group totals accordingly by declaring the variables in groupFooterSection2.