An ACTUAL Fix For TFS SSRS Error Query Execution Failed For Data Set
After doing a TFS migration between 2005 – 2008/2010, you may notice that there is a problem with your SSRS displaying reports. Most frequently this will occur when a service pack is applied. While some, such as the Velocity report, just won’t display data, others like those dealing with bugs will lead to the following SSRS error being reported:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set ‘PriorityParam’. (rsErrorExecutingCommand)
Query (19, 2) The ALLMEMBERS function expects a hierarchy expression for the argument. A member expression was used.
The data set parameter in the exception return will vary depending on the report you are doing, but it is a consistent error regardless.
Now at first glance, this is post migration / potentially post service pack, so it would lead one to believe that for some reason the .rdl files didn’t get updated to the new version. Since I didn’t have a ton of projects I unpackaged them out of the fitting.stp files, and tried manually uploading them in the Report Manager. This had no effect.
After Googling my butt off, trying all these ambiguous fixes, and trying to rebuild the TFS warehouse with 50,000 different permutations on the available switches, I sat down and found the fix myself.
Where this problem crops up is for some reason the TBL_Database table in the TfsIntegration database has a remote server, a.k.a your source TFS server for migration, name for SSAS. Now why the hell is this? Well, it’s because the MSIProperty.ini file has an entry for the same remote SSAS instance and this can be picked up and employed by service packs and other updates. When the warehouse controller starts the adapters, the process attempt to hit the remote server, which will causes the reports to fail. Once you change the SSAS server value in MSIProperty.ini and the server parameter inside TBL_Database of the TFSIntegration table, the warehouse controller will be able to run the adapters successfully.
Once the adapters are run the reports will start showing up successfully.