By using this website, you agree to our Terms of Use (click here)
I have a report I am working on that is generating duplicate records. I finally traced it down to the join to the EPEmployeeRate table. The table has two records for each employee with different effective dates for the rates.
Does anyone know how to get the record with the most recent effective date linked? Or the older records filtered out? I can't figure out how to get what is the equivalent of a SQL subquery to work in the Report Designer.
At first I was thinking that you could do something like this:
But then realized that of course that won't work.
You could do something like having a separate join for each Month or Quarter, but that's pretty messy with a lot of joins and it assumes that employees don't have more than one effective date in a Month or Quarter.
So, I'm stumped. I'm not sure you can accomplish this without doing a SQL View using a subquery, then connecting it to a DAC. The crux of the problem is that you need to read from more than one row at a time and a SQL View DAC is the only way I can think to do it.
I just wanted to say that this Active Relation join saved my mind today. I knew you could multiple the same tables with an Attribute ID, but I could get the last part of the join i.e CSAnswers.AttributeID='xxxx'
LYW
Hey, so even though we weren't able to figure out the original problem, at least this Discussion Topic was able to help with your scenario. That makes me happy 🙂