Dynamically change data source in AO Workbook

[bdumbell]

Hi all,

I'm hoping there is a way to dynamically change the data source used by an Analysis for Office crosstab similar to how a data source can be dynamically assigned to a component in Design Studio? I've had a look through the AO documentation and cannot find a macro or formula driven way to do this.

Our scenario is a workbook where we'd like to dynamically display particular measures in the report columns based on a selection the user makes (say by them clicking a button or choosing a value in a dropdown). One option is a macro based on SAPSetFilter but that will require quite a bit of code to set the appropriate measures based on the selection made and will therefore really only be maintainable by IT.

A simpler and more maintainable way (potentially even by business analysts themselves) would seem to be to define query views and then based on the user's selection assign the appropriate view as the crosstab's data source. With not being able to find a macro command for doing this I'm currently considering having each view embedded in it's own worksheet & hiding/unhiding them through a macro.

Really appreciate any suggestions that people can offer. Using AO 2.4.

Thanks, Blair