I spent about an hour last night working to solve this stupid problem with a report so I thought I’d share it with you guys. I’ve had this problem before, but for some reason I never remember it until I’ve had to work through it for a long time.
The problem is that I was trying to put a simple graph on the page and I often like to work with really simple test data. So I chose a column that had the same value all the way down which would give me a straight line on my graph. The problem is that I had 3 spikes. There shouldn’t be any spikes; the data’s all the same.
I laid the data out into a table in the report and the data was certainly all the same. Then I ran the report query in SSMS and verified again that the column only had a single value in it. So where were the spikes coming from? So as it turns out the problem was with both my query and the collections. I was collecting disk data every 10mins from a server and putting it into a table. What I wanted was to graph today’s data by time. And what I forgot to do was to limit the query to just today. And since it collects the data every day at more or less the same times, there were some times from yesterday that were the same as the collection today, so SSRS was adding the values together. And once I limited it to just today, then the spikes went away.
I consider myself lucky that I only spent an hour on this. But it just goes to show that when data’s involved, debugging instantly gets harder because you tend to make assumptions one way or another.