How to Open multiple instances of Report at the same time using X++ code in AX 2012

    Many times it is needed to display multiple reports on screen. In standard Dynamics AX 2012, if you try to call multiple times, a report; it will open 2nd report, after 1st instance of report viewer is closed. A user may be looking for, display of multiple instances at same time, without closing other.

Using controller class dialogShow method,

class VendInvoiceAttachmentController extends SrsReportRunController
{
    #define.ReportName('VendInvoiceAttachment.Report')

} 

public static void main(Args _args)
{
    VendInvoiceAttachmentController controller = new VendInvoiceAttachmentController();
 
    controller.parmReportName(#ReportName);
    controller.parmArgs(_args);
    controller.parmLoadFromSysLastValue(false);
    //controller.startOperation();
 
    if (controller.prompt())
    {
        controller.preRunModifyContract();
    }
}
 
protected void preRunModifyContract()
{
    SRSPrintDestinationSettings     settings;
    Query                           query;
    QueryRun                        queryRun;
    VendInvoiceJour                 vendInvoiceJour;
    SRSReportExecutionInfo          executionInfo;
 
    settings = this.parmReportContract().parmPrintSettings();
 
    if (settings.printMediumType() != SRSPrintMediumType::Printer &&
        settings.printMediumType() != SRSPrintMediumType::Screen)
    {
        throw error(strFmt("@$AB59", settings.printMediumType()));
    }
 
    query       = this.getFirstQuery();
    queryRun    = new QueryRun(query);
 
    while (queryRun.next())
    {
        vendInvoiceJour  = queryRun.get(tableNum(VendInvoiceJour));
 
        this.printAttachements(vendInvoiceJour.RecId);
 
        // create a instance of ReportExecutionInfo & set it on contract. This will get used while running report.
        executionInfo = new SRSReportExecutionInfo();
        executionInfo.parmReportRunId(reportRunId);
        reportContract.parmReportExecutionInfo(executionInfo);
 
        //Range
        SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(VendInvoiceJour),fieldNum(VendInvoiceJour, RecId),SysQuery::value(vendInvoiceJour.RecId));
 
        if (settings.printMediumType() == SRSPrintMediumType::Screen)
        {
            // pre report run
            this.parmReportRun().preRunReport();
            this.runToScreen();
        }
 
        if (settings.printMediumType() == SRSPrintMediumType::Printer)
        {
            // pre report run
            this.parmReportRun().preRunReport();
            this.parmReportRun().runReport();
        }
    }
}
 
Run multiple reports to screen
When printing multiple reports to screen in code, the previous printed report will block the next one, untill user closes the previous one, the next one starts rendering. This is anoying if user needs to print multiple reports to screen as a batch. The solution to this is simple, we just need to create a class to extend SrsReportRunController and overwrite the methods dialogShow and dialogClose. See sample below
 
protected void dialogShow()
{
    SysOperationDialog  sysOperationDialog;
    FormRun             formRun;
 
    if (useReportViewerForm)
    {
        dialog.run();
        this.dialogPostRun();
 
        sysOperationDialog  = dialog as SysOperationDialog;
        formRun             = sysOperationDialog.formRun();
        formRun.detach();
    }
    else
    {
        super();
    }

}

protected void dialogClose()
{
    if(!useReportViewerForm)
    {
        super();
    }

}

References:

https://community.dynamics.com/365/financeandoperations/b/microsoftdynamicsaxextensions/posts/open-multiple-instances-of-report-at-same-time

http://xhellot.blogspot.com/2016/08/how-to-run-ssrs-report-from-x-code-in_8.html

Table metadata extract to Excel file using X++ code

Hello all,

Today I want to show you how to extract Table metadata properties i.e Field Name, label, size, EDT

Here is the snippet of code.

 

