Our client is used to filter his legacy Crystal Reports using the condition [CustID] Not Equal 9*.
Crystal generates the correct SQL clause as Where [CustID] Not Like ‘9%’.
What would be the equivalent syntax in Report Designer’s Additional Filtering Conditions?
We don’t have an option like “Does Not Start With”
I could generate the correct filter using Parameters, but the client may want to use multiple ‘Not equal’, so it would be ideal to use the Additional Filtering Conditions.
Thanks
Best answer by Dioris Aguilar
@gpineda64 In that case, you could add an additional parameter where users can enter the initial characters of the customer name to be excluded:
And the constraint will be as follows:
Notice the first two lines added with the “Or” are used to prevent filtering the records when no string has been entered to the “Not Starts With” field.
Running report from 01/01/2022 to 08/09/2022 with above constraint and Not Starts With = “ABCH”:
The only sales order excluded is the one belonging to ABCHOLDING customer.
@gpineda64 You can try adding a new constraint in the Filters section in the report to filter out the unwanted customer names. The following example uses the Sales Order Summary report (SO610500):
Running report from 01/01/2022 to 08/09/2022 without any change:
Adding following constraint to Filters section:
Running report from 01/01/2022 to 08/09/2022 with above constraint:
Notice sales orders from ABCHOLDING and ABCVENTURE customers are no longer listed.
@Dioris Aguilar , thanks for you reply. Your solution with Parameters will work when the Values are known ahead of time, but the client has several sales persons who enter different value combinations for the accounts they want to exclude. In the example they provided, the user is excluding customer accounts that start with 9 or 29, but another user may want to exclude a different set of values, so it would be ideal if they can create dynamic combinations in the Additional Filtering Conditions. Thanks
I am wondering what is the meaning for the digits at the front of the customer ID, and whether this meaning could be captured in another way that is more conducive to Acumatica reporting. Can Attributes or User Defined Fields, or other fields of Acumatica hold the same information, removing the need for “Starts With” and “Doesn’t Start With” in report filters? Just an idea.
@gpineda64 In that case, you could add an additional parameter where users can enter the initial characters of the customer name to be excluded:
And the constraint will be as follows:
Notice the first two lines added with the “Or” are used to prevent filtering the records when no string has been entered to the “Not Starts With” field.
Running report from 01/01/2022 to 08/09/2022 with above constraint and Not Starts With = “ABCH”:
The only sales order excluded is the one belonging to ABCHOLDING customer.
It seem that I’m going to have to rely on Parameters, as your example. The caveat is that I’ll have to give them at least 3 Parameters options for each field, and they are needed in more than 10 reports, so it’s going to be a lot of work.
@gpineda64 Acumatica comes with a functionality for reports that allows users to add custom filter options including one to filter fields on how they start with as shown below:
However, “Does Not Start with” option should be implemented as mentioned above in this thread.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.