Changing pivot filter causes chart type to change! Why?

[DenizDuelec]

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

 

[DenizDuelec]

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.

 

[DenizDuelec]

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