Report with formula in NAV 2016

Hi

I have question. It’s possible to do report with formula not value in cell after report was import in excel?

I am using visual studio layout and navision version is 2016.

Thank’s for your answer

Hi Ugnka,
If you want to create a report that contains formula’s (not regular cell values), then you should look ifor the “excel buffer” functionality. The report designs created with Visual Studio RDLC, are just values.
But using the “excel buffer” then you can control it all your self from C/AL in NAV.

Thank’s for your answer. I try to do one report with “excel buffer” before and have a lot of issue. Maybe you know some great tutorial or example of report with “excel buffer” in NAV 2016.

Hi Ugnka,

There is a Youtube on how to use the Excel buffer (excel codeunit) by Archerpoint here:

https://www.youtube.com/watch?v=qW1MSqAZIEE

It’s for NAV 2013, but works the same in NAV 2016.

Thank’s

Something’s wrong with this codeunit in nav 2016.
When I try to save it I have this error message “You cannot create an Automation object “xlApp” on Microsoft Dynamics NAV Server. You must create it on a client computer.”

It works fine! You must be using it wrong.

How does this error occur?

This error occur after I comment line “FileName := FileDialog.OpenFile(‘Enter Excel File Name…’,FileName,2,’.XL’,0);” and try save changes.
I comment this line because I don’t have FileDialog Codeunit.

have you supplied filename then

No.Do you mean i shout write “FileName:= 'D:\SomeFile.xls”? This file shout be my report tempalte?

HI
---->If you dont Know how to export to Excel Using Automation Variables see My Blog … u would understand :slight_smile:
---->Just to use a formula in Cell you just have to use "Range " variable
Eg below given are Dotnet variables
Xlwrksht.Range(‘A1’,‘A1’).Value := 1;
Xlwrksht.Range(‘A2’,‘A2’).Value := 2;
Xlwrksht.Range(‘A3’,‘A3’).Value := 3;
Xlwrksht.Range(‘A4’,‘A4’).Value := 4;
XlRange := Xlwrksht.Range(‘B5’,‘B5’);
XlRange.Formula(’=SUM(A1:A4)’);

Variable i declared is
Name DataType Subtype Length
XlRange DotNet Microsoft.Office.Interop.Excel.Range.‘Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’

Let me Know if you need any further help

hi,
i am also fighting with creating an Export to Excel function on a list page.
i opened your blog but i did not understand so far.
is this correct??

-OnPostReport()
TempExcelBuffer.GiveUserControl;

-Transfer Header PE - OnPreDataItem()
TempExcelBuffer.DELETEALL;

-Transfer Header- OnAfterGetRecord()
RowNo += 1;
EnterCell(RowNo,1,‘No.’,TRUE,FALSE,FALSE,’’,’’);
EnterCell(RowNo,2,‘Description’,TRUE,FALSE,FALSE,’’,’’);
EnterCell(RowNo,3,‘Quantity’,TRUE,FALSE,FALSE,’’,’’);
EnterCell(RowNo,4,‘Net Weight’,TRUE,FALSE,FALSE,’’,’’);
//EnterCell(ExcelBuffer,RowNo,5,‘Location Code’,TRUE,FALSE,FALSE,’’,’’);
EnterCell(RowNo,6,‘Dimensions’,TRUE,FALSE,FALSE,’’,’’);
RowNo += 1;

-Transfer Header - OnPostDataItem()

-Transfer Line - OnPreDataItem()

-Transfer Line - OnAfterGetRecord()
RowNo += 1;
Item.GET(TransferLinePE.“Item No.”);
EnterCell(RowNo,1,TransferLinePE.“Item No.”,FALSE,FALSE,FALSE,’’,’@’);
EnterCell(RowNo,2,TransferLinePE.Description,FALSE,FALSE,FALSE,’’,’@’);
EnterCell(RowNo,3,FORMAT(TransferLinePE.Quantity),FALSE,FALSE,FALSE,’’,’’);
EnterCell(RowNo,4,FORMAT(TransferLinePE.“Net Weight” * Item.“Gross Weight”),FALSE,FALSE,FALSE,’’,‘0.000’);
//EnterCell(RowNo,5,(RECORDID.“Location Code”),FALSE,FALSE,FALSE,’’,’@’);
EnterCell(RowNo,5,FORMAT(DimensionsL.Code),FALSE,FALSE,FALSE,’’,’’);

-Transfer Line- OnPostDataItem()
TempExcelBuffer.CreateBookAndOpenExcel(‘Test Item Report’,’’,COMPANYNAME,USERID);

-LOCAL EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean;Sheetname : Text[30];NumberFormat : Text[1])
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.NumberFormat := NumberFormat; //UPG
IF TempExcelBuffer.INSERT THEN;