none
Setting style to a collapsed cell in pivottable, also changes the style for the child items when show details RRS feed

  • Question

  • Hi,

    I have a pivottable, say i have Month and Week fields on Column. Have other fields on Row and the Data fields (say Cost and Profit) on Row as well.

    When i collapse the Month column (say JAN), so that the Weeks within JAN get collapsed and I see subtotal values.

    Now, i set the style of Cost@JAN cell (by getting the range and doing range.Style = "my_custom_style")

    Now, when i expand the JAN field (showdetails or click on the "+" button), I see that the child items (cells corresponding to Cost@Weeks under JAN) inherited the style from parent.

    Is it expected ?

    I am doing it through code in my excel addin (office 2013)

    I tried creating a dummy pivottable and setting the style manually, but i didnt see child items style getting changed. So, I guess behavior is different when done through API ?

    I am trying out since 2 days on this bug so wanted to check if its in my code or is expected behavior ?

    Would be great if someone can help me on this.

    Monday, August 18, 2014 8:25 PM

All replies

  • Hi,

    According to your description, I try to reproduce your issue with the code below in my Excel 2013 add-in.

    In the code, I collapse the Month field "JAN" in the Pivot Table and then set the Style property of the cell "B7" which is in Cost@JAN. After expanding the Month field "JAN", I find only the subtotal cell of Month "JAN" and Cost has the style I set. The child items (cells corresponding to Cost@Weeks under JAN) doesn't inherited the style from parent.

    Since I failed to reproduce your issue, I think your result is not expected and it may be related to how you set the Style property of specific range and the structure of your Pivot Table. Would you mind sharing your sample code and your sample workbook through OneDrive for us to troubleshoot?

    private void ChangeStypleInPivotTable()
    {
        Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        Excel.Worksheet ws = wb.ActiveSheet;
        Excel.PivotTable pt = ws.PivotTables("PivotTable1");
        Excel.PivotField pf = pt.PivotFields("Month");
        Excel.PivotItem pi = pf.PivotItems("JAN");
        pi.ShowDetail = false;
        ws.Range["B7"].Style = "Good";
        pi.ShowDetail = true;
    }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 19, 2014 9:19 AM
    Moderator
  • Hi Luna,

    Thanks for the response. 

    I get the range by calling pivotTable.GetPivotData(), like

    Range range =  pivotTable.GetPivotData(getPivotDataParameters.ToArray())

    range.Style = "my_custom_style"

    Will that make any difference ?

    UPDATE:

    I managed to reproduce the issue creating a pivottable. Basically its related to layout.

    here is the link to my sample excel sample excel

    Steps to repro:

    - download the excel

    - collapse column "mon1", click on first data cell "5", select a style from "Styles" group in the Home tab, then expand "mon1". All child cells get styled


    • Edited by maninder lall Tuesday, August 19, 2014 12:35 PM link to sample excel
    Tuesday, August 19, 2014 11:54 AM
  • Hi,

    After downloading and reviewing your sample workbook, I find you set much properties of layout in the Pivot Table. It is not a common Pivot Table layout and the issue may be related to the layout.

    After much testing, it's hard for me to create a same Pivot Table as yours based on your source data as followed. In my Pivot Table, the list such as "Sum of m1" is in the column. I cannot put it into the row field as yours. In addition, since the "prod2" is one child of the "cat2", I think it's better to make it display under the "cat2" instead of in the same line.

    With my Pivot Table, the range style of the child items won't inherited the style from parent as I said in my first reply, so I suggest you referring to it. I have uploaded it to OneDrive: http://1drv.ms/1pL5o7M.

    If you don't think it can help you, since the issue is more related to the layout of the Pivot Table instead of Excel automation, I suggest you posting in Excel IT pro forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 20, 2014 9:58 AM
    Moderator
  • Hi Luna,

    Thanks again for the response.

    here is the screen-shot of my layout. 

    Prod2 is child of cat1, and this is the requirement. We create pivottable and set the layout as per some settings and here the data fields are on row.

    I will try posting it to  Excel IT pro forum.

    Thanks for the help Luna.

    Wednesday, August 20, 2014 12:52 PM
  • Posted to Excel IT pro forum

    Thursday, August 21, 2014 9:55 AM