Let’s imagine that we have already done all the instructions from the Report Sharp-Shooter Getting Started (page 60), which describes how to create PivotTable.
PivotTable properties connected with styles can be flexible configured, but all the cases can’t be covered. Sometimes we need a possibility to highlight cells, which satisfy some condition, set the style for a specific subgroup inside XDimension/YDimension. It’s quite evident that such settings can’t be considered during the component development as they depend on the user needs.
Well-aware users know that we can use CellCreating event, which is raised when a cell is created.
For example, we need to highlight Fact cells, which have values more than the average Fact Value.
For this purposes we need:
- To calculate average Fact value;
- To change style for the Fact cell, which satisfies our condition.
In order to calculate average Fact value we can use the following approach:
Average value for cells will be available only after creating of all of them, that is why we need to set document DoublePass property into true.
In order to achieve our goals we should place the following code inside the CommonScript:
double summ = 0;
//Amount of cells
int amount = 0;
//Checks whether the value more than the average value.
public bool ValueGreaterThanAvg(double val)
{
return (val > summ/amount - double.Epsilon)
}
//handler of the CellCreating event
public void table_CellCreating(object sender, CellCreatingEventArgs e)
{
if (e.Type==CellCreatingEventArgs.CellType.Fact && !e.Fact.IsTotal && !e.XItem.IsTotal && !e.YItem.IsTotal)
{
double val = 0;
if (e.Value!=null && double.TryParse(e.Value.ToString(), out val))
{
if (!Engine.IsDoublePass) //First pass
{
amount++;
summ += val;
}
else //Second pass
{
if (ValueGreaterThanAvg(val))
{
//change cell style
e.Style = "Highlight";
}
}
}
}
}
In the generate script of the document we have to attach our handler to the pivotTable cell creating event:
Next screenshot shows how the pivottable looked before applying of our algorithm:
Next screenshot demonstrates the result of applying our algorithm:
