Today a colleague were modifying a Reporting Services 2008 report to add some new measures to a tablix. He opened the query designer up, added his new measures, tested the query which ran fine, saved it and then proceeded to modify the cells in the tablix.
After modifying the tablix he ran the report in BIDS and we noticed that he was getting NULL values for one of the measures. We opened the dataset, confirmed the date (which was our only parameter) and tested the query. We tested the query again in the designer and saw that our measure was returning results. We closed the dataset down and tested the report. Again we saw that our report was returning NULL values for the measure. So I copied the query into management studio and ran it. It returned the correct results as expected.
At this point we were really getting fustrated. We recreated the dataset, created a brand new tablix and nothing was working. I just happen to look at the query again in Management Studio when I realized that the measure he was having issues with was in the query twice. Once I removed one of the instances of the measure, the report worked as expected.
So the lesson I learned today was to always double check my query to make sure there aren’t any duplicate measures. It appears that the conflict with each other in Reporting Services will end up showing a NULL value.