Solved

Report Designer Additional FIltering Conditions (Does Not Start With)

  • 9 August 2022
  • 6 replies
  • 703 views

Userlevel 3
Badge

Hi:

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

icon

Best answer by Dioris Aguilar 9 August 2022, 18:37

View original

6 replies

Userlevel 5
Badge +2

@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.

Userlevel 3
Badge

@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

Badge +16

Hello,

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.

Userlevel 5
Badge +2

@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.

Userlevel 3
Badge

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.

Thank you very much for your help.

Userlevel 5
Badge +2

@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.

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