private static List <Token> ResolveFormulaReferences(string formula, TotalsFunctionHelper totalsCalculator, IEnumerable <CacheFieldNode> calculatedFields) { var resolvedFormulaTokens = new List <Token>(); var tokens = totalsCalculator.Tokenize(formula); foreach (var token in tokens) { if (token.TokenType == TokenType.NameValue) { // If a token references another calculated field, resolve the chain of formulas. var field = calculatedFields.FirstOrDefault(f => f.Name.IsEquivalentTo(token.Value)); if (field != null) { var resolvedReferences = PivotTableDataManager.ResolveFormulaReferences(field.Formula, totalsCalculator, calculatedFields); resolvedFormulaTokens.Add(new Token("(", TokenType.OpeningParenthesis)); resolvedFormulaTokens.AddRange(resolvedReferences); resolvedFormulaTokens.Add(new Token(")", TokenType.ClosingParenthesis)); } else { resolvedFormulaTokens.Add(token); } } else { resolvedFormulaTokens.Add(token); } } return(resolvedFormulaTokens); }
/// <summary> /// Resolve the name references and other formulas contained in a formula. /// </summary> /// <param name="calculatedFields">The list of calculated fields in the pivot table.</param> /// <param name="totalsCalculator">The function helper calculator.</param> /// <param name="pivotTable">The pivot table the fields are on.</param> public static void ConfigureCalculatedFields(IEnumerable <CacheFieldNode> calculatedFields, TotalsFunctionHelper totalsCalculator, ExcelPivotTable pivotTable) { // Add all of the cache field names to the calculation helper. var cacheFieldNames = new HashSet <string>(pivotTable.CacheDefinition.CacheFields.Select(c => c.Name)); totalsCalculator.AddNames(cacheFieldNames); // Resolve any calclulated fields that may be referencing each other to forumlas composed of regular ol' cache fields. foreach (var calculatedField in calculatedFields) { var resolvedFormulaTokens = PivotTableDataManager.ResolveFormulaReferences(calculatedField.Formula, totalsCalculator, calculatedFields); foreach (var token in resolvedFormulaTokens.Where(t => t.TokenType == TokenType.NameValue)) { if (!calculatedField.ReferencedCacheFieldsToIndex.ContainsKey(token.Value)) { var referencedFieldIndex = pivotTable.CacheDefinition.GetCacheFieldIndex(token.Value); calculatedField.ReferencedCacheFieldsToIndex.Add(token.Value, referencedFieldIndex); } } // Reconstruct the formula and wrap all field names in single ticks. string resolvedFormula = string.Empty; foreach (var token in resolvedFormulaTokens) { string tokenValue = token.Value; if (token.TokenType == TokenType.NameValue) { tokenValue = $"'{tokenValue}'"; } resolvedFormula += tokenValue; } calculatedField.ResolvedFormula = resolvedFormula; } }