Hi all,
Not your typical PowerPivot question I'd imagine...
I have an existing workbook that was built some time back and contains four worksheets, each of which contains a single (traditional) Pivot Table. In each case the Pivot Table has a query defined that populates the field list however the Pivot Table layout itself is blank. The idea is that "power users" (no pun intended) could do their own ad-hoc data analysis by dragging and dropping the fields into the layout as required. The reason that there are four sheets is not due to any data source limitation but rather that the data sets are completely independent of each other - for example one is from our Inventory system and another is from our HR system - there are no relationships.
Having discovered PowerPivot I decided it would be nice to take advantage of some of its features. For example the existing workbook is a shade under 100MB but with PowerPivot's compression this comes down to 31MB. Also the native inclusion of slicers in the layout will be something the users will love.
The one problem I'm facing is that having defined my four queries in the PowerPivot Window, any Pivot Tables I generate contain all four data sources in the PowerPivot Field List. Although this is a great feature (and all I could find mentioned when trying to Google a solution), in this particular case I don't want it! I can collapse irrelevant data sources but I just know that users are going to expand multiple branches and drag fields from the different (unrelated) data sources into the layout and get meaningless results.
So my question is, can I either:
The only solution I could think of was to split them into separate Workbooks but I'd rather not move away from the model people are familiar with if possible.
Thanks in advance for any suggestions.
Wayne Ivory
Not your typical PowerPivot question I'd imagine...
I have an existing workbook that was built some time back and contains four worksheets, each of which contains a single (traditional) Pivot Table. In each case the Pivot Table has a query defined that populates the field list however the Pivot Table layout itself is blank. The idea is that "power users" (no pun intended) could do their own ad-hoc data analysis by dragging and dropping the fields into the layout as required. The reason that there are four sheets is not due to any data source limitation but rather that the data sets are completely independent of each other - for example one is from our Inventory system and another is from our HR system - there are no relationships.
Having discovered PowerPivot I decided it would be nice to take advantage of some of its features. For example the existing workbook is a shade under 100MB but with PowerPivot's compression this comes down to 31MB. Also the native inclusion of slicers in the layout will be something the users will love.
The one problem I'm facing is that having defined my four queries in the PowerPivot Window, any Pivot Tables I generate contain all four data sources in the PowerPivot Field List. Although this is a great feature (and all I could find mentioned when trying to Google a solution), in this particular case I don't want it! I can collapse irrelevant data sources but I just know that users are going to expand multiple branches and drag fields from the different (unrelated) data sources into the layout and get meaningless results.
So my question is, can I either:
- Define a separate PowerPivot query per Worksheet?
- Hide data source branches that I don't want to appear on each of the Pivot Tables (even by using a bit of one-off VBA if necessary)?
The only solution I could think of was to split them into separate Workbooks but I'd rather not move away from the model people are familiar with if possible.
Thanks in advance for any suggestions.
Wayne Ivory
