SQLTeam.com | Weblogs | Forums

Weird Report Bug - SumLookup(LookupSet(...)) Related?


Can anyone tell me what is wrong with this report? It is a very simple report, but I am using LookupSet and the custom code SumLookup to get some totals across the date range by filtering on the dataset.

RDL and Example Images

For some reason, it is not rendering correctly and is duplicating an Activity Code Name, as well as the ratio. I don’t believe I have ever seen a duplication in the actual numbers being retrieved via the Lookup (except in the ratio calculation). I have tried rebuilding the table, and even the entire report. It does this in Visual Studio, as well as Report Manager. The issue is intermittent and may not occur every time. It seems to only occur when a single date is selected, rather than a date range.

I have hard-coded some data on the report datasets, so a connection to the real data source is not necessary.

Has anyone ever seen this before?

I am super frustrated because we use Lookup, LookupSet, and the SumLookup custom code on several reports and now I don’t know if I can trust it anywhere. I have not seen this behavior before, but that does not mean that it is not occurring in Production under certain circumstances, and we just have not noticed it.


Ok, so after some further testing (and some help from a coworker), I have discovered that this is apparently caused by the fact that I am using LookupSet to look back on the same dataset that feeds the report object and I guess that freaks it out for some reason. Wow, that's annoying. The only solution that we can come up with is to duplicate the dataset for the lookup (or rework the base queries somehow). I have 18 reports that I now have to go out and investigate and fix due to the possibility of this bug appearing. I have no idea if this also affects the plain Lookup function.