We have a report that lists employee absences compared against the total number of employees. The issue is that the total differs between when the BAQ is run, and when the report is run. Using these same parameters…
The BAQ returns:
The Report returns:
My best guess is that the Report is not evaluating these criteria correctly.
I think it is ignoring the criteria involving the UD fields, would anyone know why that is, or any other ideas of why there’s such a big difference?
You need to provide a screen shot that shows the end parenthesis field. Without that, we can’t determine what exactly you are doing.
Try putting parenthesis around the first 3 lines. Add them to what is already there.
Are you multi company?
Are you grouping/summing anything in the report?
No not multi-company, and I’m not summing anything in the report itself. There is a group on the report, but the incorrect result was the same before I added the group as it is after.
Check the output of the report tables and see if you have a join that is duplicating lines in the report.
Sorry, what do you mean by that? Where can I find this output?
When you run an SSRS report, it creates the tables in the Prod102500_Reports database (or something similar to that name). Every Table in the RDD gets output to the database. Run the report and set the Archive Period to a day. Then you can grab the GUID in the report monitor and query the DB to see what the output is.
Ok I found it all. No I don’t see any duplicated lines in here, I get the same number of lines I get from the BAQ, where the employee count is right. In this report result set the employee count is still wrong though.
I really think the report is evaluating the conditions on the hire date and termination date incorrectly. Maybe a slight syntax difference between how Epicor views the SQL and how SSRS does?
I’ve realized that the report is ignoring my parameters. Because it’s a BAQ report, the parameters in the BAQ designer aren’t the same as the parameters in the report. The report is using option fields, but because the employee count is done based on the BAQ parameters, it’s always including more in the report than the BAQ. Now I’m just going to look into ways to replicate the count logic in the SSRS itself.
Did you leave the To Date empty when you ran that?
I think you need to check the criteria, set on BAQ report designer, if they are not consistent with the criteria you have on BAQ , that will cause the difference, that is my guess.
You can do this with a “Report Data Definition” report (based on your same BAQ) instead of a “BAQ Report” report. It’s not as swift to churn out as the BAQ report, but it’s really not bad and will handle parameters.
Yeah that’s probably what I’ll have to end up doing. If I can avoid that I would. Really all I need to do is find a way to count employees that meet certain criteria in the SSRS itself, but aggregates are hard to work with in SSRS it seems.
I have found that when I run into issues with aggregates, it is because I have created a bad join either in my BAQ or the RDD. Once I clear up the mistake they work fine.
