Pivot not supported under DotNet

mfabian
mfabian Member Posts: 187
edited 2014-05-01 in NAV Three Tier
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 ;)
With best regards from Switzerland

Marcus Fabian

Comments

  • Rob_Hansen
    Rob_Hansen Member Posts: 296
    I haven't used this myself, but it sounds good. You may want to consider using the ClosedXML .net component: https://closedxml.codeplex.com/
    Rob Hansen
    http://www.epimatic.com
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    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
    Duikmeester Member Posts: 309
    Try declare variable xlPivotCaches of type PivotCaches, instead of reaching so deep into the properties:

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

Categories