AX 2012 - create a SSRS report that takes ID as a parameter and displays the associated Sales order

Today we will learn on how to create a sales report that takes sales id as a parameter and print the sales order corresponding to the supplied sales id

       Create a temporary table named TEC_SALESINVOICETMP with Tabletype: tempDB

Make 3 Classes:-

 [DataContractAttribute]

class SalesReportDC

{

    RecId      recid;

}

[DataMemberAttribute("RecId")]

public RecId parmSalesRecid(RecId _recid =recid)

{

recid = _recid;

return recid;

}

-----------------------------------------------------------------------------

class SalesReportController extends SrsReportRunController

{

}

protected void preRunModifyContract()

{

    SalesReportDC  contract;

    RecId           recid;

    ;

    contract = this.parmReportContract().parmRdpContract() as SalesReportDC;

    contract.parmSalesRecid(this.parmArgs().record().RecId);

}

public static void main(Args _args)

{

    SalesReportController controller = new SalesReportController();

    controller.parmReportName(ssrsReportStr(SalesReportRDP, SalesReport));  //reportname,design

    controller.parmArgs(_args);

   // controller.setRange(_args, controller.parmReportContract().parmQueryContracts().lookup(controller.getFirstQueryContractKey()));

    //if (controller.prompt())

   // {

        controller.startOperation();

    //}

}

-----------------------------------------------------------------

[SRSReportParameterAttribute(classStr(SalesReportDC))]

class SalesReportRDP extends  SRSReportDataProviderBase

{

    TEC_SalesInvoiceTmp     salesInvoiceTmp; //temptable

   // SalesLine       salesline;

    //InventTable     inventtable;

    //SalesTable      salestable;

    CustInvoiceJour     custinvoicejour;

    CustInvoiceTrans    custinvoicetrans;

    RecId           recId;

}

[SRSReportDataSetAttribute(tableStr(TEC_SalesInvoiceTmp))]

public TEC_SalesInvoiceTmp getSalesInvoiceTmp()

{

    select salesInvoiceTmp;

    return salesInvoiceTmp;

}

private void insertData()

{

    SalesTable          salesTable;

   //  select salesTable   where salesTable.SalesId    ==  custinvoicejour.SalesId;

    salesTable                       = custinvoicejour.salesTable();

    salesInvoiceTmp.ItemId           = custinvoicetrans.ItemId;

    SalesInvoiceTmp.DeliveryDate     = salesTable.DeliveryDate;

    SalesInvoiceTmp.SalesId          = custinvoicetrans.SalesId;

    SalesInvoiceTmp.SalesName        = salesTable.SalesName;

    SalesInvoiceTmp.Price            = custinvoicetrans.SalesPrice;

    SalesInvoiceTmp.NameAlias        = custinvoicetrans.itemName();

    SalesInvoiceTmp.insert();

}

public void processReport()

{

    SalesReportDC           salesReportContract = this.parmDataContract();

    Query                   query = new Query();

    QueryBuildDataSource    qbds,qbds1,qbds2;

    QueryRun                qr;

    ;

    recId               =   salesReportContract.parmSalesRecid();

    qbds = query.addDataSource(tableNum(custinvoicejour));

    qbds1 = qbds.addDataSource(tableNum(custinvoicetrans));

    qbds.addRange(fieldNum(CustInvoiceJour,recId)).value(queryValue(recid));

    qbds1.relations(true);

    qbds1.joinMode(JoinMode::InnerJoin);

    qr = new QueryRun(query);

    while(qr.next())

    {

        custinvoicejour       =   qr.get(tableNum(custinvoicejour));

        custinvoicetrans      =   qr.get(tableNum(custinvoicetrans));

        //salestable       =   qr.get(tableNum(salestable));

        this.insertData();

    }

   // select salesInvoiceTmp where salesInvoiceTmp.ItemId == salesReportContract.parmSalesRecid();

    super();

}

 Now create a menu item – Output type

Drag the menu item in Menu.

Now go to visual studio and design the report.  Select precision design. And after designing add to AOT and Publish.

Now view your report in AX.

AX 2012 - view row headers on all the pages of a SSRS report

Today we will learn on how to view row headers on all pages of a AX 2012 SSRS report

If you want to view row header on all columns:

• Select tablix and Click advance after.

Here we have two rows above our data. You can see two static members under row group above details.

That means we have to change properties of both static members.

Properties:

• FixedData = true
• KeepWithGroup = above
• RepeatOnNewPage = true

If you want to hide first row containing fields from date and to date.

For the first static member, change these properties.

Properties:

• FixedData = true
• KeepWithGroup = None
• RepeatOnNewPage = true

Deploy it and add to AOT.

