Changing pivot filter causes chart type to change! Why?
 

Hi!
This is a post about a Problem in Excel 2007.
I have a Pivot&Chart thats datasource is a Cube (SSAS).

The Chart displays some values as a columns and another as a line.


The Pivot has got a Filter on the Pivot-Columns.
If I change the Filter, Excel updates the chart values. Thats Ok.

Here the Pivot Filter set to show Values of B and C

| _______________A____ Value A as Line
| ____
| ___| |
| | | B | Value B and C as Column
| | C | |
+-----+- ---+-----+------------




Problem:
Excel also changes the chart type to use only columns if I change the
Pivot-Filter.
It is two columns now. But Value A shall be still a Line!


Filter changed to only show B

| ____
| ____| |
| | | |
| | B | A | Value B
as Column and Value A as Column. But Value A shall be a line!
| | | |
+-----------+-----+------+------


Question:
How can I prevent the Chart Type to be changed if I change the pivot filter???

In Excel we used a Macro, but Macros dont work in Excel Services

Answer From Another Forum
 

Thomas Ramel answered to this in a german cross post on
http://www.office-loesung.de/ftopic255691_0_0_asc.php

That the chart is repainted and the standard diagram-type is used to do this.

Either you redefine the standard diagram (works in most cases)
or you use a macro. SheetPivotTableUpdate is the proper event to catch.

But in our case we cant use macros because we use sharepoint excel services.

Update in my blog
 

In case someone stumbles upon this post, I put screenshots and a more
detailed description on my blog-page
http://www.duelec.de/blog/?p=134