Pivot not supported under DotNet

mfabian

After having wasted almost a whole day on this issue I come to the conclusion that DotNet simply does not support the creation of Pivot Tables!

The crucial point is the statement:

xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,iRange);

The problem is that under DotNet, the PivotCaches Object doesn't have any methods and therefore not the .Add Method.

Under Automation, the creation of Pivot Tables was quite simple:

CreatePivotFromWS(iRange : Text[250];iPivotName : Text[250])
IF iPivotName = '' THEN
  iPivotName := 'PivotTable1';
xlPivotCache := XlApp.ActiveWorkbook.PivotCaches.Add(1,iRange);
xlPivotCache.CreatePivotTable('',iPivotName);
xlPivotSourceWorksheet := XlApp.ActiveSheet;
xlPivotTable := xlPivotSourceWorksheet.PivotTables(iPivotName);

Hopefully the dotnet objects will be corrected soon.
At the moment I don't see any other possibility than using pure simple and working Automation

Rob_Hansen

I haven't used this myself, but it sounds good. You may want to consider using the ClosedXML .net component: https://closedxml.codeplex.com/

Miklos_Hollender

Hi Marcus

Basically when you are tired with all this you can consider the following. Write SQL stored procedures for reports. Make PowerShell scripts for creating reports in Excel and e-mailing them to the receipients. (abstract away as much as possible into a function library). Set up a Windows task to run it daily or weekly. It takes about 20-30 hours of learning if you already know SQL to make it work flawlessly. Then you have the additional bonus of reports staying future proof as long as the database structure does not change.

Navision reports are fine when an actual user needs to print something out, but for everything you would use a pivot for, not.

BTW this is how a report I wrote in PowerShell used .NET Pivot tables, maybe it is helpful from C/AL .NET as well, maybe you can access PivotCaches:

#constants
$xlDatabase=1
$xlRowField=1
$xlColField=2
$xlDataField=4
$xlPivotTableVersion12=3

$WS2=$WB.Worksheets.Add()
$WS2.name = "SUMMARY"
$Range = 'DETAILS!R3C1:R' + $cnter + 'C11'
$PivotCache = $WB.PivotCaches().Add($xlDatabase,  $Range)
$PivotCache.CreatePivotTable('SUMMARY!R1C1','Pivot1')
$PivotTable = $WS2.PivotTables('Pivot1')
$PivotFields = $PivotTable.PivotFields("Item No.")
$PivotFields.Orientation=$xlRowField
$PivotFields = $PivotTable.PivotFields("Week")
$PivotFields.Orientation=$xlColField
$PivotFields = $PivotTable.PivotFields("Qty. on Sales Order")
$PivotFields.Orientation=$xlDataField
$PivotFields.Function = -4157  # sum, not count, dammit!

#make the current week stand out in color
$Cells2 = $WS2.Cells
$Week = get-date -UFormat %V
$w = [int]$Week
$Cells2.item(1, $w +1).EntireColumn.Interior.ColorIndex = 22 

Duikmeester

Try declare variable xlPivotCaches of type PivotCaches, instead of reaching so deep into the properties:

xlPivotCaches := XlApp.ActiveWorkbook.PivotCaches;
xlPivotCache := xlPivotCaches.Add(1,iRange);