Excel Chart Updating with Pivot table

Hi All,

I'm trying to get an excel chart to update in excel services when the pivot table that it is based on is drilled into. The chart updates in the Excel environment but not the excel services. Is this possible, and if so can someone please tell me how to do this?

TIA,

Grant

[286 byte] By [GrantSwan] at [2008-2-27]

«« Templates & Enum's
»» driver update

# 1

Hi Grant
In Excel Services, caching is enabled at multiple levels for better performance. If you go the Trusted File Locations in Shared Services Provider, you will find the caching time for workbooks referring external data. I guess its around 10 minutes by default. In fact you EWA web part will prompt you saying Refresh All Connections, when the data at the backend has been updated. Just check out the cache timing.

Thanks and Regards
Guruprasad Karnik

gpk2005 at 2007-9-12 >

top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...

# 2

Hi Guruprasad,

If i understand you correctly, and believe me, its possible i don't then recalculating the Excel web part containing the chart once i have drilled into the Pivot Table web part should update the chart and allow the drill down to be seen. I.e. when i drill into a year on the pivot table i get taken to Q1, Q2, Q3 and Q4. I'd like the given year on the chart to be drilled down to this level also.

This doesn't seem to work however. The chart remains the same as before the drilll down.

The cache Auto and Manual refresh are both set to 300.

Any further suggestions are welcomed.

Thanks,

Grant

GrantSwan at 2007-9-12 >

# 3

You need to have both the PivotTable *and* the chart in the same web part for this to work.

Since each web part opens it's own session of the workbook, changes to one will not affect the other (if you have one web part displaying the pivot and another web part displaying the workbook). From your description I'm assuming you have them on separate parts correct?

The only way to do what you describe is having the same web part display both the Pivot and the Chart.

- Luis

LuisBitencourt-Emilio-MSFT at 2007-9-12 >

# 4

Hi Luis,

I have just recieved the alert that an answer has been posted. Your answer looks as though it will certainly sort my problem. I do have one question though, How do i include multiple objects in the same web part?

Cheers,

Grant

GrantSwan at 2007-9-12 >

# 5

Hi Grant
If you want to display multiple objects say pivot table and a chart in the same web part, you can publish those in Excel services and it appears as views in the web part. However if you have a requirement where in you need to display say the pivot table and the associated chart in the same view, you can select the range of cells-pivot table and the chart and publish it as a named range in Excel Services. This named range appears a view in the Excel Web Access web part. I hope this answers your query.

Thanks
Guruprasad Karnik

gpk2005 at 2007-9-12 >

# 6

Grant,

I am trying to have combination chart with a Bar & Line on Primrary axis and another Bar & line to Secondary axis

I am getting both the Line going the right way but my Bars are coming top of each other....Is there way to seprate the bars.

SonyJP at 2007-9-12 >

SharePoint Products and Technologies

SharePoint - Excel Services

SharePoint Products and Technologies Hot Topic

SharePoint Products and Technologies New Topic

SharePoint Products and Technologies

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech