Test Data Transfer tool

1. Download Test Data Transfer Tool
2. Install it on Database server
3. Import MetadataGenerator.XPO in Source AX application. (From where you want to export data)
4. Run job MetadataGenerator and find the  Metadata file
5. Paste Metadata file into [Lists] folder. ( check your installation path in DB server)  it will be like "C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]"
6. To export data from specific company, open SQL Sever management studio and select your source database. Right click and new query. Copy paste below given code into query window. Replace your company name. (Company Name highlighted in bold font). Run the script and copy ouput.
7. Create new text file and paste query output. Save this file with name Filters.xml
8. Put Filters.xml file into [Lists] folder (Path given in step 5)
9. Open command prompt with Run as administrator.
10. Brows the path of DP.exe ("C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)")
11.  Enter below command and hit enter.
DP.exe  Export "D:\Exported Data"  MicrosoftDynamicsAX  AXDBServerName

Filters.xml file content is as below.

-- Declare the variables to store the values returned by FETCH.
DECLARE @tableName varchar(100);
DECLARE @fieldName varchar(100);
SET @fieldName = 'DataAreaID';

DECLARE table_cursor CURSOR FOR
SELECT  Name FROM sys.tables ORDER BY Name;

OPEN table_cursor;

-- Perform the first fetch and store the values in variables. 
PRINT '<tables>'
FETCH NEXT FROM table_cursor
INTO @tableName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- All tables don't have DataAreaId field, So check first that table is having dataareaid filed and then
-- add tableName to xml
IF COL_LENGTH(@tableName, @fieldName) IS NOT NULL
BEGIN
-- Concatenate and display the current values in the variables.
PRINT '<table name="' + @tableName +'"><filter><field>DataAreaID</field>=''USMF''</filter></table>'-- + @tableName --+ ' ' +  @LastName
END
-- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM table_cursor
   INTO @tableName;
END
CLOSE table_cursor;
PRINT '</tables>'
DEALLOCATE table_cursor;
GO  

Rebuild and Update balances through X++

Hi Guys,

If you are posting journals through X++ and facing issues in trial balance update you can use below code to rebuild and update balances.


public void run()
{
    DimensionFocusProcessBalance    dimFocusProcessBal = new DimensionFocusProcessBalance();
    DimensionFocusUpdateBalance     dimFocusUpdateBal = new DimensionFocusUpdateBalance();
    DimensionHierarchy focusDimensionHierarchy;

    while Select focusDimensionHierarchy
        where focusDimensionHierarchy.FocusState == DimensionFocusBalanceInitializationState::Initialized
    {
        dimFocusProcessBal = DimensionFocusProcessBalance::construct(NoYes::No, focusDimensionHierarchy);
        dimFocusProcessBal.run();
        dimFocusUpdateBal = DimensionFocusUpdateBalance::construct(focusDimensionHierarchy);
        dimFocusUpdateBal.run();
    }

    info("Balances Updated !");
}

Export data to EXCEL | AX 2009 | AX 2012 | Excel Report

Hi Techies,

Hope you are doing good. Recently due to INDIA GST Update in Microsoft dynamics AX most of the clients want to export data in excel to prepare GSTR excel files.

I designed a good excel worksheet with X++ and exported data as per requirement. I am sharing with you how can you design excel sheets with the help of X++ code. Please check below.

Step 1. Create a Class. This is class declaration method.

public class MKS_ExportToExcel extends Runbase
{
    #AviFiles
    DialogField         dlgFromDate,dlgToDate;
    TransDate           fromDate,toDate;

    //Excel file declaration
    SysExcelApplication  xlsApplication;
    SysExcelWorkBooks    xlsWorkBookCollection;
    SysExcelWorkBook     xlsWorkBook;
    SysExcelWorkSheets   xlsWorkSheetCollection;
    SysExcelWorkSheet    xlsWorkSheet;
    SysExcelRange        xlsRange;
    int                  row;
    str                  fileName;

    VendInvoiceJour     vendInvoiceJour;

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        fromDate,
        toDate
    #ENDMACRO
}

Step 2. Create Dialog method.

protected Object dialog()
{
    Dialog      dialog = super();
    #resAppl
    ;

    // Set a title for dialog
    dialog.caption('Inward Supply Register Report');
    dialog.addTabPage('Selection Criteria');
    dialog.addGroup('Select date range');
    // Add a new field to Dialog
    dlgFromDate = dialog.addFieldValue(typeid(TransDate), fromDate,"From date");
    dlgToDate   = dialog.addFieldValue(typeid(TransDate), toDate, 'To date');

    return dialog;

}

