public static string GetMeasureName(Excel.Range rngCell) { Excel.PivotItem pi = null; pi = rngCell.PivotItem; string piName = pi.Name; return(DaxDrillParser.GetMeasureFromPivotItem(piName)); }
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); } } } }