Monday, July 4, 2016

How to upload data into MS Dynamics Ax 2012 R2/R3

Though there are many ways to upload data into MS Dynamics Ax, one of the way is by making job in MS Dynamics Ax, though it is a traditional way.
Let us assume that the data to be uploaded is available in CSV format. Also assume that the data to be uploaded is a list of city under different states, as shown below:

  
In this case data to be uploaded are:
1.       CountryRegionId in Column number 2
2.       Description in Column number 4
3.       Name in Column number 7
4.       StateId in Column number 9
To write a job in MS Dynamics Ax, go to the AOT by pressing CTRL+D. Expand the under Jobs. 


Add a new job by right clicking on the Job node.

Then add the following code in the code window.

Structure of the Table is:

Sample Code:
static void zz_SNMJob_ImportLogisticAddressCity(Args _args)
{
    LogisticsAddresssCity   logisticsAddresssCity;
    LogisticsAddresssCity   uploadLogisticsAddresssCity;
    Name                    name;

    CommaIO                     csvFile;
    container                   readCon;
    counter                     icount, inserted;
    Dialog                      dialog;
    DialogField                 dfFileName;
    FileName                    fileName;
    #File
    dialog     = new Dialog("Pick the file");

    dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
    dialog.filenameLookupFilter(["All files", #AllFiles]);

    if(dialog.run())
      {
         filenamedfFileName.value();
      }
         csvFile  =  new CommaIO(filename,'r');

        if(csvFile)
      {
        readCon  = csvFile.read();
        ttsBegin;
        while (csvFile.status() == IO_Status::OK)
        {
            readCon  = csvFile.read();
            name = conPeek(readCon, 7);
            if(readCon)
                {
                    select LogisticsAddresssCity where logisticsAddresssCity.Name == name;
                    if(!LogisticsAddresssCity)
                    {

                        info(strFmt("%1 %2", conPeek(readCon, 4), conPeek(readCon, 7)));

                        uploadLogisticsAddresssCity.clear();
                        uploadLogisticsAddresssCity.CountryRegionId = conPeek(readCon, 2);
                        uploadLogisticsAddresssCity.Description = conPeek(readCon, 4);
                        uploadLogisticsAddresssCity.Name = conPeek(readCon, 7);
                        uploadLogisticsAddresssCity.StateId = conPeek(readCon, 9);
                        uploadLogisticsAddresssCity.Insert();
                        inserted++;
                        icount++;
                    }
            }
        }
        ttsCommit;
      }

    info(strFmt("%1 records has been imported out of %2", inserted, icount));
}

Explanation of the Code:
LogisticsAddresssCity   logisticsAddresssCity;
LogisticsAddresssCity   uploadLogisticsAddresssCity;
Declare two variable / object from the Table Class LogisticsAddressCity, as the data to be uploaded into the Table named LogisticsAddressCity. The first variable could be used to store or search for a name of city already existed into the database or not. And the other (uploadLogisticsAddresssCity) will be used for uploading one record.
Name                    name;
The variable name will be used for storing the name from the CSV file to search from the table if it exists or not.
CommaIO                  csvFile;
container                   readCon;
counter                      icount, inserted;
Dialog                         dialog;
DialogField                dfFileName;
FileName                   fileName;
#File  dialog = new Dialog("Pick the file");
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
For handling CSV file and Dialog Box these commands are used.
if(dialog.run())
To run the Dialog Box – dialog.run() is used, and if it is a success without any error, then…
      {
         filenamedfFileName.value();
      }
      csvFile  =  new CommaIO(filename,'r');
Get the CSV file as an Object, in a read only mode.
      if(csvFile)
      {
        readCon  = csvFile.read();
If the CSV File is having no erorr as far as its input and output access is concerned then read the content of the file as one line at a time.
        ttsBegin;
        while (csvFile.status() == IO_Status::OK)
        {
            readCon  = csvFile.read();
            name = conPeek(readCon, 7);
            if(readCon)
Begin the transaction processing by issuing a command ttsBegin.
If the status of the file is OK that is not EOF, then read the column 7 where the name of the City is there and put assign the value to the variable ‘name’. Then read the content and if the content is healthy then go further.
                    select LogisticsAddresssCity where logisticsAddresssCity.Name == name;
                    if(!LogisticsAddresssCity)
Get data from the table containing names of City (LogisticsAddressCity) where the name is same as it is contained in variable ‘name’. If the name of the city does not exists, then go further.
                        info(strFmt("%1 %2", conPeek(readCon, 4), conPeek(readCon, 7)));

                        uploadLogisticsAddresssCity.clear();
                        uploadLogisticsAddresssCity.CountryRegionId = conPeek(readCon, 2);
                        uploadLogisticsAddresssCity.Description = conPeek(readCon, 4);
                        uploadLogisticsAddresssCity.Name = conPeek(readCon, 7);
                        uploadLogisticsAddresssCity.StateId = conPeek(readCon, 9);
                        uploadLogisticsAddresssCity.Insert();
                        inserted++;
                        icount++;
For information purpose put the string into the info function about the values from the column 4 and 7 of the CSV file.
Then first clear the table variable uploadLogisticsAddresssCity, then put the values to the data field for that table variable. Insert the data into the table by using insert() method of the table class.
For the purpose of counting two counters were used (inserted and icount).
        ttsCommit;
Each transaction processing is committed into the database by ttsCommit command.
    info(strFmt("%1 records has been imported out of %2", inserted, icount));
At last another info function is used to show how many records were inserted.

By running the job we get:

On Finishing:

The data is uploaded into the table:

Friday, November 27, 2015

How to Create SSRS Report with using SQL Query or SQL Procedure MS Dynamics Ax 2012 R2

There are different ways to create SSRS report in MS Dynamics Ax 2012, mostly depending upon different type of data source used for creating report. There are several ways to retrieve data for reports. One of them is to use the Report Data Provider Class or RDP Class. To process the SQL Query or Procedure, a processing class is needed. This class will get a SQL Query string as a parameter and process the string to get a desired Result Set. Though the result set is not at all like data set, get from creating data query class in business logic.

What is the need to process SQL Query or Procedure instead of X++?
Firstly, sometimes the query becomes so complex, this becomes a difficult for novice or moderate programmer to make and test the query. It is easier to make and test SQL Queries in the Management Studio for SQL Server.

Secondly, the SQL Server processes query faster than the metadata query of X++.

Please consider the following Query.

SELECT V.*, INVENTDIM.INVENTCOLORID, INVENTDIM.INVENTSIZEID, INVENTDIM.INVENTSTYLEID, INVENTTABLE.NAMEALIAS FROM (
SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, sum(fQtyO) AS fQtyO, SUM(fAmtO) AS fAmtO, sum(fQTYR) AS fQtyR, sum(fAmtR) as fAmtR, sum(fQtyI) as fQtyI, sum(fAmtI) as fAmtI
FROM
(
SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, sum(QTY) AS fQtyO, sum(COSTAMOUNTPOSTED) AS fAmtO, 0 AS fQTYR, 0 AS fAmtR, 0 AS fQtyI, 0 AS fAmtI
FROM         TRI_INVENTDIMTRANSORIGINVIEW
WHERE     (DATEPHYSICAL > CONVERT(DATETIME, '2010-04-01 00:00:00', 102)) AND (DATEPHYSICAL < CONVERT(DATETIME, '2015-04-01 00:00:00', 102))
GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID
HAVING      (INVENTLOCATIONID = N'Main Store - BLB')
UNION
SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, 0 AS fQtyO, 0 AS fAmtO, sum(QTY) AS fQTYR, sum(COSTAMOUNTPOSTED) AS fAmtR, 0 AS fQtyI, 0 AS fAmtI
FROM         TRI_INVENTDIMTRANSORIGINVIEW AS TRI_INVENTDIMTRANSORIGINVIEW_1
WHERE     (DATEPHYSICAL >= CONVERT(DATETIME, '2015-04-01 00:00:00', 102)) AND (DATEPHYSICAL <= CONVERT(DATETIME, '2015-04-30 00:00:00', 102)) AND qty>0
GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID
HAVING      (INVENTLOCATIONID = N'Main Store - BLB')
UNION
SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, 0 AS fQtyO, 0 AS fAmtO, 0 AS fQTYR, 0 AS fAmtR, sum(QTY) AS fQTYI, sum(COSTAMOUNTPOSTED) AS fAmtI
FROM         TRI_INVENTDIMTRANSORIGINVIEW AS TRI_INVENTDIMTRANSORIGINVIEW_1
WHERE     (DATEPHYSICAL >= CONVERT(DATETIME, '2015-04-01 00:00:00', 102)) AND (DATEPHYSICAL <= CONVERT(DATETIME, '2015-04-30 00:00:00', 102))  and qty<0
GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID
HAVING      (INVENTLOCATIONID = N'Main Store - BLB')
) T
GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID
) V
LEFT OUTER JOIN
INVENTDIM ON V.INVENTDIMID  = INVENTDIM.INVENTDIMID
LEFT OUTER JOIN
INVENTTABLE ON V.ITEMID   = INVENTTABLE.ITEMID
ORDER BY V.ITEMID


To process 171 GB of Data it takes 1 Second in SQL Server Management Studio. This is not in the case of processing X++ Query.


Thirdly, it is a bit popular among the Dot Net programmer and SSRS Report Developers.

Anyhow, the SQL Reporting Services solution for Microsoft Dynamics AX 2012 includes a data source type called Report Data Provider (RDP) which can be used to build reports that have data from an X++ class as the source. Custom business logic to be rendered in reports using predefined X++ classes. Also, can bind parameter elements to Report Definition Language (RDL) expressions using the expression editor.


In this walk-through we will create, publish a report in the development server, then we will export, import and publish the report on the live server.

To do this the following set of tasks will be needed:
1.    Create a Shared Project
2.    Create a Table
3.    Create a SQL Utility Class
4.    Test the SQL Utility Class through a JOB
5.    Create a Data Contract Class
6.    Create a Report Data Provider (RDP) Class
7.    Create a reporting project in Visual Studio
8.    Create a report
9.    Build and Publish the Report
10. Export the Project from the Development and Testing Server
11. Import the Project into the Live Server
12. Re-Compile the Visual Studio Project and Publish the report with proper administrator rights to the Live Server.
Pre-Requisites
To complete this example, the followings are required:
·         Microsoft Dynamics AX with sample data
·         Microsoft Visual Studio 2010

STEP 1 - Create a Shared Project
Creating a Shared Project help in moving the reports from Development and Testing Environment to the Live servers. For this example, a shared project is defined within the Microsoft Dynamics AX development environment (AOT – can be opened by Pressing CTRL+D while running the MS Dynamics Client Application, and having proper credential).
The following procedure explains how to create a shared project.
To create a shared project
1.    Open Microsoft Dynamics AX.
2.    In the AOT, right-click the Queries node, and then click New Query.
3.    Select Project Menu Item from View Menu Group or press CTRL+SHIFT+P.
4.    Expand the Shared Folder
5.    Right Click and add a New Project, name it as zz_SNMSingleSourceVendor

STEP 2 – Create a Table
The table will hold data from the business logic class temporarily to render the data to the
SSRS report. As the approximate count of row will not be more than 1000 so an InMemory
Table Type could be used in this case.
To create a temporary Table
1.    In the AOT, Expand the Data Dictionary node, then right-click on the Table node, and then click New Table.
2.    Name the Table as zz_SNMSingleSourceVendorTmp
3.    In the TableType put InMemory Value from the dropdown.

4.       Add required Fields, by either creating as a new field or by dragging the Field name from tables which contains field (from where data is called in the business logic class)
5.       Create two fields fName having type string of 60 character, and fItem having type string 250 character.
6.       Add the newly created table to the project, by dragging the table into the project window.


STEP 3 – Create a SQL Utility Class
The SQL Utility Class will get a string as a parameter and process the string so as to get a Result Set. Please note that ResulSet is a Class in itself.
The ResultSet class provides access to a table of data generated by executing a Statement. Has different methods like cancelTimeOut, close, next, etc. The String could be a validated SQL Query Statement or name of a Procedure with Parameters.
To create a SQL Utility Class
1.    In the AOT, right-click the Classes node, and then click New Class.
2.    Name the Class as zz_SNMSQLUtility. Please note that do not create name of any class with first character as _, as it will not be processed by C# compiler while making the Visual Studio Project.
3.       Now create two methods, classDeclaration, executeQuery, as shown below.
No need to create the executeUpdate.

server static ResultSet executeQuery(str sql, Connection connection = new Connection())
{
    SqlStatementExecutePermission   sqlPerm;
    Statement                       statement;
    ResultSet                       resultSet;
    ;
    sqlPerm = new SqlStatementExecutePermission(sql);
    sqlPerm.assert();
    statement = connection.createStatement();
    resultSet = statement.executeQuery(sql);
    CodeAccessPermission::revertAssert();
    return resultSet;
}
STEP 4 – Test the SQL Utility Class through a JOB
The SQL Utility Class could be tested by creating a JOB and then running the JOB.

To create a JOB
1.    In the AOT, right-click the Jobs node, and then click New Job.
2.    Paste the following codes to test.



Listing of the Codes
static void Job44(Args _args)
{
    ResultSet       testRS;
    ;
    testRS = zz_SNMSQLUtility::executeQuery("SELECT SALESPOOLID, NAME FROM SALESPOOL");
    while(testRS.next())
    {
        info(testRS.getString(2));
    }
}

The same could be tested for a SQL Stored Procedure. The following Stored Procedure does the same as done in the SQL Query above.





STEP 5 – Create a Data Contract Class
The Data Contract Class will define the parameters, class for the parameters, and validation.

To create a Data Contract Class
1.    In the AOT, right-click the Classes node, and then click New Class.
2.    Name the Class as zz_SNMSingleSourceVendorContract. Please note that do not create name of any class with first character as _, as it will not be processed by C# compiler while making the Visual Studio Project.
3.    Now create three methods, classDeclaration, parmFromDate, parmToDate, as shown below.




Listing of the Codes
class zz_SNMSingleSourceVendorContract
{
    DatePhysical               FromDate;
    DatePhysical               ToDate;
}

[
    DataMemberAttribute('FromDate'),
    SysOperationLabelAttribute('From Date :'),
    SysOperationDisplayOrderAttribute('1')
]
public DatePhysical parmFromDate(DatePhysical _fromDate = FromDate)
{
    FromDate = _fromDate;
    return FromDate;
}

[
    DataMemberAttribute('ToDate'),
    SysOperationLabelAttribute('To Date :'),
    SysOperationDisplayOrderAttribute('2')
]
public DatePhysical parmToDate(DatePhysical _toDate = ToDate)
{
    ToDate = _toDate;
    return ToDate;
}

public boolean validate()
{
    if (!this.parmFromDate() || !this.parmToDate())
    {
        // The From date field and the To date field must be filled in.
        return checkFailed("@SYS95151");
    }

    if (this.parmToDate() && this.parmFromDate() > this.parmToDate())
    {
        // From date must be before To date
        return checkFailed("@SYS91020");
    }
    return true;
}
STEP 6 – Create a Data Report Provider (RDP) Class
The Report Data Provider (RDP) Class will get the parameters and process the data with the help of a business logic class. The result of the class will be stored into a temporary table (zz_SNMSingleSourceVendorTmp).
To create a Report Data Provider (RDP) Class
1.    In the AOT, right-click the Classes node, and then click New Class.
2.    Name the Class as zz_SNMSingleSourceVendorDP.
3.       Now create four methods, classDeclaration, get_SNMSingleSourceVendorTmp, getReportParameters, processReport as shown below. Please note the methods getReportParameters and processReport as mandatory and having specific role to render the data for SSRS, so please include these two methods and name should be as mentioned.


  
Listing of the Codes
[
   SRSReportParameterAttribute(classstr(zz_SNMSingleSourceVendorContract
))
]
public class zz_SNMSingleSourceVendorDP extends SRSReportDataProviderBase
{
    zz_SNMSingleSourceVendorTmp     SNMSingleSourceVendorTmp;
    FromDate                        fromDate;
    ToDate                          toDate;
}


[
    SRSReportDataSetAttribute(tablestr(zz_SNMSingleSourceVendorTmp))
]
public zz_SNMSingleSourceVendorTmp get_zz_SNMSingleSourceVendorTmp()
{
    select SNMSingleSourceVendorTmp;
    return SNMSingleSourceVendorTmp;
}

private void getReportParameters()
{
    zz_SNMSingleSourceVendorContract contract = this.parmDataContract();

    if (contract)
    {
       fromDate           = contract.parmFromDate();
       toDate             = contract.parmToDate();
    }
}

[SysEntryPointAttribute]
public void processReport()
{
    ResultSet       RS;
    str             bDate, eDate;
    str             cVendAccount, cName, cItemID, cItem;
    str             cVendAccount1, cName1, cItemID1, cItem1;
    int             nSingle;
    Query                               query = this.parmQuery();
    QueryRun                            queryRun;
    this.getReportParameters();

    bDate =date2str(fromDate,321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);
    eDate =date2str(toDate,321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);

    RS = zz_SNMSQLUtility::executeQuery("SELECT DISTINCT PURCHLINE.VENDACCOUNT, P.NAME AS fName, PURCHLINE.ITEMID, "+
         "          ECORESPRODUCT.SEARCHNAME+' - '+INVENTDIM.INVENTSIZEID+' - '+INVENTDIM.INVENTCOLORID+' - '+INVENTDIM.INVENTSTYLEID+' - '+INVENTDIM.CONFIGID AS fItem "+
         "FROM      PURCHLINE INNER JOIN "+
         "          INVENTDIM ON PURCHLINE.INVENTDIMID = INVENTDIM.INVENTDIMID INNER JOIN "+
         "          ECORESPRODUCT ON PURCHLINE.ITEMID = ECORESPRODUCT.DISPLAYPRODUCTNUMBER INNER JOIN "+
         "(SELECT   VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME "+
         "FROM      VENDTABLE LEFT OUTER JOIN "+
         "          DIRPARTYTABLE ON VENDTABLE.PARTY = DIRPARTYTABLE.RECID "+
         ") P ON PURCHLINE.VENDACCOUNT=p.ACCOUNTNUM "+
         "WHERE PURCHLINE.MODIFIEDDATETIME>='"+ bDate+"' AND PURCHLINE.MODIFIEDDATETIME<='"+ eDate+"' "+
         "ORDER BY fItem, fName"
         );
    RS.next();
    cVendAccount = RS.getString(1);
    cName = RS.getString(2);
    cItemId = RS.getString(3);
    cItem = RS.getString(4);
    while(RS.next())
    {
            cVendAccount1 = RS.getString(1);
            cName1 = RS.getString(2);
            cItemId1 = RS.getString(3);
            cItem1 = RS.getString(4);
            if (cItemID != cItemID1)
                {
                    SNMSingleSourceVendorTmp.VendAccount = cVendAccount;
                    SNMSingleSourceVendorTmp.fName = cName;
                    SNMSingleSourceVendorTmp.ItemId = cItemId;
                    SNMSingleSourceVendorTmp.fItem = cItem;
                    SNMSingleSourceVendorTmp.insert();
                    SNMSingleSourceVendorTmp.clear();

                    cVendAccount = cVendAccount1;
                    cName = cName1;
                    cItemId = cItemId1;
                    cItem = cItem1;
        }
    }
}

Add both the classes into the project

STEP 7 – Create a reporting project in Visual Studio
To create a Report, run Microsoft Visual Studio 2010. Then create a reporting project, you can choose from two project templates: Visual Basic and Visual C#. Most popular is Visual C# template.
To create a reporting project
1.      Open Microsoft Visual Studio 2010.
2.      On the File menu, point to New, and then click Project. The New Project dialog box displays.
3.      In the Project Types pane, click the Visual C# node, and in the Templates pane, click Dynamics AX Reporting Project.
4.      In the Name box, type _rptSNMSingleSourceVendor, and in the Location box, type a location.
5.      Click OK.
A reporting project contains a model by default. A model is a file with the .moxl file name extension.
STEP 8 – Create a reporting project in Visual Studio
After creation of reporting project, ready to create the report. The following procedure explains how to create the report.
To create a report
1.      In Model Editor, right-click the Report1 node, and then click Rename.
2.      Type zz_SNMSingleSourceVendor as the name.
3.      Expand the zz_SNMSingleSourceVendor node if it is not already expanded.
4.      Right-click the Datasets node, and then click Add Dataset.
5.      Select the node for the data set.
6.      In the Properties window, specify the following values.
Property
Value
Data Source
Dynamics AX

Data Source Type
Report Data Provider
Default Layout
Table
Name
zz_SNMSingleSourceVendorDP
Query
Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the zz_SNMMrirDP class, and then select the all fields by selecting the All check box.


 Create an AutoDesign Report. This is a quick way to see that everything designed so far is working well or not.

To create an AutoDesign Report, drag the DataSet named ds on to the Designs Node. It will create an AutoDesign1 report with all the fields as columns in the report.

Before Running the report, change some properties related to presentation of the report, as shown below:

 
Run the report by providing the parameters.

After satisfaction the data fetched by the DataSet is as per the requirement, then create a Precision Design Report.
To create a Precision Design Report:
1.    Add a Precision Design, by right clicking on the Design node.
2.    Name the Design as zz_rptSNMSingleSourceVendorReport.
3.    Design a SSRS Report.
4.    To repeat the header on each page, click on the right side of the Column Group at the bottom, and select the Advance Mode of designing.
Put True as value to the Property RepeatOnNewPage.

Test the Report designed.
STEP 9 – Build and Publish the Report
Click on the Build Menu Option and Select Build option so as to build the Report Project. Then after successful build, click on the Build Menu Option and select Deploy Option to deploy the report so that can be called from the Ax Client.
Create a Menu Item so that user can access the Report by selecting it. To create a MenuItem, expand the Menu Items Node, and then Right Click on the Output Node, select New Menu Item option. Then put values to the newly created Menu Item as shown below:
Property
Value
Name
zz_SNMSingleSourceVendorReport
Label
Single Source Vendor Report
ObjectType
SSRSReport
Object
zz_rptSNMSingleSourceVendor
ReportDesign
zz_SNMSingleSourceReport

Drag the Newly Created Menu Item Option on to the desired location on the Menu Node.
Re-Run Dynamic Ax, it will now load the Menu Option on the Client. Run the Report by providing proper parameters.

Dynamic Ax, See the header is visible on the Next Page.
Add the Report, the Visual Studio Project and the Menu Item into the Project.

STEP 10 – Export the Project from the Development and Testing Server
Right click on the project created, and select option Export, provide a suitable name and export.
Copy the Exported project file to a desired location, from where it could be imported into live server.
STEP 11 – Import the Project into the Live Server
For Client Access drag the imported Menu Item on to a Menu Node.
Test the Report, by providing desired parameters.
It will show an Error, this is due to non-deployment of the SSRS report on to the SSRS Server.
STEP 12 – Re-Compile the Visual Studio Project and Publish the report with proper administrator rights to the Live Server
There are several ways to publish a report, one of them could be by copying the Visual Studio Project File from the Development Location to a location having Administrative access of the Live Server.
Make sure that the user from where the Visual Studio is operating has stuffiest rights to publish the report on the Live Server. To Test you can view the AOT on the Visual Studio Tool Window.

Then open the project.

Then Build and Publish the Project.