By using this website, you agree to our Terms of Use (click here)
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 you please suggest any better way to achieve this goal.
Sales Order 1 (Example) - Contains only 1 line item which I want to exclude from report.
Sales Order 2 (Example) - Contains 2 line items out of which 1 item I want to exclude from the report.
Sales Order Summary Report - Additional filter condition I have added
Sales Order Summary Report - The report itself, which do not list the first sales order but has second sales order.
Hi Krunal,
FYI, this is the kind of scenario where Power BI does really well. Why? Because, in general, Report Designer is good at doing calculations one row at a time whereas Power BI can do one row at a time calculations or calculations based on groups of rows. Just thought I'd throw that out there in case you were interested in Power BI.
To do this in Report Designer, the best I can think 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. I think 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. I think that would do the trick.
Hi Tim,
Seems group total does not work as it is not considering the Visible expression. With Visible expression, the row is not showing in the report but it is still considering in the group total. I have also tried to add a variable for group total under details section but in that also it is considering only those rows which is InventoryID matching but not the entire order. Any suggestions?
Ahh, I found it. Unnecessary was making too complicated for totals. I had to declare all variables in groupFooterSection2.
Thanks.
Interesting, I thought you'd need to declare them in the Detail section. Glad to hear that you found a way to get it working!
Online Members
