Conditional Formatting
There are various ways to access groups of cells or specific ones to provide special formatting on them based on the contained value, their data field, or other property.
The two main events to use for customizing cell appearance are PreRender and CellDataBound:
CellDataBound server event is fired for each cell that is bound inside RadPivotGrid. In it you can check the cell type, which field it belongs to what is its content, etc. Based on this, you can apply a style property to the cell or assign it a CssClass and then style it as per your requirement. You can use this event for scenarios where the styling would be applied on per cell basis and would depend only on the content of the current cell.
PreRender is fired on each postback, no matter if triggered by an action in RadPivotGrid or an external control. In it, you can either access a group of items / cells and customize them one by one, or you can loop through them and check a given property until you find a specific one that you want to style. Again, you have the choice to either use the style properties of the TableCell object or to assign a CssClass and apply your own styles.
The Conditional Formatting online demo demonstrates some of the possible ways to access and style cells based on their type or some other condition. Below follow code excerpts from it with a description of what they do exactly.
Examples
- Formatting cells in CellDataBound All snippets by the end of this section are contained in the following event handler:
protected void RadPivotGrid1_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
{
//give a color to the various cells cells
if (e.Cell is PivotGridDataCell)
{
PivotGridDataCell cell = e.Cell as PivotGridDataCell;
//CODE SNIPPET FOLLOWS HERE
Protected Sub RadPivotGrid1_CellDataBound(sender As Object, e As PivotGridCellDataBoundEventArgs)
'give a color to the various cells cells
If TypeOf e.Cell Is PivotGridDataCell Then
Dim cell As PivotGridDataCell = TryCast(e.Cell, PivotGridDataCell)
'CODE SNIPPET FOLLOWS HERE
- Coloring the data aggregate cells: The sample below shows how you can apply styling to all data cells displaying the aggregate values for a given match of column and row fields. It also shows how to use the ParentColumnIndexes to recognize which column field the cell belongs to.
if (cell.CellType == PivotGridDataCellType.DataCell)
{
cell.BackColor = Color.FromArgb(220, 240, 255);
if (cell.ParentColumnIndexes[2].ToString() == "Sum of TotalPrice")
{
cell.Font.Italic = true;
}
}
If cell.CellType = PivotGridDataCellType.DataCell Then
cell.BackColor = Color.FromArgb(220, 240, 255)
If cell.ParentColumnIndexes(2).ToString() = "Sum of TotalPrice" Then
cell.Font.Italic = True
End If
The result of the above code is light blue background for all data cells:
and italic text style for those that are shown under the TotalPrice column.
- Coloring the column and row total cells: You can get the column and row total cells and apply styles to them based on various criteria. The following code applies different background color to the cells based on the data field they are calculated for and the actual values contained in them.
if (cell.CellType == PivotGridDataCellType.ColumnTotalDataCell || cell.CellType == PivotGridDataCellType.RowTotalDataCell)
{
switch ((cell.Field as PivotGridAggregateField).DataField)
{
case "TotalPrice":
if (cell.DataItem.ToString().Length > 0)
{
int price = Convert.ToInt32(cell.DataItem);
if (price > 10000)
{
cell.BackColor = Color.FromArgb(0, 102, 102);
}
else if (price > 5000)
{
cell.BackColor = Color.FromArgb(0, 153, 153);
}
else
{
cell.BackColor = Color.FromArgb(0, 204, 204);
}
}
break;
}
}
ElseIf cell.CellType = PivotGridDataCellType.ColumnTotalDataCell OrElse cell.CellType = PivotGridDataCellType.RowTotalDataCell Then
Select Case TryCast(cell.Field, PivotGridAggregateField).DataField
Case "TotalPrice"
If cell.DataItem.ToString().Length > 0 Then
Dim price As Integer = Convert.ToInt32(cell.DataItem)
If price > 10000 Then
cell.BackColor = Color.FromArgb(0, 102, 102)
ElseIf price > 5000 Then
cell.BackColor = Color.FromArgb(0, 153, 153)
Else
cell.BackColor = Color.FromArgb(0, 204, 204)
End If
End If
Exit Select
End Select
The code above results in the following formatting for column totals:
and for row totals as well:
- Coloring the column and row grand total cells: Coloring the column and row grand total cells follows the same logic as the total cells. Below follows a sample which demonstrates how styling can be applied to the row grand total cells for a given field:
else if (cell.CellType == PivotGridDataCellType.RowGrandTotalDataCell || cell.CellType == PivotGridDataCellType.ColumnGrandTotalDataCell)
{
switch ((cell.Field as PivotGridAggregateField).DataField)
{
case "Quantity":
double quantity = Convert.ToDouble(cell.DataItem);
if (quantity > 5000)
{
cell.BackColor = Color.FromArgb(0, 153, 204);
}
else
{
cell.BackColor = Color.FromArgb(153, 204, 255);
}
break;
}
}
ElseIf cell.CellType = PivotGridDataCellType.RowGrandTotalDataCell OrElse cell.CellType = PivotGridDataCellType.ColumnGrandTotalDataCell Then
Select Case TryCast(cell.Field, PivotGridAggregateField).DataField
Case "Quantity"
Dim quantity As Double = Convert.ToDouble(cell.DataItem)
If quantity > 5000 Then
cell.BackColor = Color.FromArgb(0, 153, 204)
Else
cell.BackColor = Color.FromArgb(153, 204, 255)
End If
Exit Select
End Select
- Formatting the column and row headers based on their nested level: You can use ParentIndexes property which returns an array of the names of parent fields of a given header cell. In the below example, based on the length of the array, we differentiate the nested level of the headers and provide different styling for different levels.
else if (e.Cell is PivotGridRowHeaderCell)
{
PivotGridRowHeaderCell cell = e.Cell as PivotGridRowHeaderCell;
if (cell.ParentIndexes != null && cell.ParentIndexes.Length == 1)
{
cell.Font.Bold = true;
}
else
{
cell.Font.Italic = true;
}
}
else if (e.Cell is PivotGridColumnHeaderCell)
{
PivotGridColumnHeaderCell cell = e.Cell as PivotGridColumnHeaderCell;
if (cell.ParentIndexes != null)
{
if (cell.ParentIndexes.Length == 1)
{
cell.Font.Bold = true;
}
else if (cell.ParentIndexes.Length == 2)
{
cell.Font.Italic = true;
}
}
}
ElseIf TypeOf e.Cell Is PivotGridRowHeaderCell Then
Dim cell As PivotGridRowHeaderCell = TryCast(e.Cell, PivotGridRowHeaderCell)
If cell.ParentIndexes IsNot Nothing AndAlso cell.ParentIndexes.Length = 1 Then
cell.Font.Bold = True
Else
cell.Font.Italic = True
End If
ElseIf TypeOf e.Cell Is PivotGridColumnHeaderCell Then
Dim cell As PivotGridColumnHeaderCell = TryCast(e.Cell, PivotGridColumnHeaderCell)
If cell.ParentIndexes IsNot Nothing Then
If cell.ParentIndexes.Length = 1 Then
cell.Font.Bold = True
ElseIf cell.ParentIndexes.Length = 2 Then
cell.Font.Italic = True
End If
End If
End If
- Formatting cells in PreRender In PreRender you can access groups of items/cells, as they are all already created. An example of accessing all aggregate fields (the items/cells used to indicate the name of the fields that are aggregated). The CellStyle setting is applied to the aggregate fields under the column headers, while RenderingControl references the items shown in the data zone.
else if (e.Cell is PivotGridRowHeaderCell)
{
PivotGridRowHeaderCell cell = e.Cell as PivotGridRowHeaderCell;
if (cell.ParentIndexes != null && cell.ParentIndexes.Length == 1)
{
cell.Font.Bold = true;
}
else
{
cell.Font.Italic = true;
}
}
else if (e.Cell is PivotGridColumnHeaderCell)
{
PivotGridColumnHeaderCell cell = e.Cell as PivotGridColumnHeaderCell;
if (cell.ParentIndexes != null)
{
if (cell.ParentIndexes.Length == 1)
{
cell.Font.Bold = true;
}
else if (cell.ParentIndexes.Length == 2)
{
cell.Font.Italic = true;
}
}
}
ElseIf TypeOf e.Cell Is PivotGridRowHeaderCell Then
Dim cell As PivotGridRowHeaderCell = TryCast(e.Cell, PivotGridRowHeaderCell)
If cell.ParentIndexes IsNot Nothing AndAlso cell.ParentIndexes.Length = 1 Then
cell.Font.Bold = True
Else
cell.Font.Italic = True
End If
ElseIf TypeOf e.Cell Is PivotGridColumnHeaderCell Then
Dim cell As PivotGridColumnHeaderCell = TryCast(e.Cell, PivotGridColumnHeaderCell)
If cell.ParentIndexes IsNot Nothing Then
If cell.ParentIndexes.Length = 1 Then
cell.Font.Bold = True
ElseIf cell.ParentIndexes.Length = 2 Then
cell.Font.Italic = True
End If
End If
End If
