Ok guys, off and on for the last 2 years I have been trying to work out a way to pass multiple parameters to a BAQ through a dashboard’s TrackerView for the same field. More specifically, here is what I have:
BAQ with InvcHead and InvcDtl fields (and various others that are linked properly).
Dashboard that uses above BAQ to return some financial reporting KPIs.
What I need to be able to do with this is to pass several states to filter the BAQ based on a shipment’s ShipTo.State field. I have tried using BAQ parameters, but that wants to prompt the user with a new selection popup window and doesn’t like multiple options being selected. I have even toyed with creating my own UI screen for a dashboard replacement in Visual Studio, but have run into the same issues when attempting to parameterized the SQL query call. Any ideas on what I can try?
In the dashboard I would try a publish and subscribe method. One baq view
to bring in the states to publish and then subscribe your invoice baq view
to the first one.
I haven’t done what you are attempting but that is how I would try it.
And that’s why I come here to ask questions! You’re suggestion seems sound in theory, and easy enough to implement without major revisions to the dashboard. Thanks Brad! I’ll take a look at that this afternoon and let you know how it turns out.
So, the publish/subscribe feature really won’t let me choose multiple states either. On the “State” GridView, I am able to highlight multiple lines, but on the “Invoice” GridView that is subscribed to that dataset, only the last selected state’s value is used as a filter. I guess what I really need to figure out is: is there a way to select multiple state values, and have Epicor utilize them as an “OR” type designation in the “WHERE” clause of the BAQ instead of being a singular value, ie. if I select “NV” and “IL” then it shows me all invoices that have a ShipTo.State=NV OR ShipTo.State=IL. I’m feeling as though this might be virtually impossible within the built-in dashboard maintenance module.
Is there anybody that has experience setting up a customized dashboard like this that can point me in the right direction on this?
Yeah, my first attempt was to add parameters to the BAQ. I ran into 2 issues with that design. The first was a cosmetic/workflow issue that isn’t a huge deal: parameterizing a BAQ forces a secondary window to open that the user has to fill in for the query to run. The second issue was the show-stopper on parameterizing the BAQ: I can’t get the BAQ to accept more than one parameter value.
Typically, I would one be selecting 3 or 4 states. I know I can run the dashboard 3 or 4 times, collect all the data from those and paste into excel, then analyze it from there. However, I am trying to put together a tool that is more user friendly for the average system user to run on their own.
I tried a simple BAQ on the Customer table & seemed OK…
Added parameters State1, State2, State3
Then Criteria State = State1 OR Sate = State2 OR Sate = State3
Maybe you have more tables or other criteria that are complicating the selection?
Yeah, that could work with the massive BAQ that I’m working with. I hadn’t really tried setting it up that way. I had attempted to setup a parameter (just one) as a RadioButtonSet on the Editor Type in the hopes that with the single parameter, I could select multiple values and have it automatically convert it to a “Selected1 OR Selected2 OR Selected3” type input. I’ll take a look at setting up multiple parameters as you have described and see what happens whenever one of the parameters is left blank. Thanks for the brainstorming session.
In case you’re curious, here is the BAQ that I have defined:
“for each InvcDtl no-lock , each Customer no-lock where (InvcDtl.Company = Customer.Company and InvcDtl.CustNum = Customer.CustNum ) , each InvcHead no-lock where InvcHead.CreditMemo <> true , each ProdGrup no-lock , each Part no-lock where (Part.Company = ProdGrup.Company and Part.ProdCode = ProdGrup.ProdCode ) , each ShipDtl no-lock , each ShipTo no-lock , each OrderHed no-lock , each CustCnt no-lock , each OrderDtl no-lock where (InvcHead.Company = InvcDtl.Company and InvcHead.InvoiceNum = InvcDtl.InvoiceNum ) where (InvcDtl.Company = Part.Company and InvcDtl.PartNum = Part.PartNum ) outer-join where (InvcDtl.Company = ShipDtl.Company and InvcDtl.PackNum = ShipDtl.PackNum and InvcDtl.PackLine = ShipDtl.PackLine ) outer-join where (InvcDtl.Company = ShipTo.Company and InvcDtl.CustNum = ShipTo.CustNum and InvcDtl.ShipToNum = ShipTo.ShipToNum ) outer-join where (InvcDtl.Company = OrderHed.Company and InvcDtl.OrderNum = OrderHed.OrderNum ) outer-join where (InvcDtl.Company = CustCnt.Company and InvcDtl.ShipToCustNum = CustCnt.CustNum and InvcDtl.ShipToNum = CustCnt.ShipToNum and InvcDtl.ShpConNum = CustCnt.ConNum ) outer-join where (ShipDtl.Company = OrderDtl.Company and ShipDtl.OrderNum = OrderDtl.OrderNum and ShipDtl.OrderLine = OrderDtl.OrderLine ).”
This is a brilliant way to attack the problem. It’s not 100% ideal, but it does solve the issue of a single parameter being able to accept multiple values. One update to what I did though, the field that I need to parameterize is a calculated field, so I setup the above suggested filter on the “Display -> Filter” tab in BAQ Designer instead of as a table criteria. Thank you so much for the input on this!
