Create/ Export Excel BOM Report Template from X++ in AX 2012

BOM Excel Template report:

Path: Product information management/common/release products

Select each grid record whose Production type is BOM or Formula as shown in below screen and click Lines in BOM Engineer action pane or Lines in Formula Engineer action pane

Add Export to excel button on version Group for exporting excel report template

Override Export to Excel click button which will call a new class through action menu item

void clicked()
{
     BOMVersion      BOMVersionlocal;
    str             menuItemStr;
    MenuFunction    menuFunction;
    Args            args = new args();
 
    super();
    //getFirst method gets all the selected records in the grid
    BOMVersionlocal = BOMVersion_ds.getFirst(1,true);
    args.record(BOMVersionlocal);
 
    menuItemStr = menuitemActionStr(SL_BOMExport);   
    menuFunction = new MenuFunction(menuItemStr, MenuItemType::Action);
    if (BOMVersionlocal.RecId != 0)
    {
       menuFunction.run(args);
    }
    else
    {
        warning('No record selected');
    }
 
}

Create a new Class which exporting BOM header and Lines into excel

public static void Main(Args _args)
{
    ItemId          itemid;
    BOMVersion      BOMVersion;
 
    BOMVersion = _args.record();
 
    itemid = BOMVersion.ItemId; 
 
    SL_BOMExport::exportBOM(itemid,BOMVersion.BOMId);
}

public static void exportBOM(ItemId _itemId, BOMId _bomId)
{ 
    BOMVersion               BOMVersion;
    BOM                      BOM;
    Description              personalNumbervalue;
 
    SysExcelApplication     application;
    SysExcelWorkBooks       workbooks;
    SysExcelWorkBook        workbook;
    SysExcelWorksheets      worksheets;
    sysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            SysExcelFont;
 
    int                     row = 1;
 
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();            //gets the workbook object
    workbook = workbooks.add();                     // creates a new workbook
    worksheets = workbook.worksheets();             //gets the worksheets object
    worksheet = worksheets.itemFromNum(1);          //Selects the first worksheet in the workbook to insert data
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@'); // numberFormat ‘@’ is to insert data as Text 
 
    //header
    worksheet.cells().item(1,1).value(CompanyInfo::Find().Name);
    worksheet.cells().item(1,1).font().bold(true);
 
    worksheet.cells().item(2,1).value("List of item formula / BOM");
    worksheet.cells().item(2,1).font().bold(true); 
 
    // bom header
    worksheet.cells().item(4,1).value("BOM");
    worksheet.cells().item(4,1).font().bold(true);
 
    worksheet.cells().item(5,1).value("Name");
    worksheet.cells().item(5,1).font().bold(true);
 
    worksheet.cells().item(6,1).value("Site");
    worksheet.cells().item(6,1).font().bold(true);
 
    worksheet.cells().item(4,4).value("From date");
    worksheet.cells().item(4,4).font().bold(true);
 
    worksheet.cells().item(5,4).value("To date");
    worksheet.cells().item(5,4).font().bold(true);
 
    worksheet.cells().item(6,4).value("From qty");
    worksheet.cells().item(6,4).font().bold(true);
 
    worksheet.cells().item(4,7).value("Active");
    worksheet.cells().item(4,7).font().bold(true);
 
    worksheet.cells().item(5,7).value("Approved by");
    worksheet.cells().item(5,7).font().bold(true);
 
    worksheet.cells().item(6,7).value("Approved");
    worksheet.cells().item(6,7).font().bold(true);
  
     select BOMVersion
           where BOMVersion.ItemId == _itemId
           && BOMVersion.BOMId     == _bomId;
 
        cell = cells.item(4,2);
        cell.value(BOMVersion.BOMId);
 
        cell = cells.item(5,2);
        cell.value(BOMVersion.Name);
 
        cell = cells.item(6,2);
        cell.value(InventDim::find(BOMVersion.InventDimId).InventSiteId);
 
        cell = cells.item(4,5);
        if (BOMVersion.FromDate != dateNull())
        {
            cell.value(BOMVersion.FromDate);
        }
        else
        {
            cell.value("");
        }
 
        cell = cells.item(5,5);
        if (BOMVersion.ToDate != dateNull())
        {
             cell.value(BOMVersion.ToDate);
        }
        else
        {
             cell.value("");
        }
        cell = cells.item(6,5);
        cell.value(BOMVersion.FromQty);
 
        cell = cells.item(4,8);
        cell.value(enum2Value(BOMVersion.Active));
 
        cell = cells.item(5,8);
        personalNumbervalue = HcmWorker::find(BOMVersion.Approver).name();
        cell.value(personalNumbervalue);
 
        cell = cells.item(6,8);
        cell.value(enum2Value(BOMVersion.Approved));
 
    row = 6 ;
    row+=2; 
 
     // header lines
 
       worksheet.cells().item(row,1).value("Item number");
       worksheet.cells().item(row,1).font().bold(true);
 
       worksheet.cells().item(row,2).value("Product name");
       worksheet.cells().item(row,2).font().bold(true);
 
       worksheet.cells().item(row,3).value("Configuration");
       worksheet.cells().item(row,3).font().bold(true);
 
       worksheet.cells().item(row,4).value("Quantity");
       worksheet.cells().item(row,4).font().bold(true);
 
       worksheet.cells().item(row,5).value("Unit");
       worksheet.cells().item(row,5).font().bold(true);
 
       worksheet.cells().item(row,6).value("Per series");
       worksheet.cells().item(row,6).font().bold(true);
 
       worksheet.cells().item(row,7).value("Size");
       worksheet.cells().item(row,7).font().bold(true);
 
       worksheet.cells().item(row,8).value("Color");
       worksheet.cells().item(row,8).font().bold(true);
 
       worksheet.cells().item(row,9).value("Style");
       worksheet.cells().item(row,9).font().bold(true);
  
     while select BOMVersion
           where BOMVersion.ItemId == _itemId
            &&   BOMVersion.BOMId  == _bomId
         join BOM
            where BOMVersion.BOMId == BOM.BOMId
 
    { 
            row++;
 
            cell = cells.item(row,1);
            cell.value(BOM.ItemId);
 
            cell = cells.item(row,2);
            cell.value(BOM.itemNameGrid());
 
            cell = cells.item(row,3);
            cell.value(InventDim::find(BOM.InventDimId).configId);
 
            cell = cells.item(row,4);
            cell.value(BOM.BOMQty);
 
            cell = cells.item(row,5);
            cell.value(BOM.UnitId);
 
            cell = cells.item(row,6);
            cell.value(BOM.BOMQtySerie);
 
            cell = cells.item(row,7);
            cell.value(InventDim::find(BOM.InventDimId).InventSizeId);
 
            cell = cells.item(row,8);
            cell.value(InventDim::find(BOM.InventDimId).InventColorId);
 
            cell = cells.item(row,9);
            cell.value(InventDim::find(BOM.InventDimId).InventStyleId); 
    }
 
    application.visible(true);  // opens the excel worksheet 
}

Happy DAXing

..........