static void ExtractMetaData(Args _args)
{
    // Metadata related variables
    DictTable                   table;
    DictIndex                   dictIndex;
    DictField                   field;
    DictEnum                    enum;
    DictType                    type;
    DictRelation                relation;
    int                         i = 0, j,k;
    SQLDictionary               sqlDictionary;
    Tablename                   _tablename;
    str                         enumDetails;
    #DictField

    // Excel related variables
    int                         rowNum = 1,rowNum2;
    Filename                    _filename;
    SysExcelApplication_2007    excel;
    SysExcelWorkBook_2007       workBook;
    SysExcelWorkSheet           workSheet;
    SysExcelCells               cells;
    #Excel

    // User interaction related variables
    Dialog                      dialog;
    DialogField                 dialogField_Table, dialogField_FilePath;

    ;

    // Get Table name and file save path from user
    /*dialog = new Dialog();
    dialog.caption('Metadata Extractor');
    dialogField_Table = dialog.addField(typeid(TableName));
    dialogField_Table.label('Table name');
    dialogField_FilePath = dialog.addField(typeid(FilePath));
    dialogField_FilePath.label('Directory to create output file');

    if(dialog.run())
    {
        _tablename = dialogField_Table.value();
        _filename = dialogField_FilePath.value() + '\\'+ _tablename + '_Metadata.xlsx';
    }
    else
        throw error::missingParameter(dialog);*/

    _tablename = "DMFSubledgerEntityOCP";
    _filename = "D:\\DMF\\SubledgerBalances.xlsx";

    // Start execution
    try
    {
        // Create instance of the excel application
        excel = SysExcelApplication_2007::construct(new COM(#excel));

        // Create a excel workbook
        workBook = excel.workbooks().add();

        // Name the 1st worksheet in the workbook
        workBook.worksheets().itemFromNum(1).name(_tableName);//_tableName

        // Delete the other worksheets from the workbook
       /*worksheet = workBook.worksheets().itemFromNum(2);
        worksheet.delete();
        workSheet = workBook.worksheets().itemFromNum(2);
        workSheet.delete();*/

        // Access the cells of the 1st worksheet in the workbook and add the title row
        cells = workBook.worksheets().itemFromNum(1).cells();
        cells.item(1,rowNum).value('Field Name');
        cells.item(2,rowNum).value('Field Label');
        cells.item(3,rowNum).value('Data Type');
        cells.item(4,rowNum).value('Length');
        cells.item(5,rowNum).value('Unique Field');
        cells.item(6,rowNum).value('Mandatory Field');
        cells.item(7,rowNum).value('Reference Field');
        cells.item(8,rowNum).value('Reference Table');
        cells.item(9,rowNum).value('Other constraints');

        // Iterate through all the fields of the table and add the metadata to the excel worksheet
        while select sqlDictionary
       //order by fieldId
        where
        sqlDictionary.fieldId != 0 &&
        sqlDictionary.tabId == tableName2Id(_tableName)
        {
            // Increment the working row
            rowNum++;

            // Retrieve the "DictField" object for reference
            field = new DictField(sqlDictionary.tabId,sqlDictionary.fieldId);

            // Populate the excel with metadata
            cells.item(1,rowNum).value(field.name()); //Field Name
            cells.item(2,rowNum).value(field.label()); //Data Type
            cells.item(3,rowNum).value(enum2str(sqlDictionary.fieldType)); //Data Type

            // Length of a field is only populated if it is exists
            if(sqlDictionary.strsize != 0)
                cells.item(4,rowNum).value(sqlDictionary.strSize); //Length

            // Retrieve the "DictType" object for reference
            type = new DictType(field.typeId());

            // Populate the excel with metadata
            if(bittest(field.flags(),#DBF_MANDATORY))
                cells.item(6,rowNum).value('Yes'); //Mandatory field

            // Check if the field has a EDT attached to it
            if(type)
            {
                // Extract the relationship defined on the EDT
                relation = type.relationObject();

                // Proceed if a relationship exists
                if(relation)
                {
                    // Populate the excel with metadata
                    cells.item(7,rowNum).value(fieldid2name(relation.table(),relation.lineExternTableValue(1))); //Reference Field
                    cells.item(8,rowNum).value(tableid2name(relation.table())); //Reference Table
                }
            }

            // Check if the field has a Enum attached to it
            if(field.enumId())
            {
                // Retrieve the "DictEnum" object for reference
                enum = new DictEnum(field.enumId());

                // Initialize variable to temporarily store enum values
                enumDetails = 'Possible values = ';

                // Extract the enum values into the temporary variable
                for(i=0;i<enum.values();i++)
                {
                    enumDetails = enumDetails +'\n'+ int2str(enum.index2Value(i)) + ' - ' + enum.index2Label(i);
                }

                // Populate the excel with metadata
                cells.item(9,rowNum).value(enumDetails); //Other constraints
            }
        }

        // Retrieve the "DictTable" object for reference
        table = new DictTable(tableName2Id(_tableName));
  /*
        // Loop through all the indices of the table
        for (j=0;j<table.indexCnt();j++)
        {
            // Retrieve the "DictIndex" object for reference
            dictIndex = new DictIndex(tableName2Id(_tableName),table.indexCnt2Id(j));

            // Check if the index is a unique index
            if(dictIndex.allowDuplicates() == NoYes::No)

                for(k=1;k<=dictIndex.numberOfFields();k++) // Iterate through all the fields of the index
                {
                    // Find the position of the field in the worksheet and set the Unique Index property to Yes
                    cells.item(4,(cells.range('A:A').find(fieldid2name(table.id(),dictIndex.field(k))).column())).value('Yes');
                }

        }
   */
        // Format the worksheet - Make the header row grey and bold, and autofit the columns
        workBook.styles().add('1').interior().color(WinApi::RGB2int(190, 190, 190));
        workBook.styles().item(1).font().bold(true);
        workBook.worksheets().itemFromNum(1).range('A1:H1').style('1');
        workBook.worksheets().itemFromNum(1).columns().autoFit();

        // Save the workbook and present it to the user
        workBook.saved(true);
        //workBook.saveAs(_filename);
        excel.visible(true);
    }
    catch(Exception::Error)
    {
        throw error("error");
    }

}

D365 F&O Standard

Table - DataFeedsTableCatalog

/// <summary>

/// This table store a list of tables that can be used to configure a data feed

/// </summary> 

 

/// <summary>

    /// resets the contents of the table catalog

    /// </summary>

    internal static void resetTableCatalog()

    {

        DataFeedsTableCatalog tableCatalog;

        delete_from tableCatalog;

        ttsbegin;

        System.Collections.IEnumerator tableCollectionEnumerator = DataFeedsTableCatalog::getTableQueryEnumerator();

        while (tableCollectionEnumerator.MoveNext())

        {

            DataFeedsTableCatalog::createTableRefRecord(tableCollectionEnumerator.Current as Microsoft.Dynamics.AX.Metadata.NodeLib.Node);

        }

 

        ttscommit;

    }

 

    /// <summary>

    /// Gets the table query enumerator.

    /// </summary>

    /// <returns>The instance of the <c>System.Collections.IEnumerator</c> class that can iterate table nodes.</returns>

    internal static System.Collections.IEnumerator getTableQueryEnumerator()

    {

        System.Collections.IEnumerator tableCollectionEnumerator;

 

        var query = Microsoft.Dynamics.AX.Metadata.NodeLib.Specialized.TableQuery::Construct();

        query.AllowTemporary = false;

        query.AllowSystem    = true;

        tableCollectionEnumerator = query.GetEnumerator();

       

        return tableCollectionEnumerator;

    }

 

    internal static void createTableRefRecord(Microsoft.Dynamics.AX.Metadata.NodeLib.Node _node)

    {

        DataFeedsTableCatalog tableCatalog;

        SysDictTable    dictTable = new SysDictTable(tableName2Id(_node.Name));

        tableId         tableId   = dictTable.id();

       

        if (dictTable && dictTable.enabled())

        {

            // don't include views, temp tables, in memory tables, dixf staging tables

            if ( DictTable.isView() || dictTable.tableType() != TableType::Regular || dictTable.tableGroup() == TableGroup::Staging)

            {

                return;

            }

 

            tableCatalog.TableIdValue = tableId;

            tableCatalog.TableObjectName = dictTable.name();

            tableCatalog.TableLabel = dictTable.label();

            tableCatalog.TableConfigurationKey = dictTable.getRootConfigKeyLabel();

            tableCatalog.SavePerCompany = dictTable.dataPrCompany();

            tableCatalog.TableGroup = dictTable.tableGroup();

            var tableMetadata = Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetTable(tableCatalog.TableObjectName);

            tableCatalog.Tags = tableMetadata.Tags;

            tableCatalog.insert();

        }

    }

Create Item and setup using x++ code

    public void CreateItemAndSetup()

    {

        InventTable                                     inventTable;

        InventTableModule                               inventTableModule;

        InventItemSetupSupplyType                       inventItemSetupSupplyType;

        InventModelGroupItem                            inventModelGroupItem;

        InventItemGroupItem                             inventItemGroupItem;

        NumberSequenceTable                             numberSequenceTable;

        EcoResProduct                                   ecoResProduct;

        EcoResDistinctProduct                           ecoResDistinctProduct;

        EcoResProductIdentifier                         ecoResProductIdentifier;

        EcoResStorageDimensionGroupProduct              ecoResStorageDimensionGroupProduct;

        EcoResTrackingDimensionGroupProduct             ecoResTrackingDimensionGroupProduct;

        EcoResStorageDimensionGroupItem                 ecoResStorageDimensionGroupItem;

        EcoResTrackingDimensionGroupItem                ecoResTrackingDimensionGroupItem;

        DataAreaId                                      _dataAreaId=curext();

        ItemId                                          _itemid;

        ItemName                                        _itemname;

        if (EcoResProduct::findByProductNumber(_itemid).RecId == 0)

        {

            //Product

            ecoResDistinctProduct.clear();

            ecoResDistinctProduct.initValue();

            ecoResDistinctProduct.ProductType = EcoResProductType::Item;

            ecoResDistinctProduct.DisplayProductNumber  = _itemid;

            ecoResDistinctProduct.SearchName            = _itemname;

            if (ecoResDistinctProduct.validateWrite())

            {

                ecoResDistinctProduct.insert();

                ecoResProductIdentifier.clear();

                ecoResProductIdentifier.initValue();

                ecoResProductIdentifier.ProductNumber   = _itemid;

                ecoResProductIdentifier.Product         = ecoResDistinctProduct.RecId;

                ecoResProductIdentifier.insert();

                //Released Product

                select firstOnly ecoResProduct

                    where EcoResProduct.DisplayProductNumber == _itemid;

                //Storage dimension group

                ecoResStorageDimensionGroupProduct.clear();

                ecoResStorageDimensionGroupProduct.initValue();

                ecoResStorageDimensionGroupProduct.Product               = ecoResProduct.RecId;

                ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName("SiteWH").RecId;

                if (ecoResStorageDimensionGroupProduct.validateWrite())

                {

                    ecoResStorageDimensionGroupProduct.insert();

                }

                //Tracking dimension group

                ecoResTrackingDimensionGroupProduct.clear();

                ecoResTrackingDimensionGroupProduct.initValue();

                ecoResTrackingDimensionGroupProduct.Product                = ecoResProduct.RecId;

                ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName("None").RecId;

                if (ecoResTrackingDimensionGroupProduct.validateWrite())

                {

                    ecoResTrackingDimensionGroupProduct.insert();

                }

                if (_itemname)

                {

                    EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProduct.RecId, _itemname, _itemname);

                }

                inventTable.clear();

                inventTable.initValue();

                inventTable.initFromEcoResProduct(ecoResProduct);

                inventTable.ItemId      = ecoResProduct.productNumber();

                inventTable.NameAlias   = ecoResProduct.SearchName;

                inventTable.insert(true);

                // Create inventTableModules

                inventTableModule.clear();

                inventTableModule.initValue();

                inventTableModule.ItemId        = inventTable.ItemId;

                inventTableModule.ModuleType    = ModuleInventPurchSales::Invent;

                inventTableModule.Price         = 10;

                inventTableModule.insert();

                inventTableModule.clear();

                inventTableModule.initValue();

                inventTableModule.ItemId        = inventTable.ItemId;

                inventTableModule.ModuleType    = ModuleInventPurchSales::Purch;

                inventTableModule.insert();

                inventTableModule.clear();

                inventTableModule.initValue();

                inventTableModule.ItemId        = inventTable.ItemId;

                inventTableModule.ModuleType    = ModuleInventPurchSales::Sales;

                inventTableModule.insert();

                //Inventory model group

                inventModelGroupItem.clear();

                inventModelGroupItem.initValue();

                inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;

                inventModelGroupItem.ItemId         = inventTable.ItemId;

                inventModelGroupItem.ModelGroupId   = "FIFO";

                inventModelGroupItem.ModelGroupDataAreaId = curext();

                inventModelGroupItem.insert();

                //Item group

                inventItemGroupItem.clear();

                inventItemGroupItem.initValue();

                inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;

                inventItemGroupItem.ItemId         = inventTable.ItemId;

                inventItemGroupItem.ItemGroupId    = "Sevices";

                inventItemGroupItem.ItemGroupDataAreaId = curext();

                inventItemGroupItem.insert();

                //Create inventItemLocation

                InventItemLocation::createDefault(inventTable.ItemId);

                // Creates a new item default order type for the product that is released.

                inventItemSetupSupplyType.clear();

                inventItemSetupSupplyType.initValue();

                inventItemSetupSupplyType.ItemId            = inventTable.ItemId;

                inventItemSetupSupplyType.ItemDataAreaId    = inventTable.DataAreaId;

                inventItemSetupSupplyType.insert();

                //create relationship tables to dimension groups.

                ecoResStorageDimensionGroupProduct  = EcoResStorageDimensionGroupProduct::findByProduct(ecoResProduct.RecId);

                ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResProduct.RecId);

                // mandatory storage dimension group for the product

                if (ecoResStorageDimensionGroupProduct.RecId)

                {

                    ecoResStorageDimensionGroupItem.clear();

                    ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

                    ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;

                    ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;

                    ecoResStorageDimensionGroupItem.insert();

                }

                // mandatory tracking dimension group for the product

                if (ecoResTrackingDimensionGroupProduct.RecId)

                {

                    ecoResTrackingDimensionGroupItem.clear();

                    ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

                    ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;

                    ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;

                    ecoResTrackingDimensionGroupItem.insert();

                }

                info(strfmt("Product successfully released to UsMf legal entity"));

            }

        }

    }

       /// <summary>

    ///  To create or update service orderable attribute YES or NO

    /// </summary>

    public void createOrUpdateProductAttribute()

    {

        EcoResProduct                       product;

        EcoResDistinctProductVariant        variant;

        EcoResProductInstanceValue          ecoResProductInstanceValue;

        EcoResAttributeType                 ecoResAttributeType;

        EcoResAttribute                     ecoResAttribute;

        EcoResAttributeValue                ecoResAttributeValue;

        EcoResBooleanValue                  ecoResBooleanValue;

        ItemId                              itemId;

        EcoResCategoryName                  categoryName;

        RefRecId                            itemRecID;

        str                                 attributeName;

        Boolean                             attributeValue;

        itemId          = this.ItemNumber;

        attributeName   = "@BC:ServiceOrderableLbl";

        attributeValue  = this.BCServiceOrderable;

        categoryName    = this.BCEcoResCategoryName;

        product = EcoResProduct::findByProductNumber(itemId);

        //Get Product by Itemid

        if(product)

        {

            ttsbegin;

            if(product.getInstanceRelationType() == tableStr(EcoResDistinctProductVariant))

            {

                variant = product as EcoResDistinctProductVariant;

                product = EcoResProduct::find(variant.ProductMaster);

            }

            //Find records in ecoResProductInstanceValue if not insert new

            ecoResProductInstanceValue = EcoResProductInstanceValue::findByProduct(product.recid);

            if(!ecoResProductInstanceValue)

            {

                //insert records in ecoResProductInstanceValue

                ecoResProductInstanceValue.product = product.Recid;

                ecoResProductInstanceValue.insert();

            }

            //Check if record exists in EcoResAttributeValue

            ecoResAttributeType     = EcoResAttributeType::findByName("Boolean");

            ecoResAttribute         = EcoResAttribute::findByName(attributeName, ecoResAttributeType.RecId);

            ecoResAttributeValue    = EcoResAttributeValue::findByInstanceAttribute(ecoResProductInstanceValue.recid, ecoResAttribute.recid, true);

           

            if(ecoResAttributeValue)

            {

                //If EcoResBooleanValue exists, check it doesn't have the same value

                if(EcoResBooleanValue::find(ecoResAttributeValue.Value).BooleanValue != attributeValue)

                {

                    //create a record in EcoresBooleanvalue

                    ecoResBooleanValue.clear();

                    ecoResBooleanValue.BooleanValue = attributeValue;

                    ecoResBooleanValue.insert();

  

                    //Update ecoResAttribute

                    ecoResAttributeValue.Value = ecoResBooleanValue.recid;

                    ecoResAttributeValue.update();

                }

            }

            //if ecoresAttributeValue not exists

            else

            {

                //Always is necessary to insert a new EcoResValue record per attribute (1:1)

                ecoResBooleanValue.clear();

                ecoResBooleanValue.BooleanValue = attributeValue;

                ecoResBooleanValue.insert();

 

                //insert record in ecoResAttributeValue

                ecoResAttributeValue.clear();

                ecoResAttributeValue.InstanceValue  = ecoResProductInstanceValue.recid;

                ecoResAttributeValue.Attribute      = ecoResAttribute.recid;

                ecoResAttributeValue.Value              = ecoResBooleanValue.Recid;

                ecoResAttributeValue.insert();

                

            }

            ttsCommit;

        }

    }

    /// <summary>

    ///  To create product categories 

    /// </summary>

    public void createProductCategory()

    {

        EcoResCategoryHierarchy     ecoResCategoryHierarchy;

        EcoResCategory              ecoResCategory;

        EcoResProductCategory       selectedEcoResProductCategory;

        EcoResProduct               ecoResProduct;

        EcoResTextValue             textValue;

        ecoResCategoryHierarchy = EcoResCategoryHierarchy::findByName(this.BCEcoResCategoryHierarchyName);

        ecoResCategory          = EcoResCategory::findByName(this.BCEcoResCategoryName, ecoResCategoryHierarchy.Recid);

        if (ecoResCategory.RecId)

        {

            ecoResProduct = InventTable::find(this.ItemNumber).Product();

            select  firstonly selectedEcoResProductCategory

                where selectedEcoResProductCategory.CategoryHierarchy == ecoResCategory.CategoryHierarchy

                &&  selectedEcoResProductCategory.Product             == ecoResProduct.RecId;

            if (selectedEcoResProductCategory.RecId)

            {

                selectedEcoResProductCategory.selectForUpdate(true);

                ttsbegin;

                selectedEcoResProductCategory.Category = ecoResCategory.RecId;

                selectedEcoResProductCategory.update();

                ttscommit;

            }

            else

            {

                EcoResProductCategory::insertEcoResProductCategory(ecoResCategory, ecoResProduct);

            }

        }

    }