none
Excel Chart Updating with Pivot table

    Question

  • 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
    Tuesday, March 06, 2007 9:29 AM

Answers

  • 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

    Monday, March 19, 2007 9:33 PM
  • 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
    Sunday, April 01, 2007 3:59 PM

All replies

  • 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
    Thursday, March 08, 2007 11:11 AM
  • 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
    Friday, March 09, 2007 3:52 PM
  • 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

    Monday, March 19, 2007 9:33 PM
  • 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

    Wednesday, March 28, 2007 8:22 AM
  • 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
    Sunday, April 01, 2007 3:59 PM
  • 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.

     

     

     

    Tuesday, August 07, 2007 4:13 PM
  • Hi Grant

    I have done following steps to resolve the issue, hope it will work for you as well:

    1) I have a Pivot Table and Pivot Chart

    2) Placed the Pivot Chart infront of Pivot Table, so that only filter filed of pivot table is shown and rest of the pivot table hides behind the pivot chart

    3) select the region of cells including filter fields and pivot chart and name it as a named range

    4) Publish only the named range to sharepoint

    5) now call this named range into Excel Web Access

    When select pivot table filter filed chart changes and pivot table does not display because it is behind the pivot chart.


    Hussain Ali
    • Proposed as answer by StaciS Thursday, October 24, 2013 4:52 PM
    Thursday, October 01, 2009 10:23 AM