Import from CSV (Comma Delimited) file with X++

To Import data from CSV file "Comma Delimited" for example inventory journal transactions use the below code assuming the csv file include data as the following table:

Item Id	Site	Warehouse	Qty	Price
10001	Main	RM	55	120

static void ImportfromCSV(Args _args)

{

    Dialog      dialog  = new Dialog();

    DialogField dialogField;

    AsciiIo importFile;

    str filePath,fileNameOnly;

    filetype type;

    container record;

    str         Delimiter = ",";

    int totalRecords;

   

    InventDim               InventDim;

    InventJournalTable      journalTable;

    InventJournalTrans      journalTrans;

    InventJournalTableData  journalTableData;

    InventJournalTransData  journalTransData;

   

    dialogField=dialog.addField(extendedTypeStr(FilenameOpen),"Select File","Select file to import");

    dialog.caption("File Picker");

    dialog.filenameLookupFilter(['csv','*.csv']);

    if(!dialog.run())

        return;

    [filePath, fileNameOnly, type] = fileNameSplit(dialogField.value());

    importFile = new AsciiIo(dialogField.value(), 'R');

    if((!importFile) || (importFile.status() != IO_Status::Ok))

    {

        warning("Error in opening import file");

        throw(Exception::Error);

    }

    importFile.inFieldDelimiter(Delimiter);

    if((!importFile) || (importFile.status() != IO_Status::Ok))

    {

        warning("Error in opening log file");

        throw(Exception::Error);

    }

    try

    {

        ttsbegin;

        journalTableData = JournalTableData::newTable(journalTable);

        journalTransData = journalTableData.journalStatic().newJournalTransData(journalTrans,journalTableData);

        journalTable.clear();

        journalTable.JournalId = journalTableData.nextJournalId();

        journalTable.JournalType = InventJournalType::Movement;

        journalTable.JournalNameId = journalTableData.journalStatic().standardJournalNameId(journalTable.JournalType);

        journalTableData.initFromJournalName(journalTableData.journalStatic().findJournalName(journalTable.JournalNameId));

        journalTable.insert();

       

        record = importFile.read();

        while(importFile.status() ==  IO_Status::Ok)

        {

            record = importFile.read();

            if(!record)

                break;

            totalRecords = totalRecords + 1;

            journalTrans.clear();

            journalTransData.initFromJournalTable();

            journalTrans.TransDate          = today();

            journalTrans.ItemId             = conPeek(record,1);

            journalTrans.Qty                = conPeek(record,4);

            journalTrans.CostPrice          = conPeek(record,5);

            journalTrans.CostAmount         = JournalTrans.Qty * JournalTrans.CostPrice;

           

            // Dimension details

            inventDim.clear();

            inventDim.InventLocationId  = conPeek(record,2);

            inventDim.InventSiteId      = conPeek(record,3);

            journalTrans.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;

           

            journalTransData.create();

        }

        ttscommit;

    }

    catch(Exception::Error)

    {

        Throw(Exception::Error);

    }

    info(strFmt("Total Read Records = %1",totalRecords));

}

Prepare the SharePoint 2010 document library to enable the “Link to Document” content type

 

1.       Open the document library you want to use for publishing a link to the Management Reporter report library. You must be logged in as an account assigned to the Design permission level, or have at least the Manage Web Site permission granted.

2.       Click Library

3.       Click Library Settings

4.       In the General Settings section, click Advanced Settings

5.       In the Content Types section, set "Allow management of content types" to Yes. By default, this is set to "No."

6.       Click OK to return to the Document Library Settings page.

7.       There will now be an additional section titled "Content Types." There is one content type named  "Document" that is added by default. You will now have to add an additional content type. Click Add from existing site content types.

8.       Select All Groups under Select site content types from.

9.       Click to select the Link to a Document content type, click Add and then click OK.

10.   On the document library settings page, you will now see two content types: Document and Link to a Document.

 

 

