/// <summary>
/// Calculates a grand total value for a cell.
/// </summary>
/// <param name="headers">The headers for the grand totals.</param>
/// <param name="cellBackingData">The backing data for the current grand total cell.</param>
/// <param name="isRowTotal">A value indicating if this is a row grand total.</param>
/// <param name="isParentColumnTotal">Bool indicating if % of Parent Total is used.</param>
/// <returns>An object value for a cell.</returns>
protected object CalculateGrandTotalValue(List <PivotTableHeader> headers, PivotCellBackingData cellBackingData, bool isRowTotal, bool isParentColumnTotal = false)
{
var currentHeader = headers[cellBackingData.MajorAxisIndex];
List <Tuple <int, int> > parentRowHeaderIndices, parentColumnHeaderIndices;
string rowTotalType = string.Empty, columnTotalType = string.Empty;
// Data fields do not get a value.
if (currentHeader.CacheRecordIndices.Count == 1 && currentHeader.CacheRecordIndices.First().Item1 == -2)
{
return(null);
}
// Find the parent indices in order to calculate the parent total value.
var headerIndices = currentHeader.CacheRecordIndices.Take(currentHeader.CacheRecordIndices.Count - 1).ToList();
parentRowHeaderIndices = new List <Tuple <int, int> >();
parentColumnHeaderIndices = isParentColumnTotal ? new List <Tuple <int, int> >() : headerIndices;
if (!isRowTotal)
{
// Use the datafield tuple if it exists (for parent row calculator), otherwise, use the tuple list passed in.
if (currentHeader.CacheRecordIndices.Any(i => i.Item1 == -2) && currentHeader.CacheRecordIndices.First().Item1 != -2)
{
parentColumnHeaderIndices = currentHeader.CacheRecordIndices.Where(i => i.Item1 == -2).ToList();
}
}
var parentBackingData = PivotTableDataManager.GetParentBackingCellValues(
this.PivotTable,
this.DataFieldCollectionIndex,
parentRowHeaderIndices,
parentColumnHeaderIndices,
rowTotalType,
columnTotalType,
this.TotalsCalculator);
var baseValue = parentBackingData.Result;
if (cellBackingData?.Result == null)
{
// If both are null, write null.
if (baseValue == null)
{
return(null);
}
// If the parent has a value, write out 0.
return(0);
}
else if (baseValue == null)
{
return(1);
}
return(this.CalculatePercentage(Convert.ToDouble(cellBackingData.Result), Convert.ToDouble(baseValue), true));
}
private double?GetCalculatedFieldTotal(PivotItemTreeNode node, ExcelPivotTable pivotTable, CacheFieldNode cacheField)
{
var totalsFunction = new TotalsFunctionHelper();
var calculatedFields = pivotTable.CacheDefinition.CacheFields.Where(f => !string.IsNullOrEmpty(f.Formula));
PivotTableDataManager.ConfigureCalculatedFields(calculatedFields, totalsFunction, pivotTable);
var fieldNameToValues = new Dictionary <string, List <object> >();
foreach (var cacheFieldName in cacheField.ReferencedCacheFieldsToIndex.Keys)
{
var values = pivotTable.CacheDefinition.CacheRecords.GetChildDataFieldValues(node.CacheRecordIndices, cacheField.ReferencedCacheFieldsToIndex[cacheFieldName]);
fieldNameToValues.Add(cacheFieldName, values);
}
var total = totalsFunction.EvaluateCalculatedFieldFormula(fieldNameToValues, cacheField.ResolvedFormula);
if (double.TryParse(total.ToString(), out var result))
{
return(result);
}
return(null);
}
/// <summary>
/// Calculates a pivot table body value for show data as percent of parent [row|column|total].
/// </summary>
/// <param name="isParentRow">A value indicating if the parent is a row.</param>
/// <param name="dataRow">The current row in the data.</param>
/// <param name="dataColumn">The current column in the data.</param>
/// <param name="parentHeaderIndices">The cache record indices of the parent header.</param>
/// <param name="backingDatas">The backing body data for the pivot table.</param>
/// <param name="isParentColumnTotal">Bool indicating if % of Parent Total is used.</param>
/// <returns>An object value for a cell.</returns>
protected object CalculateBodyValue(bool isParentRow, int dataRow, int dataColumn, List <Tuple <int, int> > parentHeaderIndices, PivotCellBackingData[,] backingDatas, bool isParentColumnTotal = false)
{
var rowHeader = base.PivotTable.RowHeaders[dataRow];
var columnHeader = base.PivotTable.ColumnHeaders[dataColumn];
var cellBackingData = backingDatas[dataRow, dataColumn];
var dataField = base.PivotTable.DataFields[base.DataFieldCollectionIndex];
List <Tuple <int, int> > parentRowHeaderIndices, parentColumnHeaderIndices;
string rowTotalType, columnTotalType;
if (isParentRow)
{
parentRowHeaderIndices = parentHeaderIndices;
parentColumnHeaderIndices = columnHeader.CacheRecordIndices;
var parentRowHeader = this.FindHeader(base.PivotTable.RowHeaders, parentRowHeaderIndices, out _);
// If the parent is a datafield no value is written out.
if (base.PivotTable.DataFields.Any(d => d.Field == parentRowHeader.PivotTableField))
{
return(null);
}
rowTotalType = parentRowHeader.TotalType;
columnTotalType = columnHeader.TotalType;
// Use the datafield tuple if it exists (for parent row calculator), otherwise, use the tuple list passed in.
if (rowHeader.CacheRecordIndices.Any(i => i.Item1 == -2) && rowHeader.CacheRecordIndices.First().Item1 != -2)
{
parentRowHeaderIndices = rowHeader.CacheRecordIndices.Where(i => i.Item1 == -2).ToList();
}
}
else
{
parentColumnHeaderIndices = parentHeaderIndices;
parentRowHeaderIndices = rowHeader.CacheRecordIndices;
var parentColumnHeader = this.FindHeader(base.PivotTable.ColumnHeaders, parentColumnHeaderIndices, out _);
// If the parent is a datafield no value is written out.
if (base.PivotTable.DataFields.Any(d => d.Field == parentColumnHeader.PivotTableField))
{
return(null);
}
rowTotalType = rowHeader.TotalType;
columnTotalType = parentColumnHeader.TotalType;
if (isParentColumnTotal)
{
parentColumnHeaderIndices = null;
}
}
var parentBackingData = PivotTableDataManager.GetParentBackingCellValues(
base.PivotTable,
base.DataFieldCollectionIndex,
parentRowHeaderIndices,
parentColumnHeaderIndices,
rowTotalType,
columnTotalType,
base.TotalsCalculator);
// Don't calculate the percentage if the cell contains an error value.
if (cellBackingData?.Result != null && Values.TryGetErrorType(cellBackingData?.Result.ToString(), out _) ||
parentBackingData.Result != null && Values.TryGetErrorType(parentBackingData.Result.ToString(), out _))
{
return(null);
}
if (cellBackingData?.Result == null || Convert.ToDouble(cellBackingData.Result) == 0 ||
parentBackingData.Result == null || Convert.ToDouble(parentBackingData.Result) == 0)
{
// If both are null, write null.
if (parentBackingData.Result == null || Convert.ToDouble(parentBackingData.Result) == 0)
{
return(null);
}
// If the parent has a value, write out 0.
return(0);
}
return(base.CalculatePercentage(Convert.ToDouble(cellBackingData.Result), Convert.ToDouble(parentBackingData.Result)));
}
