Everything below is from my experience working with SSRS 2005 and MDX queries using Visual Studio 2005.
Most of the errors generated by the Report Server at development and run time are self explanatory and are easy to troubleshoot, but not all of them. I had to spend a lot of time troubleshooting the problems I listed here and I hope it will save you some time.
1. The query builder is not working properly without SQL 2005 SP2.
The problem: You built your report against OLAP cube using the VS 2005 query builder, if you try to change anything in the connection string (actually even if you change nothing, just click edit and then save) the built query will disappear and you have to build it again from scratch. By design you can change the connection to a cube with the same structure and the query should work.
The solution: Installing SQL 2005 SP2 fixed the problem.
It took more than one day for me to figure it out; I couldn’t find anything on the internet about the problem, looks like I was the only one who experienced it.
2. Changing one of filters created in query builder makes all other filters generate errors at run time.
The problem: You built your report against OLAP cube using the query builder, you created several filters, if you change or delete one of the filters (only changing the last filter is ok), you got one of the errors:
“An error occurred during local report processing. The definition of the report ‘…’.is invalid. The Field expression for the data set “….” refers to the field ‘ParameterLevel’. Report item expressions can only refer to fields current data set scope or, if inside an aggregate, the specified data set scope”.
“An error occurred during local report processing. The definition of the report ‘ ‘ is invalid. The value expression for the query parameter ‘ ‘ refers to a non-existing report parameter”
, additionally you got unused queries in the queries drop down list and unused report parameters (see my blog Report Project under the hood).
There are several possible solutions:
· You can delete all filters below changed/deleted filter; delete all queries and all report parameters associated with the deleted filters. After that you will have to recreate all of the filters again.
· You can manually change the dataset parameters in the queries created for the filters below the changed/deleted filter.
3. For a report created by wizard a Table control is created inside a List control, when you try to use the feature “Header should remain visible while scrolling” you get an error at run time.
The problem: You built your report using the wizard, the wizard created a report, a list control and a table control inside a list control, now if you try to set “Header should remain visible while scrolling” by checking the checkbox in the table properties you got an error: “The table ‘table1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.”
The solution: The wizard creates Table control inside List control if you created multipage report. If you want to convert the multipage report to a single page report to use the above feature remove your Table control from the List control. The parent element of your table should be Body (not List).
