SSRS , BAQ Result & where clause

Epicor ERP 10
, , , ,
1

I am running a BAQ report with a from date and a to date (OrderRel_ApprovedByEngDate_c) and i am getting different results compare to SQL, the reason is because epicor doesn’t apply my where clause in SQL only after the query runs and I have all the result then it will apply the filters used in the BAQ Report (the two dates) .When I am in the query editor and I set the dates this works just fine.
My problem is cause my PERCENTILE_CONT function is applied to the entirety of the result which is not what i want.

Does someone have this same issue? Is there an Epicor fixed? I also open a ticket in Epicor.
Thanks a lot,

select
[ShopDrawingKPI].[OrderDtl_Company] as [ShopDrawingKPI_OrderDtl_Company],
[ShopDrawingKPI].[OrderDtl_OrderNum] as [ShopDrawingKPI_OrderDtl_OrderNum],
[ShopDrawingKPI].[OrderDtl_OrderLine] as [ShopDrawingKPI_OrderDtl_OrderLine],
[ShopDrawingKPI].[OrderDtl_PartNum] as [ShopDrawingKPI_OrderDtl_PartNum],
[ShopDrawingKPI].[OrderDtl_LineDesc] as [ShopDrawingKPI_OrderDtl_LineDesc],
[ShopDrawingKPI].[SysUserFile_UserID] as [ShopDrawingKPI_SysUserFile_UserID],
[ShopDrawingKPI].[SysUserFile_Name] as [ShopDrawingKPI_SysUserFile_Name],
[ShopDrawingKPI].[UDCodes_CodeID] as [ShopDrawingKPI_UDCodes_CodeID],
[ShopDrawingKPI].[UDCodes_CodeDesc] as [ShopDrawingKPI_UDCodes_CodeDesc],
[ShopDrawingKPI].[ReasonUDCodes_CodeID] as [ShopDrawingKPI_ReasonUDCodes_CodeID],
[ShopDrawingKPI].[ReasonUDCodes_CodeDesc] as [ShopDrawingKPI_ReasonUDCodes_CodeDesc],
[ShopDrawingKPI].[PriorityUDCodes_CodeID] as [ShopDrawingKPI_PriorityUDCodes_CodeID],
[ShopDrawingKPI].[PriorityUDCodes_CodeDesc] as [ShopDrawingKPI_PriorityUDCodes_CodeDesc],
[ShopDrawingKPI].[OrderRel_ReqShopDrawingDate_c] as [ShopDrawingKPI_OrderRel_ReqShopDrawingDate_c],
[ShopDrawingKPI].[OrderRel_ApprovedByEngDate_c] as [ShopDrawingKPI_OrderRel_ApprovedByEngDate_c],
[ShopDrawingKPI].[Calculated_RecordType] as [ShopDrawingKPI_Calculated_RecordType],
[ShopDrawingKPI].[Calculated_ReqSDvsApprEng_DayDiff] as [ShopDrawingKPI_Calculated_ReqSDvsApprEng_DayDiff],
(PERCENTILE_CONT(0.5) within group(order by ShopDrawingKPI.Calculated_ReqSDvsApprEng_DayDiff)
over (partition by ShopDrawingKPI.SysUserFile_UserID,ShopDrawingKPI.PriorityUDCodes_CodeID)) as [Calculated_Median]
from dbo.ShopDrawingKPI as ShopDrawingKPI
where
(ShopDrawingKPI.OrderRel_ApprovedByEngDate_c >= ‘2019-01-01’ and ShopDrawingKPI.OrderRel_ApprovedByEngDate_c <= ‘2019-11-26’)

2

Have you tried making a sub query that only returns the data within the range, to your top level query?

3

I think it will be same principle, the sub query will run for the all records then where clause will be applied, also I cannot know in my query what filter were applied .

4

What if you use Baq parameters instead of options in baq report.

Vinay Kamboj

5

Do BAQ parameters work with BAQ Reports?