View the Output.

AX 2012 - Virtual Company and Table Collections

Today we will learn on how to share data across multiple companies using AX Virtual Company concept

Virtual Company:
Dynamics Ax stores data as per company in tables. But there might be occasions when you want to share data across companies, like country, state, and zip codes data. This sharing of data is achieved by creating a virtual company and storing data in this virtual company. Normal companies are then configured to read/write data from this virtual company. The only purpose of virtual company is to share data across companies, you cannot log into this virtual company.

Before seeing how to do virtual company setup, I would like you to show another trick that can be used to share data across Ax. There is a property on Ax tables called "SaveDataPerCompany", you can use this property to save data globally in Ax. To share data set this property to "No".

Note: This data is shared by all the companies in Ax. This option will delete DataAreaId field and default (DataAreaId, RecId) index from the table. If you want more control on shared data, like which companies can share and which cannot then use virtual company. 

Virtual Company setup:

Step 1: Create Table Collection

Decide which tables you want to share and create a table collection for these functionally related tables.

 For e.g: Create a new table which you want to share like TEC_VirtualCompany

To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection

Create a virtual company that will hold the shared data for normal companies.
Note: Before doing the below steps, make sure you are the Ax administrator and the only user online.

1.    Go to Administration -- Setup -- Virtual Company accounts, and create a virtual company.

2.    Decide which companies needs to share data and attach those normal companies with this virtual company.

3.    Attach the table collection with this virtual company.

Your Ax client will re-start and you are done with setting up the virtual company account.

Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

Test:

1. Company which is added in Virtual Collection.

  2. Company which is not added in Virtual Collection.

SQL level changes: DATAAREAID will be the virtual company name we created.

How to move existing data to virtual company?
When you setup a new virtual company, Ax does not move data automatically from normal company to virtual company. This is done by system administrator manually.

There are many ways to do this data move, but I will discuss only two approaches here.

Ax Import / Export:
This is standard Ax approach.

1.    Manually export existing normal company data from Ax.

2.    Remove duplicate records from this exported data set. 

3.    Delete exported data from normal companies.

4.    Import the exported data back in Ax, while logged into one of the participating companies. 

5.    Create records deleted in point 2 again in Ax using your logic. How you want to handle duplicate? For example, if you have customer 'Rah' in more than one normal company, what you want to do with this?

Direct SQL:
Use this approach if you have good knowledge about SQL queries and Ax table structures/relationships. Below are few points that will help you understand what to do and how to do.

• All Ax tables store data as per company unless otherwise specified. For this, Ax uses a special field called DataAreaId. In case of virtual company, it does not matter from which normal company you log-in, it is always the virtual company id which is stored in DataAreaId field of shared tables. 
• Ax also assigns a unique 64bit number to each record in table. For this, Ax uses a special field called RecId. This RecId is unique in the table and is generated by Ax when you insert a new record in Ax. It is not related to DataAreaId / Company.
• For unique records between all participating normal companies, update the DataAreaId to the virtual company id.
• For duplicate records, create them again in Ax using some Ax job or Ax import/export technique.

AX 2012 - Import data from .csv file

Today we will learn on how to import data from a .csv file into AX 2012 through a Job

1)     Create a table TEC_Import

Emp_Age        int

Emp_Dob        date

Emp_Id           str 20

Emp_Name     str 20

2)     Write a job

static void Import(Args _args)

{

    #File

    IO              iO;

    TEC_Import      import;

    FilenameOpen    filename = "c:\\import.csv";//To assign file name

    Container       record;

    boolean         first = true;

    boolean         ifexist = false;

    TEC_ID          empid;

    ;

    iO = new CommaTextIo(filename,#IO_Read);

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

    {

        throw error("@SYS19358");

    }

     ttsBegin;

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

    {

        record = iO.read();// To read file

        if (record)

        {

            if (first)  //To skip header

            {

                first = false;

            }

            else

            {

                empid = conPeek(record, 1);

                select Emp_Id from import where import.Emp_Id == empid;

               /*if(import.Emp_Id)

                {

                first = false; //skip those records whose id's are same

                info("Record already exist");

                }

                else

                {*/

                    //  read container to insert record in your table…….

                import.Emp_Id = conPeek(record, 1);

                import.Emp_Name = conPeek(record, 2);

                import.Emp_Age = str2int(conPeek(record, 3));

                import.Emp_Dob = str2Date(conPeek(record, 4),123);

                    //if(import.validateWrite())

                    //{

                         import.insert();

                      //   info("Inserted successfully");

                    //}

                    //else

                    //{

                      //  info("Insertion Failed due to exception");

                   // }

                //}

            }

        }

       }

               ttsCommit;

}