I am using a ssas CUBE as the datasource for creating reports in Excel 2007. There are 3 tabs in a sheet each having separate connection from the same cube. There should be only one set of prompts for all the 3 tabs. If a user selects values in the promts/parameters in the first tab,it should get applied for all the tabs.
You could change the required pivot table field selections in multiple sheets from the values in the first sheet using some VBA code. Assuming that the values chosen are in cell ranges in the first sheet, it would be a good idea to first name the ranges with the chosen values so that they can referenced easily. If the values are within forms such as list boxes, you can use the MATCH and INDEX functions to return the selected value in a cell. The VBA code then looks something as follows. VBA Code:
Dim cellval1 As Variant cellval1 = Range("namedrange1").Value Sheets("sheetname1").PivotTables("pivottablename").PivotFields("pivotfieldname") = cellval1
It may be a good idea to first record a macro to change the pivot tables in order to get the correct code for to chnage the selection in each pivot table.
