How to keep Analysis for Office workbooks changing the datasource query

[javierc1]

Hi all,

     We have many customers using complex Analysis for Office workbooks created by themselves, however they were using 3.x queries and now we need to replace the queries with 7.x queries with a different technical name.

    We tried by changing the datasource in "components", here we change the 3.x query for the new 7.x query. The problem is that the workbook layout is changed to the initial layout of the query, the workbook layouit saved by the user changes.

   Do you know a way to chenge the query behind keeping the layout of the workbook?

Thanks in advance

[Former Member · Accepted solution]

It sounds like you've got the original Datasource set to not Reset on open, so that user configurations are preserved. I don't think you'll be able to substitute the query behind the DataSource unless you replace the original query, so that the new query has the same technical name as the original query.

I think, as long as the new query's technical name is the same as the old query's, Analysis won't know that it's looking at a "different" query. Of course, if the new query has differing dimensions/members/measures, users might get unexpected crosstab layouts, unexpected filter results, or even errors/warnings.

The same issues would occur, even if you were making small changes to a query (as opposed to replacing a query entirely), if the Datasource isn't set to reset on open. I prefer to force a reset on open, just to make sure there aren't any caching issues, and so that users always have the full set of measures available. If users need to preserve customisation, you could preserve and reapply those customisations in VBA, after the initial refresh.

[javierc1 · Accepted solution]

Hi Andrew,

   I'm very sorry for the late reply, but we were testing many options, and as you say the workbook layout doesn't change if we keep the same technical name, the version can change from 3.x to 7.x.

However we cannot implement it because we must change the technical name.

Anyway thanks for the answer it was really useful.

Kind Regards