# BAQ Report Filter field **Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5) **Created:** 2017-01-12 13:08 UTC **Views:** 13960 **Replies:** 36 **URL:** https://www.epiusers.help/t/baq-report-filter-field/36881 --- ## Post #1 by @ckrusen What limits which fields are available for a Filter in BAQ Report Designer? Here's the graphic of my BAQ And here's what is available for Filter fields in the BAQ Report Designer Are filters only limited to the first two tables? My end goal is to have a filter for the Plant Name, on the Report window. --- ## Post #2 by @josecgomez You can only filter on fields you are displaying on the bAQ> --- ## Post #3 by @ckrusen But I'm displaying many more fields than are shown in the BAQ Report Designer filter option Here's the fields being displayed: Here's the BAQ Query Phrase select [Part].[PartNum] as [Part_PartNum], [Part].[PartDescription] as [Part_PartDescription], [Part].[TypeCode] as [Part_TypeCode], [Part].[NonStock] as [Part_NonStock], [Part].[QtyBearing] as [Part_QtyBearing], [Part].[InActive] as [Part_InActive], [Part].[IUM] as [Part_IUM], [PartBin].[BinNum] as [PartBin_BinNum], [PartBin].[LotNum] as [PartBin_LotNum], [PartBin].[OnhandQty] as [PartBin_OnhandQty], [PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost], [PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost], [PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost], (PartBin.OnhandQty * PartCost.AvgMaterialCost) as [Calculated_StockValue], [Plant].[Name] as [Plant_Name] from Erp.Part as Part cross join Erp.Plant as Plant inner join Erp.PartBin as PartBin on Part.Company = PartBin.Company And Part.PartNum = PartBin.PartNum inner join Erp.PartCost as PartCost on Part.Company = PartCost.Company And Part.PartNum = PartCost.PartNum inner join Erp.Warehse as Warehse on Warehse.Company = Plant.Company And Warehse.Plant = Plant.Plant inner join Erp.Warehse as Warehse and PartBin.Company = Warehse.Company And PartBin.WarehouseCode = Warehse.WarehouseCode --- ## Post #4 by @ckrusen What's that "cross join" in the query I posted above? In the GUI phrase builder GUI, all the join types show as "inner" Could it be from my attempt to reorder the tables? I had moved table Plant from #5 to #2 (originally thinking that the filter was limited to the first two tables) --- ## Post #5 by @Susai The filters will be having only for Key Fields of the BAQ tables. --- ## Post #6 by @josecgomez Cross join means you have a screwed up join in your BAQ. --- ## Post #7 by @jdtrent I think you can only filter for fields that have a "code base" like part num, cust ID, etc. For other fields you can add options from the BAQ report writer, but then you have to go into the SSRS report and filter them. It's a little less automagic. Joe --- ## Post #8 by @josecgomez Add Plant ID to the Display List then you can filter by that, sorry I didn't realize you were using Plant_Name, just add plant List and filter that way --- ## Post #9 by @ckrusen I re-reordered the tables and the cross join went away. But the original problem still exists (as it did before the original re-ordering that created the cross join) --- ## Post #10 by @ckrusen [quote="josecgomez, post:8, topic:36881, full:true"] Add Plant ID to the Display List then you can filter by that, sorry I didn't realize you were using Plant_Name, just add plant List and filter that way [/quote] I added Plant.Plant as a displayed field, but it still doesn't show. Is it because the Plant table does not have a direct connection to the Part Table? --- ## Post #11 by @josecgomez No that shouldn't matter Plant_Plant should show up... in your BAQ add all those fields to LIKE column... --- ## Post #12 by @ckrusen New problem ... I was playing around in the BAQ designer (not the report designer), and added a Table criteria of type "specified paramter". I created a parameter, added values, etc... And it worked as expected. Then I decided to remove that table criteria. I d did so by selecting it in the lower pane, and clicking the delete button ('X'). The criteria line disappeared from the lower pane, but when I test the BAQ, I still get prompted for the criteria. How can I get rid of this? --- ## Post #13 by @ckrusen Where is this "LIKE Column" in the BAQ? In the Display Field Attributes? --- ## Post #14 by @Randy In the BAQ, open Actions - Define Parameters. --- ## Post #15 by @ckrusen I looked there. the list is empty. If add a Table Criteria of type "specified parameter", and then click the "specified" link I get: Seems that Plant_Name parameter is FUBAR'd. If I try to make a new Query Parameter (from Actions \ Define Parameters) and name it Plant_Name, it says it already exists. Yet it doesn't show in the Query Parameters window. --- ## Post #16 by @Randy Hmm, I've seen this before. I've cleared it by saving the BAQ and reopening it. Also sometimes jumping around the Analyze, Query Builder, and General tabs will get the BAQ to "refresh" and clear out lingering junk like this. Finally, check the SQL query phrase on the General tab, see if the @parameter is gone. --- ## Post #17 by @Zoher_Ali Hello, Does anyone got the answer for this question? According Epicor definition : > Only columns from the BAQ that have an **adapter** are displayed in the list. This property lets this column link to other columns to restrict the data that appears I have POHeader.PONum and Project.ProjectID in my BAQ but it is not appearing in available filter list drop down. --- ## Post #18 by @josecgomez You have to add these columns to the BAQ Like Search tab --- ## Post #19 by @Zoher_Ali Still not showing... --- ## Post #20 by @bordway [quote="Zoher_Ali, post:19, topic:36881"] not showing… [/quote] where BAQ Report doesn't list some of the expected "key" fields. I've had this happen too... seems to be intermittent ( possibly related to the order in which I've added/changed tables in the BAQ)? When a BAQ gets "out of whack" like this, I've not always been able to clear it up. i.e. I've needed to rebuild some BAQs from scratch and verify the desired filters are listed before I get too far along. Not sure what data you want returned but...here are a couple screen shots, quick example with some of the same tables and where the plant is listed in the BAQR filters. ![image|619x205](upload://dhGZVoB8585es20ah4XWOdmdppT.png) ![image|464x301](upload://f0t5JEpMXtc4b4s4ndOQoE6Rn4q.png) --- ## Post #21 by @ckrusen Do the fileds from Warehouse and PartCost not show because they are on the 3rd level? Or for some other reason? If PartCost.PartNum was an included field, would PartCost_PartNum, show in the dropdown list? --- ## Post #22 by @bordway [quote="ckrusen, post:21, topic:36881"] Or for some other reason? [/quote] Some other reason, since the PartBin fields show up at the same level as PartCost. Just a guess, but I always thought it had something to do with the way tables are chained together.. where at some point the system ignores the tables "key" fields. Probably doesn't answer your question but I still refer to this this old copy of a schema (that someone was nice enough to upload to the Yahoo site) when I'm chaining part tables together in BAQs. PartTables.pdf (59.5 KB) --- ## Post #23 by @ckrusen Nice find. I may have that printed on bronze, and hang it on my wall. --- ## Post #24 by @A.Baeisa just need to add PartLot table to this nice schema --- ## Post #25 by @bordway [quote="A.Baeisa, post:24, topic:36881"] PartLot table [/quote] Again, from the old Yahoo sites file section.. Another schema I reference from DotNet, includes the PartLot table, and more. Part.pdf (277.5 KB) --- ## Post #26 by @A.Baeisa nice one pal, that is a comprehensive one (treasure map):slight_smile: [quote="bordway, post:25, topic:36881"] from the old Yahoo sites file section [/quote] and sorry to ask, is this an old archive site or what ??? --- ## Post #27 by @ckrusen It was a members only Yahoo Group https://groups.yahoo.com/neo/groups/vantage/conversations/messages EDIT: And it looks like a few people still use it (seeing posts in October & November) --- ## Post #28 by @bordway Yes, the old Vantage group was hosted by Yahoo. All the threads were migrated to this site but the none of the files. A little dated now but still has some good stuff. https://groups.yahoo.com/neo/groups/vantage/info Not sure new accounts are being accepted, or if you'd be able to access files. --- ## Post #29 by @josecgomez Nobody is moderating over there I doubt a new account would be accepted. All the info should be here (except the files) however they are for the most part old and obsolete even the schemas would be out of date for the most part. --- ## Post #30 by @A.Baeisa @bordway @ckrusen thanks for explaining this guys. @josecgomez yes you are right, old schemas will be out of date as Bruce and Calvin mentioned on the thread, it just a start point then who ever need it can update it accordingly to serve his use. --- ## Post #31 by @bordway [quote="josecgomez, post:29, topic:36881"] schemas would be out of date [/quote] Yes, wondering if you've ever seen any schema layout sheets for E10 directly from Epicor? The I only one I saw from Epicor was a long time ago & that was for Clientele. I'm a big fan of schema layouts (and processing flow charts). --- ## Post #32 by @josecgomez There are simply way too many tables in Epicor for a proper ERD... There are a few that exist but are pretty awful Here's this one https://epidd.wcibags.com/ERD/ --- ## Post #33 by @bordway [quote="josecgomez, post:32, topic:36881"] Here’s this one [/quote] Thanks for the example. [quote="josecgomez, post:32, topic:36881"] exist but are pretty awful [/quote] Oh my.... you aren't kidding. --- ## Post #34 by @A.Baeisa infinite thanks @josecgomez, for me this is a gold mine, i agree it is awful but very valuable :slight_smile: ![image|632x379](upload://aZzbLi6xzwjIZoDJQjo4rtPiuBk.jpeg) --- ## Post #35 by @amaragni I didn't see a resolution to the original question - which is what controls who shows up in the BAQReport designer FILTER tab. Did i miss it? --- ## Post #36 by @hkeric.wci @ckrusen @amaragni See this thread for a UD Example. https://epiusers.help/t/tips-tricks-add-ud100-key1-as-filter-to-baq-report/45012 So what limits it, is has to be a field that has a LIKE property and that LIKE Property lets say Part.PartNum has to have a "context menu" configured w/ a Base Search. So you can add your own, but obviously Key fields usually have one. But lets say if you are using "Character10" and it is a PartNum field in your BAQ, you can modify the properties in the BAQ Designer (attributes) and assign a LIKE Part.PartNum property. ![2018-05-31_1248|654x486](upload://wZXKa2QkCJyYHLyifAHGJs1P4Jb.png) --- ## Post #37 by @amaragni Thank you @hkeric.wci - That works! --- **Canonical:** https://www.epiusers.help/t/baq-report-filter-field/36881 **Original content:** https://www.epiusers.help/t/baq-report-filter-field/36881