By using this website, you agree to our Terms of Use (click here)
I’m have seen this used, but am not comfortable using this calc to accumulate YTD costs in Report Designer:
=sum(iif([FinPeriod.FinPeriodID] <= [@FinPeriod] and [FinPeriod.FinYear] = right([@FinPeriod],4),[Cost.TotalTargetAmount],0))
It does not give consistent results, which I believe is due to the <= acting on a string. Looking for opinions on the best way to aggregate YTD and ITD values in a report designer report?
I would be curious to see what kind of SQL this gets turned into when the report runs. You can see this by going to Tools -> Trace after the report is run.
I didn't remember that Sum could act on this kind of formula. I thought that it could only act on values that exist in the Detail section.
If text is indeed the only problem, you could use the CInt() formula to turn the text into integers.