As there are merits of a solution which depend on the situations and
conditions in which they are used, I am suggesting one more alternate for
achieving the objective i.e. Exporting data to Excel. In this solution we will
make use of standard AX’s Reports and the concept of exporting the report data
to an Excel file. I assume that we all understand that any standard AX report
can be sent to a variety of Output devices such as Printer,Screen,Fax,File (
CSV,TXT,PDF,EXCEL etc) . The disadvantage in this is that the report headers is
repeated after every page break and that is an undesired part . In the following
walkthrough we will get over this problem as well as restrict the output to a
file so that it gives a look and feel of an data export functionality.
We will create a sample report for exporting the Price agreements data to Excel
Start creating a report in AOT as per the following steps : Step 1: Create a class as shown below in which we would declare objects.
public class ReportRun extends ObjectRun
{
boolean printHeading ; DialogField dlgFileName;
FileName fileName;
} “printHeading” is for checking whether the report heading has been printed already. If so, then do not not print them on the page. So headings will be printed on page one but not on next pages. The other two variables are for getting the filename provided by users from dialog
Step 2: Override the following two methods as shown below to set the PrinterSettings and PrintMedium ( i.e. File)
public boolean printerSettings(int _showWhat=-1)
{
boolean ret; ret = super(_showWhat); return ret;
} public PrintMedium setTarget(PrintMedium _target)
{
PrintMedium ret; ret = super(PrintMedium::File); return ret;
}
Step3 :Add an init method to initialise the report
public void init()
{
super(); printHeading = true; // initially the heading will be printed
this.printJobSettings().setTarget(PrintMedium::File); // setting the report output option to “file”
this.printJobSettings().preferredFileFormat(PrintFormat::ASCII); // Print format is set to ASCII
this.printJobSettings().fileName(fileName); //"c:\\PriceAgrements.xls"); }
Step 4: Add a report dialog (Override dialog method)
public Object dialog(Object _dialog)
{
Dialog dialog = super(_dialog); dlgFileName = dialog.addFieldValue(typeid(FileNameSave), ""); return dialog;
} public boolean getFromDialog()
{
;
fileName = dlgFileName.value(); // get values for file name from the dialog
return true;
} Step 5 : The followiing two methods are mandatory to add when we need to provide a button for browsing the file and selecting the file name on dialog // AOSRunMode::client
str fileNameLookupTitle()
{
return 'Select file to save';
} container fileNameLookupFilter()
{
return [".CSV", ".xls"];
} Step 6 : Finally the override the run method public void run()
{;
this.printJobSettings().fileName(fileName); super();
} Step 7 : In addition to the above, add methods for getting the fields which we wish to export or choose in the fields’ properties in the design “body” section . I have provided a link for the sample xpo below for download. not sure if it will work . But in case it does not then send me an email.
We will create a sample report for exporting the Price agreements data to Excel
Start creating a report in AOT as per the following steps : Step 1: Create a class as shown below in which we would declare objects.
public class ReportRun extends ObjectRun{
boolean printHeading ; DialogField dlgFileName;
FileName fileName;
} “printHeading” is for checking whether the report heading has been printed already. If so, then do not not print them on the page. So headings will be printed on page one but not on next pages. The other two variables are for getting the filename provided by users from dialog
Step 2: Override the following two methods as shown below to set the PrinterSettings and PrintMedium ( i.e. File)
public boolean printerSettings(int _showWhat=-1)
{
boolean ret; ret = super(_showWhat); return ret;
} public PrintMedium setTarget(PrintMedium _target)
{
PrintMedium ret; ret = super(PrintMedium::File); return ret;
}
Step3 :Add an init method to initialise the report
public void init()
{
super(); printHeading = true; // initially the heading will be printed
this.printJobSettings().setTarget(PrintMedium::File); // setting the report output option to “file”
this.printJobSettings().preferredFileFormat(PrintFormat::ASCII); // Print format is set to ASCII
this.printJobSettings().fileName(fileName); //"c:\\PriceAgrements.xls"); }
Step 4: Add a report dialog (Override dialog method)
public Object dialog(Object _dialog){
Dialog dialog = super(_dialog); dlgFileName = dialog.addFieldValue(typeid(FileNameSave), ""); return dialog;
} public boolean getFromDialog()
{
;
fileName = dlgFileName.value(); // get values for file name from the dialog
return true;
} Step 5 : The followiing two methods are mandatory to add when we need to provide a button for browsing the file and selecting the file name on dialog // AOSRunMode::client
str fileNameLookupTitle()
{
return 'Select file to save';
} container fileNameLookupFilter()
{
return [".CSV", ".xls"];
} Step 6 : Finally the override the run method public void run()
{;
this.printJobSettings().fileName(fileName); super();
} Step 7 : In addition to the above, add methods for getting the fields which we wish to export or choose in the fields’ properties in the design “body” section . I have provided a link for the sample xpo below for download. not sure if it will work . But in case it does not then send me an email.
SD_ExportToExcel
e.g. for QtyAmount field the field property can be set to fetch data from
PriceDiscTable.QuantityAmount
similarly for other fields shown in “Body”also we can set the field property
appropriately.
Step 8 : In the report design add the following to prevent the printing of
report heading in subsequent pages.
public void executeSection()
{
if(printHeading)
{
super();
} printHeading = false;
}
The other methods shown in the report for fetching the field data are for
reference/educational purpose only that there is another way of getting the
field values.
e.g. we can get the QuantityAmount field by adding following method also and
then in the field property in the report‘s “Body” section use the DataMethod
instead of Table and Data field properties.
display Amount qtyAmount()
{
return priceDiscTable.QuantityAmount;
} The text fields added in the report design “ Page header are for printing the column headings
Step 9 : Test the report. When run the following dialog will open
Select the file name and path using the browse button .
Hit OK. The file will be saved at the location specified. Open the file using MS Excel. It will prompt that the file is in different format and if you still wish to open it using excel, Accept and go ahead.
e.g. for QtyAmount field the field property can be set to fetch data from
PriceDiscTable.QuantityAmount
similarly for other fields shown in “Body”also we can set the field property
appropriately.
Step 8 : In the report design add the following to prevent the printing of
report heading in subsequent pages.
public void executeSection(){
if(printHeading)
{
super();
} printHeading = false;
}
The other methods shown in the report for fetching the field data are for
reference/educational purpose only that there is another way of getting the
field values.
e.g. we can get the QuantityAmount field by adding following method also and
then in the field property in the report‘s “Body” section use the DataMethod
instead of Table and Data field properties.
display Amount qtyAmount(){
return priceDiscTable.QuantityAmount;
} The text fields added in the report design “ Page header are for printing the column headings
Step 9 : Test the report. When run the following dialog will open
Select the file name and path using the browse button .
Hit OK. The file will be saved at the location specified. Open the file using MS Excel. It will prompt that the file is in different format and if you still wish to open it using excel, Accept and go ahead.
