I am trying to create an inventory report that allows the user to view the inventory of whatever specific items they please (and only those items). The first 4 digits of our products' item numbers designate which family of items the products are in, so that is what I would like to filter by, using the "Begins With" comparison operator.
I have gotten the report to work as intended when I only input one parameter (the attached screenshot shows the result of using "Begins With 3140", but I can't figure out how to get it to accept multiple parameters (ie. show all items that start with 3140 OR 0191 OR 5690, but not show any items that don't).
I've tried typing in "3140, 0191, 5690", as well as "3140 OR 0191 OR 5690", both of which gave me an error saying there was no data (I assume this is not the right formatting).
I have also tried making multiple Begins With parameters, but couldn't get that to work. If I had 3 separate parameters, each set to 1 of the values above, with "Use Or Logic" turned off, it would tell me there was no data (presumably because an item can't start with 3 different values). However, if I had "Use Or Logic" turned on for all 3 parameters, it would output every single item in our inventory, regardless of its item number.
I suspect this has something to do with the fact that the report also has filters built in, but I'm not sure if that's really the issue. I'm not sure how the behind-the-scenes stuff works in Excel or this program, but maybe the Or Logic is effectively saying it starts with any of the 3 values I input, OR meets any of the criteria I set for the report filters. Whereas I want it to be limited to items that start with any of the 3 values I input, AND meets the criteria of the filters.
How can I configure this correctly?
I think you are on the right track with using OR and multiple Begin with parameter.
However, did you try open bracket "Prefix with Logic Break (" for the first begin with and close bracket for the last Suffix with Logic Break ) ?
Thank you Shwu, that (mostly) solved the problem.
I just put "Prefix with Logic Break (" on the first parameter, and "Prefix with Logic Break )" on the last parameter.
What ended up working was making everything but the first parameter use OR logic. If the first parameter used OR logic, it spit out all of the data like before. However, if the first parameter did not use OR logic and was set to AND instead, while the remaining parameters were set to OR, it spit out the proper data I requested.
My only issue now is that if the item number starts with a 0, it ignores the 0 when outputting. Ie. If the number starts with 0191, the report treats it like I input 191.
If I understand correctly, only the output is affected.
What if you format the Item Column of the output file as TEXT, save the template and then rerun the report?