Prepare the SharePoint 2010 document library to enable publishing of content by the Management Reporter 2012 Process Service account

1.       Determine the account associated with the Management Reporter 2012 Process Service.

a.       On the Management Reporter 2012 server, click Start, point to Administrative Tools and then click Services.

b.      Locate the Management Reporter 2012 Process Service.

c.       Right-click the Management Reporter 2012 Process Service and select Properties.

d.      Click the Log On tab.

e.      Record the domain account that is listed.

Note If a domain account is not selected for the Management Reporter 2012 Process Service, publishing a link to a Management Reporter 2012 report to SharePoint 2010 may not work without special configuration that is not covered by this guide.

2.       Open the document library you want to use for publishing a link to the Management Reporter report library. You must be logged in as a user account that has the Manage Permissions site permission, or be a user who has Full Control permissions such as the “Site Owners” group.

3.       Click the Library tab

4.       Click Library Settings

5.       Click the Library Permissions icon in the Settings section of the toolbar

6.       Depending on the permissions assigned to the document library, one of the following will be true:

The document library is configured to inherit permissions from its parent. If this is true, you will see a message that states This library inherits permissions from its parent.

a.       The document library is not configured to inherit permissions. If this is true, you will see a message that states This library has unique permissions.

b.      You have two options to configure permissions for the document library: Stop inheriting permissions from the parent object and create unique permissions; or manage permissions for the document library and all other objects at the parent level.

                                                               i.      If you want to stop inheriting permissions from the parent object and create unique permissions, continue to step 7 option 1.

                                                             ii.      If you want to manage permissions for the document library and all other objects at the parent level, continue to step 7 option 2.

7.       Options to configure permissions for the document library:

a.       Option 1: Stop the document library from inheriting permissions from the parent

                                                               i.      Click Stop Inheriting Permissions

                                                             ii.      Click OK to the warning that unique permissions will be created

                                                               i.      The document library will now update to say This library has unique permissions. Go to step 8.

b.      Option 2: Manage permissions for the document library and all other objects at the parent level

                                                               i.      Click Manage Parent

                                                             ii.      Go to step 8.

8.       Configure permissions for Management Reporter Process Service account

a.       Click Grant Permissions to open the Grant Permissions Window

b.      Click the People Picker icon to search for the Management Reporter 2012 Process Service account

c.       Type the account name for the Management Reporter 2012 Process Service and press Enter to search. In the results returned, select the correct account name, click Add and then click OK.

d.      In the Grant Permissions section, click Grant Users permission directly and check Design.

Note You may also add the Management Reporter 2012 Process Service account to a SharePoint 2010 group that provides the Design permission. By default for most sites, the Design permission is not provided to a group.

e.      Click OK.

Microsoft Management Reporter 2012 Integration TraceLog

Many people face a lot of issue in data integration but the errors is not enough as the data mart integration log just present error subject, in the following post we will see how to increase the data mart integration trace log level.

1. Navigate to C:\Program Files\Microsoft Dynamics ERP\Management Reporter\2.1\Server\Services\
2. Open the MRServiceHost.exe.config in notepad.
3. Search for <add name="Microsoft.Dynamics.Integration.TraceLog" value="0"/>
4. Change the 0 to a 4, so it looks like this:   <add name="Microsoft.Dynamics.Integration.TraceLog" value="4"/>
5. Restart the MR Process Service.

The log file will capture the error and it may provide some more input to what is failing.

Dynamics AX 2012 purchasing posting types “Purchase Expenditure"

Purchase Expenditure for product and Purchase expenditure, un-invoiced posting types were introduced in AX2012 as part of the two voucher strategy for Purchase Distributions. The purchase expenditure for product is used for invoice posting and the Purchase expenditure, un-invoiced is used for posting of product receipts. Failure to set up these accounts will result in error when attempting to post a product receipt and/or invoice.

