public static void AddKpiToPivotTable(Kpi k, Excel.PivotTable pvt)
{
foreach (string sKpiPart in new string[] { "KPI_VALUE", "KPI_GOAL", "KPI_STATUS", "KPI_TREND" })
{
string sKpiMeasure = Convert.ToString(k.Properties[sKpiPart].Value);
if (string.IsNullOrEmpty(sKpiMeasure))
{
continue;
}
Excel.CubeField field = pvt.CubeFields.get_Item(sKpiMeasure);
if (field.Orientation == Excel.XlPivotFieldOrientation.xlDataField)
{
continue;
}
field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
if (sKpiPart == "KPI_STATUS" || sKpiPart == "KPI_TREND")
{
Excel.PivotItem pi = (Excel.PivotItem)pvt.DataPivotField.PivotItems(sKpiMeasure);
Excel.Range range = pi.DataRange;
Excel.IconSetCondition iconSet = (Excel.IconSetCondition)range.FormatConditions.AddIconSetCondition();
string sStatusGraphic = (sKpiPart == "KPI_STATUS" ? k.StatusGraphic : k.TrendGraphic);
IconSetDefinition def = new IconSetDefinition();
if (_dictIconSetLookup.ContainsKey(sStatusGraphic))
{
def = _dictIconSetLookup[sStatusGraphic];
}
else
{
System.Windows.Forms.MessageBox.Show("Status graphic type " + sStatusGraphic + " not expected. Please contact the authors of OLAP PivotTable Extensions on the About tab.", "OLAP PivotTable Extensions");
}
iconSet.IconSet = pvt.Application.ActiveWorkbook.IconSets[def.IconSet];
try
{
iconSet.ScopeType = Microsoft.Office.Interop.Excel.XlPivotConditionScope.xlDataFieldScope;
}
catch { }
iconSet.ShowIconOnly = true;
iconSet.ReverseOrder = def.Reverse;
int i = 2;
foreach (double d in def.ValueBoundaries)
{
Excel.IconCriterion crit = iconSet.IconCriteria[i++];
crit.Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
crit.Value = d;
crit.Operator = (int)(Excel.XlFormatConditionOperator.xlGreaterEqual);
}
}
}
}
public LevelChooserForm(Excel.CubeField cubeField, Excel.PivotTable pt)
{
PivotTable = pt;
InitializeComponent();
this.chkLevels.Items.Clear();
//cubeField.CreatePivotFields(); //shouldn't be necessary since it's already in the PivotTable
foreach (Excel.PivotField pf in cubeField.PivotFields)
{
if (pf.IsMemberProperty)
{
continue;
}
this.chkLevels.Items.Add(new LevelContainer(pf), !pf.Hidden);
}
}