// some custom logic to demonstrate the approach in action
private void OnPivotCustomCellValue(object sender, PivotCellValueEventArgs e)
{
if (e.DataField == fieldExtendedPriceUnbound)
{
PivotDrillDownDataSource ds = e.CreateDrillDownDataSource();
decimal value = 0;
Dictionary <PivotGridField, object> columnFieldValues = new Dictionary <PivotGridField, object>();
PivotGridField[] columnFields = e.GetColumnFields();
foreach (PivotGridField field in columnFields)
{
columnFieldValues[field] = e.GetFieldValue(field);
}
for (int i = 0; i < ds.RowCount; i++)
{
bool skip = false;
foreach (PivotGridField field in columnFields)
{
if (!Comparer.Equals(ds[i][field], columnFieldValues[field]))
{
skip = true;
break;
}
}
if (skip)
{
continue;
}
decimal v1 = Convert.ToDecimal(ds[i][e.DataField]);
value += v1;
}
e.Value = value;
}
}
object UpdateValue(PivotGridField field, int index, PivotCellValueEventArgs e)
{
object value = e.GetFieldValue(field, index);
if (field == fieldOrderDate)
{
int currentPosition = YearValues.IndexOf(value);
if (currentPosition > 0)
{
value = YearValues[currentPosition - 1];
}
else
{
value = "0000";
}
}
return(value);
}
private void pivotGridControlMain_CustomCellValue(object sender, PivotCellValueEventArgs e)
{
try
{
int CYear = 0;
if (bbiStartDate.EditValue != null)
CYear = ((DateTime)bbiStartDate.EditValue).Year;
if (e.DataField == calcCases)
{
if (e.GetFieldValue(fieldYearNo) == null)
return;
if ((int)e.GetFieldValue(fieldYearNo) == CYear - 1)
{
if (e.Value == null || e.GetCellValue(8, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(8, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(0, e.RowIndex) / (decimal)e.GetCellValue(8, e.RowIndex);
}
else if ((int)e.GetFieldValue(fieldYearNo) == CYear)
{
if (e.Value == null || e.GetCellValue(0, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(0, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(8, e.RowIndex) / (decimal)e.GetCellValue(0, e.RowIndex);
}
}
else if (e.DataField == calcNPS)
{
if (e.GetFieldValue(fieldYearNo) == null)
return;
if ((int)e.GetFieldValue(fieldYearNo) == CYear - 1)
{
if (e.Value == null || e.GetCellValue(10, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(10, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(2, e.RowIndex) / (decimal)e.GetCellValue(10, e.RowIndex);
}
else if ((int)e.GetFieldValue(fieldYearNo) == CYear)
{
if (e.Value == null || e.GetCellValue(2, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(2, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(10, e.RowIndex) / (decimal)e.GetCellValue(2, e.RowIndex);
}
}
else if (e.DataField == calcVolume)
{
if (e.GetFieldValue(fieldYearNo) == null)
return;
if ((int)e.GetFieldValue(fieldYearNo) == CYear - 1)
{
if (e.Value == null || e.GetCellValue(12, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(12, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(4, e.RowIndex) / (decimal)e.GetCellValue(12, e.RowIndex);
}
else if ((int)e.GetFieldValue(fieldYearNo) == CYear)
{
if (e.Value == null || e.GetCellValue(4, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(4, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(12, e.RowIndex) / (decimal)e.GetCellValue(4, e.RowIndex);
}
}
else if (e.DataField == calcPPL)
{
if (e.GetFieldValue(fieldYearNo) == null)
return;
if ((int)e.GetFieldValue(fieldYearNo) == CYear - 1)
{
if (e.Value == null || e.GetCellValue(14, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(14, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(6, e.RowIndex) / (decimal)e.GetCellValue(14, e.RowIndex);
}
else if ((int)e.GetFieldValue(fieldYearNo) == CYear)
{
if (e.Value == null || e.GetCellValue(6, e.RowIndex) == null || e.Value == "0" || e.GetCellValue(6, e.RowIndex) == "0")
return;
e.Value = (decimal)e.GetCellValue(14, e.RowIndex) / (decimal)e.GetCellValue(6, e.RowIndex);
}
}
}
catch (Exception ex)
{
Logger.Debug(ex.Message, ex);
}
}
private void pivotGridControl1_CustomCellValue(object sender, PivotCellValueEventArgs e)
{
try
{
// row wise GrandTotal only
if (e.DataField == pivotGridField4 && e.RowValueType == PivotGridValueType.GrandTotal && e.ColumnValueType == PivotGridValueType.Value)
{
decimal summaryValue1 = Convert.ToDecimal(e.GetFieldValue(pivotGridField2));
decimal summaryValue2 = Convert.ToDecimal(e.GetFieldValue(pivotGridField3));
//decimal value=summaryValue1 / summaryValue2;
if (summaryValue1 != 0)
{
if (summaryValue1 != 0 && summaryValue2 != 0)
{
decimal acc = summaryValue1 / summaryValue2;
e.Value = 100 * (1 - acc);
}
}
else
{
e.Value = 0;
}
}
// Column wise grand total
if (e.DataField == pivotGridField4 && e.RowValueType == PivotGridValueType.GrandTotal && e.ColumnValueType == PivotGridValueType.GrandTotal)
{
decimal summaryValue1 = Convert.ToDecimal(e.GetFieldValue(pivotGridField2));
decimal summaryValue2 = Convert.ToDecimal(e.GetFieldValue(pivotGridField3));
//decimal value=summaryValue1 / summaryValue2;
if (summaryValue1 != 0)
{
if (summaryValue1 != 0 && summaryValue2 != 0)
{
decimal acc = summaryValue1 / summaryValue2;
e.Value = 100 * (1 - acc);
}
}
else
{
e.Value = 0;
}
}
// row wise Total
if (e.DataField == pivotGridField4 && e.RowValueType == PivotGridValueType.Total && e.ColumnValueType == PivotGridValueType.GrandTotal)
{
decimal summaryValue1 = Convert.ToDecimal(e.GetFieldValue(pivotGridField2));
decimal summaryValue2 = Convert.ToDecimal(e.GetFieldValue(pivotGridField3));
if (summaryValue1 != 0)
{
if (summaryValue1 != 0 && summaryValue2 != 0)
{
decimal acc = summaryValue1 / summaryValue2;
e.Value = 100 * (1 - acc);
}
}
else
{
e.Value = 0;
}
}
// row wise Total and coulmn wise total
if (e.DataField == pivotGridField4 && e.RowValueType == PivotGridValueType.Total && e.ColumnValueType == PivotGridValueType.Value)
{
decimal summaryValue1 = Convert.ToDecimal(e.GetFieldValue(pivotGridField2));
decimal summaryValue2 = Convert.ToDecimal(e.GetFieldValue(pivotGridField3));
//decimal value=summaryValue1 / summaryValue2;
if (summaryValue1 != 0)
{
if (summaryValue1 != 0 && summaryValue2 != 0)
{
decimal acc = summaryValue1 / summaryValue2;
e.Value = 100 * (1 - acc);
}
}
else
{
e.Value = 0;
}
}
// row wise value and coulmn wise Grand Total
if (e.DataField == pivotGridField4 && e.RowValueType == PivotGridValueType.Value && e.ColumnValueType == PivotGridValueType.GrandTotal)
{
decimal summaryValue1 = Convert.ToDecimal(e.GetFieldValue(pivotGridField2));
decimal summaryValue2 = Convert.ToDecimal(e.GetFieldValue(pivotGridField3));
//decimal value=summaryValue1 / summaryValue2;
if (summaryValue1 != 0)
{
if (summaryValue1 != 0 && summaryValue2 != 0)
{
decimal acc = summaryValue1 / summaryValue2;
e.Value = 100 * (1 - acc);
}
}
else
{
e.Value = 0;
}
//
if (summaryValue1 == 0 && summaryValue1 != null)
{
if (summaryValue2 != 0)
{
e.Value = 100;
}
else if (summaryValue2 == 0)
{
e.Value = 0;
}
}
}
}
catch
{
MessageBox.Show("Problem With loading records Please check it");
}
finally
{
SplashScreenManager.CloseForm(false);
}
}
private void pivotGridControl1_CustomCellValue(object sender, PivotCellValueEventArgs e)
{
// Calculates the 'Percent' field values.
if (e.DataField.Name == "PercentOfBeverages")
{
// Do not display grand total values.
if (e.RowValueType == PivotGridValueType.GrandTotal)
{
e.Value = null;
return;
}
var rowValues = e.GetRowFields().Select(f => f.FieldName == "CategoryName" ? "Beverages" : e.GetFieldValue(f)).ToArray();
var columnValues = e.GetColumnFields().Select(f => f.FieldName == "CategoryName" ? "Beverages" : e.GetFieldValue(f)).ToArray();
decimal beveragesValue = Convert.ToDecimal(e.GetCellValue(columnValues, rowValues, e.DataField));
if (beveragesValue == 0)
{
e.Value = null;
}
else
{
e.Value = Convert.ToDecimal(e.Value) / beveragesValue;
}
}
else
{
return;
}
}