Printing reports to Excel in Dynamics NAV

Generally we use reports to view the data and print the necessary information. There are cases wherein we will be required to export the report da into an Excel file and do a comparative study.

The steps below will take us through on how to export a report into an excel sheet

Consider the scenario wherein you already have a built in report (Ex: GL Transactions), now you need to export the data for a particular period to an excel sheet

What should I do ?

1.       Create a new Boolean Variable against the report you want to work with
2.       Design a request form like the below image

3.       Assign the Boolean Variable to the “SourceExpr” of the Check box.
4.       Save and Close the report.

The request page provides you an option like Print to Excel. If the Option is checked, the report will be printed to excel and if it is unchecked excel report will not be generated.

Note:               

Consider, if there is no need of previewing the report.  Then there is no need of request form. You can directly make the section’s visibility as False (“CurrReport.Showoutput(False)”) and “processingOnly” property of the report as “Yes”.

This enables the user to print the report to excel instead of previewing.

Coding:

We can use both excel buffer and automation server for exporting/print the report to excel.

·         The Report 108 in the standard NAV will guide you on using Excel Buffer functions.
·         For Automation Server I have given a sample code below for reference. This code will be executed when the Check Box (Boolean variable) in the request form is checked or selected

<SAMPLE CODE>

IF PrinttoExcel THEN //PrinttoExcel – Boolean variable

BEGIN

  IF ISCLEAR(xlapp) THEN  //xlapp – Automation – Microsoft Excel 12.0 Object Library.Application

    CREATE(xlapp);

  //Window – Dialog variable

  window.OPEN(Text001+'@1@@@@@@@@@@@@@@@@@@@@\');

  window.UPDATE(1,0);

  //xlbook – Automation – Microsoft Excel 12.0 Object Library.Workbook

  xlbook:=xlapp.Workbooks.Add;

  //xlsheet – Automation – Microsoft Excel 12.0 Object Library.WorkSheet

  xlsheet:=xlbook.Worksheets.Add;

  xlsheet.Name:='Budget Analysis';

  RowNo:=1; // Where RowNo as integer 

//Printing report Header – Specify the report column headers here

xlsheet.Range('A'+FORMAT(RowNo)).Value:= ‘Column XXXX’

xlsheet.Range('B'+FORMAT(RowNo)).Value:= ‘Column YYYY’

xlsheet.Range('C'+FORMAT(RowNo)).Value:= ‘Column ZZZZ’

  …….

//Printing report header

END;

Note: When using automation Server, we need to increment the Row no for each record in the data item.

Hope the information was useful.