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.
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.
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.