Pivot table - Showing records in different columns.

shibily

Hi,
I am trying to create an Excel Pivot table through Navision.
For that i have put the ncessary records in one sheet and and created another sheet in the same workbook to create a Pivot table based on these records.
The columns in the Pivot table are Customer, Item Category, Item, Qty and Value.
I almost managed to create the table, however i face few issues on this which i am not able to rectify

a) I want to bring the Qty and Value in different columns. Currently i am getting both in the same column. I am not able to give the Postition for the Value field. When i give, i am getting an error " Unable to set the Position property of the PivotField ".
Please find the Pivot.Jpg . By default i am getting the Sum of Qty and Sum of Value in the Same coumn. I want it to be populated in two different columns.

b) I want the fields to be in Collapse mode by default. By referring the Macro, i wrote the code as xlPivotField.ShowDetail := FALSE;.
Here also i am getting some error.

Please find the following code which i have used in ths.
//***************************************************

xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,'Table1!A1:E'+FORMAT(intRowCount));
xlPivotCache.CreatePivotTable('','PivotTable1');

XlWorksheet := xlApp.ActiveSheet();
xlPivotTable := XlWorksheet.PivotTables('PivotTable1');
XlWorksheet.Name := 'Pivot Table';

xlPivotField := xlPivotTable.PivotFields('Customer');
xlPivotField.Orientation := 1; //xlRowField;
xlPivotField.Position := 1;


xlPivotField := xlPivotTable.PivotFields('Category');
xlPivotField.Orientation := 1; //xlRowField
xlPivotField.Position := 2;
//xlPivotField.ShowDetail := FALSE;

xlPivotField := xlPivotTable.PivotFields('Item');
xlPivotField.Orientation := 1; //xlRowField
xlPivotField.Position := 3;
//xlPivotField.ShowDetail := FALSE;


xlPivotField := xlPivotTable.PivotFields('Qty');
xlPivotField.Orientation := 1;
xlPivotField.Position := 4;
xlPivotTable.AddDataField(xlPivotField,'Sum of Quantity') ;


xlPivotField := xlPivotTable.PivotFields('Value');
xlPivotField.Orientation := 1;
//xlPivotField.Position := 5;
xlPivotTable.AddDataField(xlPivotField,'Sum of Value') ;

Can anybody help me on this?

Thanks in advance.
Shibily

kine

I am using this syntax:

xlPivotField := xlPivotTable.PivotFields(FORMAT(ValueField1));
xlPivotTable.AddDataField(xlPivotField);

xlPivotField := xlPivotTable.PivotFields(FORMAT(ValueField2));
xlPivotTable.AddDataField(xlPivotField);

xlPivotField := xlPivotTable.DataPivotField;
xlPivotField.Orientation := 2; //xlColumnField

xlPivotField := xlPivotTable.PivotFields(FORMAT(DataField));
xlPivotField.DrillTo(FORMAT(DataField)); //collaps to this field level

shibily

Thanks Kine :-)
It worked

kine

You are welcome... 8)