Step 3. Create getFromDialog method.

public boolean getFromDialog()
{
    ;
    // Retrieve values from Dialog
    fromDate = dlgfromDate.value();
    toDate = dlgToDate.value();

    return super();

}

Step 4. main Method

public static void main(Args _args)
{
     MKS_ExportToExcel reportClass = new MKS_ExportToExcel();

    // Prompt the dialog, if user clicks in OK it returns true
    if (reportClass.prompt())
    {
        reportClass.run();
    }

}

Step 5. run method.

public void run()
{
    ;
    this.setExcelInitialsAndHeader();
    this.fetchDataIntoExcel();
    this.endExcel();

}

Step 6. setExcelInitialsAndHeader - To setup Excel report heading section.

public void setExcelInitialsAndHeader()
{
    COM                     range;
    COM                     autoFit;
    COM                     merge;
    COM                     WrapText;
    COM                     ColumnWidth;
    COM                     RowHeight;

    SysExcelStyles          styles;
    SysExcelStyle           style;
    SysExcelFont            font;
    SysExcelCells           cells;
    FileNameFilter          filter = ["Excel file", "*.xlsx"];
    ;

    row = 1;

    fileName = winapi::getSaveFileName(infolog.hWnd(), filter , @"c:\...\desktop", "Save as Excel file","xlsx","Report name");
    //fileName ="C:\\Windows\\Temp\\test.xlsx";

    if(!fileName)
    return;
    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);
    //throw error("File already exist");


    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();

    //Open Excel document
    //xlsApplication.visible(true);


    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    Styles = xlsWorkBook.styles();
    // Create new style
    style = styles.add('Header');
    // Set font for this style to bold
    font = style.font();
    font.bold(true);
    font.color(51);


    //Excel Report Heading
    range = xlsWorkSheet.range('A1:F1').comObject();
    range.merge();
    RowHeight = range.RowHeight(30);

    xlsWorkSheet.rows().item(1).style('Title');

    range = xlsWorkSheet.range('A2:BE2').comObject();
    xlsWorkSheet.rows().item(2).style('Output');
    xlsWorkSheet.rows().item(2).horizontalAlignment(-4108);
    wrapText = range.wraptext(true);

    ColumnWidth = range.ColumnWidth(14.71);

    xlsWorkSheet.cells().item(row,1).value('GST Report');
    row++;

    //Excel column caption
    xlsWorkSheet.cells().item(row,1).value('Sr. No.');
    xlsWorkSheet.cells().item(row,2).value('Vendor Code');
    xlsWorkSheet.cells().item(row,3).value('Vendor Name');
   
    row++;

}

Step 6. endExcel -  To Setup process while closing excel.

public void endExcel()
{
    ;
    // Set the column width to autoFit
    xlsWorkSheet.columns().autoFit();
    //Save Excel document
    xlsWorkbook.saveAs(fileName);


    xlsWorkbook.comObject().save();
    xlsWorkbook.saved(true);
    //Open Excel document
    xlsApplication.visible(true);

}

Step 7. fetchDataIntoExcel - Used to put data into excel rows.

