Different Pivot table drops totals

Question

I'm using the below code block to pass in a pivot table along with some settings to loop through the measures and add differences where needed. The output is dropping the totals. Grand total, column total and row total are all returning null. Is there a config setting I am missing?

private static IPivotTable getDiffPivotTable(IPivotTable pvtTbl, List<bool> showDifference, List<bool> isPercent, List<string> direction)
        {
            DifferencePivotTable diffPvt;
            IPivotTable output = pvtTbl;

            for (int i = 0; i < showDifference.Count; i++)
            {
                if (showDifference[i])
                {
                    switch (direction[i])
                    {
                        case "previousRow":
                            {
                                diffPvt = new DifferencePivotTable(
                                  pvtTbl,
                                  DifferencePivotTable.DifferenceMode.PreviousRow,
                                  i);
                                diffPvt.Percentage = isPercent[i];
                                output = diffPvt;
                                break;
                            }
                        case "nextRow":
                            {
                                diffPvt = new DifferencePivotTable(
                                  pvtTbl,
                                  DifferencePivotTable.DifferenceMode.NextRow,
                                  i);
                                diffPvt.Percentage = isPercent[i];
                                output = diffPvt;
                                break;
                            }
                        case "previousColumn":
                            {
                                diffPvt = new DifferencePivotTable(
                                  pvtTbl,
                                  DifferencePivotTable.DifferenceMode.PreviousColumn,
                                  i);
                                diffPvt.Percentage = isPercent[i];
                                output = diffPvt;
                                break;
                            }
                        case "nextColumn":
                            {
                                diffPvt = new DifferencePivotTable(
                                  pvtTbl,
                                  DifferencePivotTable.DifferenceMode.NextColumn,
                                  i);
                                diffPvt.Percentage = isPercent[i];
                                output = diffPvt;
                                break;
                            }
                        default:
                            {
                                Console.WriteLine("Difference type not supported");
                                return output;
                            }
                    }
                }
            }
            return output;
        }

score 0 · Answer 1 · answered Nov 28 '19 at 07:04

0

Grand total is empty because its difference cannot be calculated (what is the 'previous' value for the grand-total?..).

In the current implementation of DifferencePivotTable difference is not calculated for sub-totals (including rows/columns totals). Technically, depending on difference type, some sub-totals may be calculated: for example, in case of DifferenceMode.PreviousColumn or DifferenceMode.NextColumn it is possible to calculate difference for the row with column totals (but this is not implemented).

answered Nov 28 '19 at 07:04

Vitaliy Fedorchenko

8,447
3
37
34

I understand the grand total, was only mentioning since all totals are null. In the example here https://www.nrecosite.com/pivotdata/calculate-difference-pivot-table.aspx is shows that the totals column totals will calculate but I am not seeing that. Is it because I am assigning the difference pivot table to the IPivotTable type output or is some setting missing? – Jason Nov 30 '19 at 03:01
@Jason I've checked that, appropriate row/column totals were worked before 1.3.6 release. After that all totals are empty as result of the bugfix (which is not related to that, in fact). Calculation of these totals will back in upcoming 1.5.1 release. – Vitaliy Fedorchenko Nov 30 '19 at 06:58
When is the 1.5.1 release scheduled for? – Jason Dec 03 '19 at 17:46
@Jason very soon, most likely next week or so – Vitaliy Fedorchenko Dec 03 '19 at 19:02

Different Pivot table drops totals

1 Answers1