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.
You can only filter on fields you are displaying on the bAQ>
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.WarehouseCodeWhat’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)
The filters will be having only for Key Fields of the BAQ tables.
Cross join means you have a screwed up join in your BAQ.
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
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
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)
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?
No that shouldn’t matter Plant_Plant should show up… in your BAQ add all those fields to LIKE column…
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?
Where is this “LIKE Column” in the BAQ?
In the Display Field Attributes?
In the BAQ, open Actions - Define Parameters.
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.
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.
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.
You have to add these columns to the BAQ Like Search tab
Still not showing…
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.
