I have a BAQ in which I created 4 parameters. Two are dates, the other two are bits. When I add a Table criteria with an expression, neither of the bit parameters are available in the “tree” menu.
I wrote the expression anyway using the @ symbol and the bit parameter names. In the Analyze tab, this works correctly without issue. However, when I load only that BAQ into a dashboard and enter the parameters, I get a Bad SQL server error.
The server error is "Ice.Common.EpicorServerException: Must declare the scalar variable “@[BitParameterName]”.
Anyone know why 1) the bit parameters are not available in the Table Criteria expression editor, and 2) why the BAQ runs perfectly by itself, but simply loading it into a dashboard with no other manipulation causes the above error?
Thank you in advance!
ETA: This BAQ has the Top Level query and one CTE subquery. The Table criteria expression is being added to a table on the subquery.
For the table filter value, instead of using ‘specified expression’, try using ‘specified parameter’ and you should be available to choose the bit parameters from in the ‘select parameter’ window that pops up. If not, try doing this to one of the tables on your top level BAQ to verify whether the parameter is only missing for the subquery.
Yes, the parameters are available in the list if I select ‘specified parameter’ in the criteria, but I need to use the criteria as part of a conditional expression, not just the criteria itself.
I did confirm the same behavior happens on both the top level and the subquery.
So just to confirm… you have a bit type parameter, it IS available in the list of parameters if you filter a field on a table on the top level query via the ‘specified parameter’ option but if you turn around and change the criteria selection for that same table, same field, the bit parameters are not visible on the tree view under the parameters node in the fields selection?
That is correct. I just ran a test and added a parameter of every type, then went into a ‘specified expression’ table criteria and only the original two date parameters are available in the tree view. But, if I switch the table criteria to ‘specified parameter’, all of the parameters are available to choose from.
When a bit parameter is required, I have to check and then uncheck the parameter checkbox for the BAQ to run without error. If I just try to leave it unchecked, it throws an error about it being required.
This is not typical behavior, but we don’t know where the query hosed these parameters up. I know this sounds painful, but I would rebuild the query from scratch. When rebuilding, the first thing I would do is create all the parameters I need, then as I add my first table, check to make sure I can see them in the expression editor. I would do a lot of saving along the way, and keep on checking those parameters, maybe not after every table I add, but at least when I add new subqueries, etc. If you do this, you’ll either discover that somewhere, your original query just bugged out for no good reason, or you’ll discover exactly when the issue starts occurring and have more information for us to go off of.
I started a brand new BAQ, defined my 4 parameters, added the first table and a single table criterion with a specified expression. The bit parameters are not available if they are not mandatory.
I’m starting to think it was designed this way. If anyone else reading this has the time to verify on their system, I’d love to find out if this is isolated to our environment. 10.2.700.24
To further complicate the question, if I leave ‘mandatory’ unchecked and ‘Skip condition if empty’ unchecked, the BAQ will not run (paramater required message) unless I check the boxes, or check>uncheck the boxes.
If I leave ‘mandatory’ unchecked and check ‘Skip condition if empty’, the BAQ will run, but then the bit parameters are not available in the expression editor. Is that also the case in your 10.2.700 environment?
Well, one mystery solved. Its the ‘skip condition if empty’. If that is selected, it’s hidden from the the expression editor. Yes, the BAQ on a bit will parameter will fail if its not selected, or selected, then unselected in the expression, because it’s null until something is done to it. If you use the bit parameter via the specified parameter option, you shouldn’t run into this issue. I know you said this wasn’t an option for you, that you need the table criteria to be an expression w/ the parameter… if you share what the expression you would use if the BAQ editor would let you do so, I might be able to help you come up with a creative solution.
Thank you for verifying. I posted a screenshot of the simple case expression earlier today.
Essentially, I’m using the checkboxes to allow users to filter by brand. For one brand, we use CustIDs that start with a number, for the other brand, CustIDs start with a letter.
In anticipation of needing to find another way to do this, I do already have calculated fields on the BAQ that determine which brand a record is from.
I just haven’t figured out how to use a parameter to take advantage of those columns yet. Any ideas would be very welcomed!
I found the solution to my particular problem. I can leave both the ‘mandatory’ and ‘Skip condition if empty’ boxes of the bit paramters unchecked and then set the default value to ‘no’. This allows them to be available in the expression editor, and still be actually usable as expected on the pop-up when running the BAQ.