The purpose of the new posting accounts is to allow Microsoft Dynamics AX to write an accounting entry for the whole value of the purchase, without any variances, and thus allow it to handle the variances in a separate voucher. This new posting account temporarily records the cost in control account and then moves the cost into ledger account. Effectively, INVENTTRANS credits the Purchase expenditure account for product and then debits Inventory Receipt account.

In the scenario below, without tax or any additional costs or discounts, the following journal entries will be made. Standard cost item for $100USD, Purchased from the vendor for $115USD.

New accounting entries for Microsoft Dynamics AX2012:

Accounts used for receipt:

Voucher 1:

Debit: $115 Purchase expenditure, un-invoiced (total PO amount)

Credit: $115 Purchase, accrual (total PO amount)

Effect: Creates liability for goods received not invoiced (balanced by Purchase expenditure
un-invoiced)

 

Voucher 2:

Debit: $100 Product receipt (standard cost amount)

Debit: $15 Purchase price variance (for the variance amount)

Credit: $115 Purchase expenditure, un-invoiced (total PO amount)

Effect: Creates increase in value of receipted goods and records standard cost deviation (balanced by Purchase expenditure un-invoiced)

 

Accounts used for invoice:

Voucher 1:

Debit: $115 Purchase, accrual – for the whole PO amount

Credit: $115 Purchase expenditure, un-invoiced for the whole PO amount

Effect: Balances the 1^st Product receipt voucher

Voucher 2:

Debit: $115 Purchase expenditure for product (total PO amount)

Credit: $115 Vendor balance (total PO amount)

Effect: Creates entry to Purchase Expenditure for
Product and created vendor liability

Voucher 3:

Debit: $115 Purchase expenditure, un-invoiced (total PO amount)

Debit: $100 Purchase, inventory receipt (standard cost amount)

Credit: $ 100 Product receipt (standard cost amount)

Credit: $ 115 Purchase expenditure for product (total PO amount)

Effect: Create an increase in inventory value and
standard cost variance and balances the product receipt.

Conclusion:

The value of inventory will increase (for a normal purchase) and
where a standard cost item is being used, if there is a variance this will be
recorded in the PPV account and liability to pay the vendor will be generated in
the vendor balance account. The new Purchase expenditure for product and Purchase expenditure, un-invoiced accounts
simply make the previous process in Microsoft Dynamics AX 2009 possible in
conjunction with the new functionality for sub ledger accounting.

How to Use Command Line Scheduling with Management Reporter for Microsoft Dynamics ERP

Management Reporter for Microsoft Dynamics ERP now supports command line report scheduling with the Feature Pack 1 release. This functionality will enable automatic report generation until this functionality is built into the user interface in a future feature pack.

Command line scheduling is available for individual reports or for report groups. When these commands are executed, they are sent to the Process Server exactly like a manually generated report.

Note The command-line options are limited to what is listed here. Any other options such as output type or output location will use the value that is stored in the report definition.

There are three requirements in order to use this feature.

1. If you are using Task Scheduler then the user who is running the task must be a valid Management Reporter user.
2. The Windows user must have selected the save company credentials option for the company the report is being run for.
3. The report cannot prompt for any information during generation. The option to include all units instead of select units at runtime must be selected even if no Reporting Tree is attached.

Commands available:

Note Both the – and /  switches are valid.

Command Argument	Description
/generate “ReportDefinitionName”	This is the name of the Report Definition
/generategroup “ReportGroupName”	This is the name of the Report Group
/reportdate “01/31/2010”	This will be translated to the base period and year in the Report Definition
/?	Return available command line parameters

Example syntax:
ReportDesigner.exe /generate BalSheet /reportdate 3/31/2013

• Report will run for the company the report is attached to
  Example Batch File for Task Scheduler:
  
  “C:\Program Files\Microsoft Dynamics ERP\Management Reporter\2.0\Client\ReportDesigner.exe” -generate BalSheet -reportdate 3/31/2016

Any error messages will be logged in the Windows Application Event Viewer under the source of Management Reporter Report Designer.