By default in Velixo 6 and higher, the filtering of the Generic Inquiries in your reports is handled by Acumatica (so as to reduce the amount of data that needs to be transferred between Acumatica and your report).
Performing filtering through OData is very fast; however, it does have some limitations. For example: certain filtering options (such as "startswith") are not supported, there is a limit to the number of characters that can be in the filter, and filtering cannot be done on any calculated fields.
If your filtering efforts encounter one or more of these limitations, you may need to force Velixo to do the filtering, instead.
From the Acumatica ribbon in Excel, select Options, then the three-dot ‘kabob’ menu on the specific GI, and then Edit:
Then check the box for Always load the entire Generic Inquiry and filter the data locally:
This will do exactly that: the entire data set from the inquiry will be downloaded and then Velixo will do the filtering
You will need to specify the Key Fields used for the generic inquiry (click the round icon with the pencil in it).
And move any necessary fields from the Available Fields list to the Key Fields list:
(and click on OK for each dialog to get back to the report).
You can now refresh your report and the filtering of your GI() function(s) will be handled within Excel by Velixo Reports.
Smart Expand feature
Velixo Reports filter of generic inquires supports wildcards to match the beginning, the end, and the body of a text strings.
When all of the following are true:
- There is no explicit operator (=, <>, >=, etc.) contained in the criteria value
- The General Inquiry column being searched is of OData type (not date/time, number, etc)
- The criteria string is in one of the following formats:
GIFILTER will automatically replace the criteria with:
|other wildcard configurations (such as head*tail) are not supported|
Smart Expand Example 1
=GIFILTER("Demo","AR-Invoices and Memos","CustomerName","*USA*")
A wildcard * at both the beginning and end of the criteria denotes that the CustomerName field must CONTAIN the string USA
Smart Expand Example 2
=GIFILTER("Demo","AR-Invoices and Memos","Type","*Memo")
The wildcard * at the beginning of the criteria denotes that the Type field must END WITH the word Memo
Accommodating GI Filters in Acumatica
Acumatica Generic Inquiries can include pre-defined parameters which are used within Acumatica to filter the results of the inquiry:
Acumatica's OData interface (used by Velixo Reports for querying the GI) does not support this type of filtering. To be compatible with your Velixo report, you would need to modify your GI by either (a) removing the parameters or (b) making them optional by ensuring that the conditions in your GI will return the full dataset when the parameters are not specified.