SSAS/SSRS remove parameter from cube report destroys report

Question

Group, We built a data cube using SSAS and are now building SSRS reports off of that cube. Not sure if anyone has come across this, but when you build the report using the wizard and include parameters all looks fine. However if you are in the report after the wizard is compete, and you decide you want to remove one of the parameters you created it debunks the report and the only way to get it back is to re-create the whole report.

Any way you can remove or add parameters after the initial build without destroying your report?

Thanks in advance for the help! I love this forumn!

Answer 1

If you look at the hidden datasets created for each parameter, each one has a query which depends on some of the other parameters, in a cascading fashion. Unless you delete the very last parameter, the other parameters won't work.

You can fix this by manually editing the parameter dataset queries can updating them not to use the parameter you want to delete.

The main query also uses the parameters in a nested FROM (... (... (... fashion. You need to update that so that it doesn't constrain itself to that parameter. Then there is a parameters button on top of the query builder which allows you to remove the reference to that parameter in the query.

I think if you remove the parameters from the wizard, it should update all the other parameters and the query to work correctly. This is probably the easiest route. All of your report objects should remain functional.