public void fetchDataIntoExcel()
{
    VendTable               vendTable;
    CompanyInfo             companyInfo = CompanyInfo::find();
    TaxWithholdTrans_IN     tdsTable;
    LedgerJournalTrans      ledgerJournalTrans;

    ;
    progress = new RunbaseProgress();
    progress.setCaption("Export To Excel in progress...");
    progress.setAnimation(#AviTransfer);

    while select vendInvoiceJour
    order by    vendInvoiceJour.InvoiceDate
        where vendInvoiceJour.InvoiceDate >= fromDate
        && vendInvoiceJour.InvoiceDate <= toDate

    {
        vendTable = VendTable::find(vendInvoiceJour.InvoiceAccount);
        progress.setText(strfmt("Vendor %1", vendTable.Name));

        xlsWorkSheet.cells().item(row,1).value(row-2);
        xlsWorkSheet.cells().item(row,2).value(vendInvoiceJour.InvoiceAccount);
        xlsWorkSheet.cells().item(row,3).value(vendTable.name);

row++;
    }


}

Step 8. PACK and UnPack methods.

container pack()
{
    return [#CurrentVersion,#CurrentList];

}


public boolean unpack(container _packedClass)
{
    int version = conPeek(_packedClass,1);

    switch (version)
   {
        case #CurrentVersion:
            [version,#CurrentList] = _packedClass;
            break;
        default:
            return false;
    }
    return true;

}

Ledger Account Setup Dynamics AX 2012

Hi Guys,

The Ledger Account setup is the most important setup for Dynamics AX Implementation.

Below are the Account Types and number sequence (Range), this will help you to arrange accounts in particular range.

Account Type	From	To
Assets	10000	19999
Liabilities	20000	29999
Equities/Funds	30000	39999
Revenue	40000	49999
Expense	50000	59999

Sample segment
Private Sector
Company--Department ---Cost Center ---Account --Project (maybe)

Public Sector
Appropriation Year --Cost Center --Account --Project (May be)

MB6-890 Microsoft Development Introduction - AX7 (Rainer) - Exam Topics

Exam Topics

Understand the Architecture and Development Environment (20% - 25%)

Understand the Architecture and Development Environment

This objective may include but is not limited to: identify features of Microsoft Dynamics AX; describe the development environment; describe the components in the application stack; identify cloud architecture components; explain the server architecture; describe the layer architecture.

Use Microsoft Visual Studio to manage development with Microsoft Dynamics AX

This objective may include but is not limited to: identify the windows and basic navigation of Visual Studio; describe differences between and uses for projects, models, solutions, and packages.

Use the Application Explorer to Develop New Elements (25% - 30%)

Create and manage labels and resources

This objective may include but is not limited to: create new label files; create and use labels; describe elements and uses for labels; identify best practices for labels identify uses for resources.

Create and manage data types

This objective may include but is not limited to: describe uses for base enumerations; create new base enumerations; identify best practices for base enumerations; describe primitive and extended data types; create new extended data types; identify key properties for extended data types; implement best practices for extended data types.

Create and manage tables

This objective may include but is not limited to: identify the components of a table; describe various types of table relationships; describe various types of table indexes; implement best practices for tables, relationships, and indexes.

                                                        

Read, Write and Understand Basic X++ (30% - 35%)

Describe X++

This objective may include but is not limited to: identify the characteristics of X++; use the code editor to write X++; describe the features of IntelliSense; identify basic syntax for X++; describe the use of common key words in X++; describe the features of the debugger; use the comparison tool; use the best practice checker.

Work with X++ control statements

This objective may include but is not limited to: work with variables, operators, conditional statements, and loops; use built-in functions; use communication tools.

Work with classes

This objective may include but is not limited to: create new classes; describe scoping events and parameters; describe inheritance; identify key best practices when writing X++.

Use X++ to manipulate data

This objective may include but is not limited to: identify techniques for data retrieval; explain uses for transaction integrity checking; insert, update, and delete records using X++; identify best practices for manipulating data with X++.

Manage exceptions in X++

This objective may include but is not limited to: identify exception types; use try/catch statements; describe the features of optimistic concurrency control; describe best practices for exception handling with X++ .

Manage the User Interface and Security for Developers  (20% - 25%)

Manage the user interface

This objective may include but is not limited to: identify components of various form patterns; identify common form patterns and sub patterns; identify key properties for form elements; apply and validate form patterns; describe uses for tiles; describe how to join data in forms; implement best practices for form development.

Manage user navigation

This objective may include but is not limited to: identify types of menu items; create menu items and menus; identify key properties for menu items; describe uses for menu items; implement best practices for menus and menu items.

Manage security in the development environment

This objective may include but is not limited to: describe the security architecture; describe the components of role-based security; create new privileges, duties, and roles in the application explorer; describe the extensible data security framework.

AX DB Restore Scripts - Moving AX DB from one server to another server

Following SQL script is very useful while moving AX DB from one server to other server. Thanks to http://www.exploreax.com.

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1
where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS
where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set
    SERVERID=@REPORTSERVERID,
    SERVERURL=@REPORTSERVERURL,
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL,
    SERVERINSTANCE=@REPORTINSTANCE,
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID,
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set
    SERVERNAME=@SSASSERVERNAME,
    DESCRIPTION=@SSASDESCRIPTION,
ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS

reference : http://www.exploreax.com/blog/blog/2015/10/13/ax-db-restore-scripts-full-script/