You can compare his sales with target, sales as a percentage of total sales or sales in comparison to previous month's sale etc. For instance, there are many ways to evaluate performance of a sales employee. While analyzing spreadsheet data, instead of comparing exact values, you may want to compare the values in terms of calculations. IPivotTable calculatedFieldTable = (pivotCache, calculatedFieldSheet.Range) Ĭ = PivotFieldOrientation.RowField Ĭ = PivotFieldOrientation.DataField Ĭ = "$#,#0_) ($#,#0)" Ĭ( "Tax", "=IF(Amount > 1000, 3% * Amount, 0)") IPivotCache pivotCache = (worksheet.Range) IWorksheet calculatedFieldSheet = () ĬalculatedFieldSheet.Name = "CalculatedField"
#Excel pivot table tabular format code#
Refer to the following code to create a calculated field in the pivot table: To remove a calculated field from the collection you can use the Remove method which takes target field name as its parameter. The Add method accepts fieldname and IPivotField.Formula property as its parameters to generate the calculated field. You can use Add method of the ICalculatedFields interface to create a new calculated field in the pivot table. In GcExcel, CalculatedFields property represents the collection of all calculated fields in a particular pivot table. At year end, one can easily calculate the raised salary of employees by creating calculated field using salary and the rating field. For instance, an employee database of a company holds data about existing salary and performance rating of each employee. These fields are especially useful when summary functions and custom calculations do not generate the desired output. The output of above example code when viewed in Excel, looks like below:Ĭalculated fields in pivot table refer to the data fields created by applying additional logic or formula on existing data fields of the underlying data source. Pivottable.AddDataField(field_Amount2, "count amount", ConsolidationFunction.Count) Var field_Amount2 = pivottable.PivotFields
Pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum) Refer to the following example code to manage the visibility settings of the grand total field.įield_Product.Orientation = PivotFieldOrientation.RowField For example, if you want to display the grand total only for rows, then set the RowGrand property to true and ColumnGrand to false. These properties take boolean values and are set to true by default. You can display or hide the grand total for the row or column field by setting the visibility of ColumnGrand and RowGrand properties of the IPivotTable interface. The Grand total in pivot table helps in analyzing the total sum of the data in the pivot table. category will in level 1 and product in level 2. Var field_category = pivottable.PivotFields įield_category.Orientation = PivotFieldOrientation.RowField Var field_product = pivottable.PivotFields įield_product.Orientation = PivotFieldOrientation.RowField Refer to the following example code to add field function in a pivot table. Var field_Country = pivottable.PivotFields įield_Country.Orientation = PivotFieldOrientation.PageField Var field_Amount = pivottable.PivotFields įield_Amount.Orientation = PivotFieldOrientation.DataField Var field_Product = pivottable.PivotFields įield_Product.Orientation = PivotFieldOrientation.ColumnField
Var field_Category = pivottable.PivotFields įield_Category.Orientation = PivotFieldOrientation.RowField Var pivottable = (pivotcache, worksheet.Range, "pivottable1") IWorksheet worksheet = workbook.Worksheets Initialize the WorkBook and fetch the default WorkSheet
0 kommentar(er)
