We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Creating Pivot Table with "Values" Column

Can anyone assist on a small problem I have please.

I’ve created a PivotTable but want to replicate the “[Sigma] Values” column that is automatically generated in Excel (in the Column Labels).

I don’t want to list Columns, just the generic “Data” that encapsulates individual dragged fields into the data area.

Is there any way of doing this?

Hi,


I think you may use RowGrand or ColumnGrand boolean attributes of PivotTable accordingly for your need:
e.g
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

If you have different requirement, give us your simple sample template file with details (you may give description/text or screen shots for your needs), so that we can understand your requirement and reply you accordingly.

Thank you.

Amjad,

Thanks for your help. Unfortunately this produces an error in Excel (shows the red bar at the top saying there’s a problem).

The code I have is:

Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(0)
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report1
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 0)

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Product”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 1”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 2”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 3”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 4”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Brand”)

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear1”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear2”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear3”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear4”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear5”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear6”)


Which as you can see, I’m adding rows and the data values but don’t want the columns.


Hi,


Please paste your complete code here with the attachment of input and output Excel files here. Also, give us a sample file with your desired pivot table in it. We will check it soon.

Thank you.

All,

I ran into the same issue -- my "{Sigma} Values" data was auto-placed in the ROW instead of where I wanted it in the COLUMN. I have a hack (Java) below that moves the PivotField over to the COLUMN, but maybe the experts have a better way?

- Kieron

int i = ws.getPivotTables().add(data, "A1", PIVOT_TABLE_NAME);

PivotTable p = ws.getPivotTables().get(i);

p.setRowGrand(true);
p.setColumnGrand(true);

p.addFieldToArea(PivotFieldType.ROW, 0); //Year
p.addFieldToArea(PivotFieldType.ROW, 1); //Month
p.addFieldToArea(PivotFieldType.DATA, 2); //No. Findings
p.addFieldToArea(PivotFieldType.DATA, 3); //No. Meetings

//this is a hack to move the auto-generated "{Sigma} Values" PivotField to the correct location
if(p.getFields(PivotFieldType.ROW).size() > 0) {
PivotFields pfs = p.getFields(PivotFieldType.ROW);
for (int x = 0; x < pfs.size(); x++) {
PivotField pf = pfs.get(x);
//System.out.println("PF, {name:"+pf.getName()+", displayName:"+pf.getDisplayName()+", field:"+pf.getBaseField()+", function:"+pf.getFunction()+"}");

//I only added "Year" and "Month" above. "Data" is the name of the auto-generated PivotFieldType.ROW
if (pf.getName().equals("Data")) {
//System.out.println("pf moved");
p.addFieldToArea(PivotFieldType.COLUMN, pf);
}

}
}

Kieron,

Thanks for the help there, glad to hear I explained myself correctly and there’s a somewhat-solution behind it.

Will look at converting that hack to VB and see if it does the trick, but likewise if the Aspose experts have a better/proper/official way of achieving this it would be nice to now.

Thanks again Kieron, much appreciated.

Kieron

Modified my code to be a VB equivalent of your Java hack and it works perfectly!

Thank you SO much for your assistance.

Best regards,

Jason

Glad to help -- and to know that at least this hack is cross-platform-able.

- Kieron

HUGE HELP! THANK YOU.


I can confirm this worked in C# as well:

PivotTableCollection pivotTables = sheet2.PivotTables;
PivotTable p = pivotTables[0];
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, “SOME FIELD”); // BU
pivotTable.AddFieldToArea(PivotFieldType.Row, “SOME FIELD2”); // LE_NM
pivotTable.AddFieldToArea(PivotFieldType.Data, “SOME FIEL3”);
pivotTable.AddFieldToArea(PivotFieldType.Data, “SOME FIELD4”);

//this is a hack to move the auto-generated “{Sigma} Values” PivotField to the correct location
if (p.Fields(PivotFieldType.Row).Count > 0) {
PivotFieldCollection pfc = p.Fields(PivotFieldType.Row);
for (int x = 0; x < pfc.Count; x++) {
PivotField pf = pfc[x];
if (pf.Name.Equals(“Data”)) {
p.AddFieldToArea(PivotFieldType.Column, pf);
}
}
}

Hi,

You can use this sample code:


pivotTable.ColumnFields.Add(pivotTable.DataField);//move datafied to columns


OR

PivotField data = pivotTable.DataField;
pivotTable.AddFieldToArea(PivotFieldType.Column, data);


So the property DataField stores the "{Sigma} Values" com.aspose.cells.PivotField Object. Niiice. I'll try that out. Certainly a lot more efficient to access a property than to run a "for" loop.

Could "DataField" be added to the list of public properties in the Java Doc? Although I guess it might be confusing when "DataField" is alongside the "getDataFields()" function... hmmm... *shrug*

Oh well. Good to know. Thank you for the insight!

- Kieron

Hi,


We provide the similar APIs for Java version as well, e.g There are two property setters/getters for PivotTable class in the JAVA Docs:

getDataField:
public PivotField getDataField()
Gets a PivotField object that represents all the data fields in a PivotTable. Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels. It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.

getDataFields:
public PivotFieldCollection getDataFields()
Gets a PivotField object that represents all the data fields in a PivotTable. Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels. It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.