However, the solution for that one has me stumped. They suggest add a Criteria mapping on the RDD however its all grayed out for me. I’m not even able to duplicate the Report on RDD because it says “record not found”. Forgive me as I’ve only used Report Data Definition once before.
Additionally, the options field directly on BAQ Report Designer isn’t going to work for me in this scenario which is why I need to use the parameter directly on the BAQ itself.
I have a calculated field that returns inaccurate results if I run it for every job in the system and use this method. Oddly enough, the BAQ returns it correctly but it shows up as a completely different value when the SSRS Report generates which is why I want to pass the Job Num from the BAQ instead.
You can just remove the parameter from your BAQ, and instead add it in as a filter on your BAQ report. The filtering will still be done server side.
In your example, leave the parameter and filter on JobNum out of the BAQ, and add a filter on the JobHead_JobNum field in the BAQ Report Designer window.
@HLalumiere If I use the filter option within BAQ Report, I get inaccurate results on a calculated field which is why I need to filtering done directly on the BAQ itself but specified at the time the report is ran. I suspect its because all jobs in the system have to get returned first.
Thank you Jason, I figured I’d have to use RDD made from scratch as compared to BAQ Report Designer. I did look at the ICE Tools guide but came up short of any explanation on how to make one. I’m going to get the Implementation guide as you suggest and see if I can get a RDD built. Thanks again!
A filter works server side just like an additional WHERE clause. It should work exactly the same.
EDIT: I think i understand, you have top-level calculated fields that somehow aren’t being filtered… Try to put your query with the calculated fields in a subquery, instead, and do a select * on the top level query. That should fix your issue.
My calculated field is for a carton count, this report is essentially a shipping label.
Here’s the syntax
Blockquote
CONCAT(ROW_NUMBER() OVER(PARTITION BY JobProd.Partnum ORDER BY JobHead.JobNum DESC), ’ of ', CAST(JobProd.ProdQty as INT))
So regardless if I have a parameter on Job Number at the BAQ level or not, the BAQ itself returns correct results ie. 1 of 3, etc… However, when I use the Filter on BAQ Report Designer, the carton count will say a crazy number like 1376 of 3. I suspect its because its counting cartons for all jobs in the system.
When I specify the Job Number on the BAQ then use the same Job # on the BAQ Filter you’re suggesting it works fine, 1 of 3 etc…
So that why I wanted to go down the route of making the parameter on the BAQ dynamic to be the same as what is manually entered on the BAQ Report Designer Filter.
This BAQ only has a Top Level, I’ll try and move this field to a sub query and see if I get the same results. Thanks @HLalumiere
It’s possible it’s being a bit wonky, I tend to use group by more than partition by, but in any case, you should be able in the BAQ editor to just create a new subquery, then in its options set it as top level. Then go back to your original subquery, and set that one to an inner subquery instead. In the new top level query, add in your original one, and select *. The reason why I think this will fix your issue is that it flattens the query output for Epicor, but at the same time when Epicor adds in the filter and compiles the expression, Entity Framework and SQL Server should be plenty smart enough to optimize.
Also, shouldn’t it be like this instead?
CONCAT(ROW_NUMBER() OVER(PARTITION BY JobProd.Partnum, JobHead.JobNum ORDER BY JobHead.JobNum DESC), ’ of ', CAST(JobProd.ProdQty as INT))
I’ll have to test out that Subquery method here in a bit when I have an opportunity. As for the syntax, it been working fine for me directly on the BAQ results. I’ll definitely take a look at the suggested syntax as well and see if I over looked something but here’s an example of the output with my current calculated field.
Yes, the issue is that because you had a parameter for the jobnum, you only ever had one jobnum shown on screen. Now the dataset can have many jobs and many parts. Just including the JobHead.JobNum field in the PARTITION BY should fix this for you.
I’ll be damned, that was it. After fixing the syntax the BAQ Report Designer Filter worked. The Calculated field shows the correct results which match the BAQ. Thanks @HLalumiere !
