Hide-and-Seek with expressions in SSRS Reports

This will be a quickie as I’m posting from my tablet for the first time on this site and it is just before going to bed. It’s about an issue I came across with an SSRS report this evening. It was a perfectly running report in production for over a year. The report’s layout has a dashboard-like feel with a summarised tablix of budgets and expenses representing my client’s business expenditure. The highlight of the summary was a Gauge Panel to show graphical representation of the data in every row. I had to modify the dataset dsExpenses ( which contained the results of an MDX query) to reflect the changes I had made to the cube from which the report was generated. I sucessfully ran the new dataset’s MDX query from the dataset’s query designer and changed all references in the report’s tablix cells to use the new fields from the newly modified dataset dsExpenses. One of the fields that was changed in dsExpenses was Payment_Amount which is now referred to as Payment_Amount_Programs.

I changed all references in the summary tablix from Payment_Amount to Payment_Amount_Programs manually by checking on each cell’s properties. I avoided modifying the report’s code as there were other tables that had other datasets with the same field name Payment_Amount. After carefully checking on each cell’s properties and values, I ran a quick build of the report and to my surprise the Gauge Panel’s background colour expression was thrown on the error report as it still had references to the old Payment_Amount. I knew, for sure, that I had checked the background colour expression for any outdated references. But when I checked the report’s xml code, I found that there was an expression hiding under the gauge data series’ back colour. I changed the expression to refer to Payment_Amount_Programs instead of Payment_Amount and the report ran successfully. This was a good lesson learnt the hard way to always check for expressions hiding in the code holding old references after a dataset is modified.

Posted to The No-Brainer Lab using Android